Skip to content

How to get SQL Server Database backup history? Or when was my SQL Server database last backed up?

February 26, 2013

It’s important to keep track of all the backups happening to make sure we have a latest backup available and all the required databases are backed up regularly. Use below scripts to check backup history and other backup related details.

1: Script to get backup history of all the databases. With a little modification as indicated in script you can easily get below details:

  • Script for Backup History of only one database.
  • Script for Backup history of one/ all the databases between particular dates
  • Script for only Full/Diff/Log backups of all/ Particular database, complete history or between particular dates.
SELECT p.database_name AS DatabaseName,
p.backup_start_date AS 'Backup Start Time',
p.backup_finish_date AS 'Backup Finish Time',
CAST((DATEDIFF(MINUTE, p.backup_start_date, p.backup_finish_date)) AS varchar)+ ' min  '+ CAST((DATEDIFF(ss, p.backup_start_date, p.backup_finish_date)) AS varchar) + ' sec ' AS [Total Time] ,
CASE p.type
WHEN 'D' THEN 'Full '
WHEN 'I' THEN 'Diffrential'
WHEN 'L' THEN 'Log'
END AS 'Backup Type',
Cast(p.backup_size/1024/1024 AS numeric(10,2)) AS 'Backup Size(MB)' ,
a.physical_device_name AS 'Physical File location'
FROM msdb..backupmediafamily a,
msdb..backupset p
WHERE a.media_set_id=p.media_set_id

-- Uncomment below line and Replace <Database name> with DB you want to check backup history
--and p.database_name='Database name'

-- Uncomment below line and replace start and end dates with dates you want to check history
--and p.backup_start_date>'2013-01-20' and p.backup_start_date<'2013-01-25 23:59:59'

--Uncomment below line to see only the full backups, replace with 'I' to check diffrential and 'L' to check only Log backups.
--and p.type='D'

ORDER BY p.backup_start_date DESC 

2: Script to get name of databases are never backed up:

SELECT name AS 'Database Name',
       NULL AS [last backup]
FROM sys.sysdatabases
WHERE name NOT IN
    (SELECT database_name
     FROM msdb..backupset)
  AND name <> 'tempdb'

3: Latest Full/Differential/Log backup of one/all databases with duration:

SELECT Database_name,
       MAX(Backup_finish_date) AS 'latest Backup Date',
       CAST((DATEDIFF(hh, MAX(Backup_finish_date), GETDATE())) AS varchar) AS [Total Time]
FROM msdb..backupset

--Replace 'D' with 'I' to check latet Diff backup and 'L' to check latest full backup
WHERE TYPE='D'

--Uncomment below line and replace database name you want to check latest full backup
 --and Database_name='master'

GROUP BY database_name

Important Points:

These scripts can esaily be used in a scheduled job and scheduled to run so that you always know which databases are missing from backup schedules and if all of them are backed up properly.

Advertisements
No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: