SQL 2005 Backup History – How to retrieve a list of the most recent database backups
The MSDB database maintains a record of all SQL backup jobs that have been executed. Specifically you can find this information if you query MSDB..backupset. This script joins sys.databases, so that if a database exists but has not been backed up yet, it will still be displayed in the results. If you were to query the backupset table alone without the JOIN statement, you wouldn’t see any entries for databases on the server that have not been backed up. Also note that this particular script assumes that you’re only interested in Full and Differential backups. You could easily modify it to include information about Log backups.
Download the script here: SQL Backup History
I’ve also posted a HTML Application (HTA) version of this script as well, which you can see here: http://dougzuck.com/hta
OK, let’s take a look at the code…
SELECT @@servername AS server_name, s.name AS database_name, backup_type = CASE WHEN b.type = 'D' THEN 'Full' WHEN b.type = 'I' THEN 'Diff' END, MAX(b.backup_start_date) AS last_backup_start_time, DATEDIFF(d, MAX(b.backup_start_date), getdate()) AS days_since_last_backup, STATUS = CASE WHEN b.type = 'D' AND DATEDIFF(d, MAX(b.backup_start_date), getdate()) <= 7 THEN 'OK' /*if the most recent full backup was less than or equal to 7 days ago, we're OK*/ WHEN b.type = 'D' AND DATEDIFF(d, MAX(b.backup_start_date), getdate()) > 7 THEN 'Possible Problem' /*if the most recent full backup was more than 7 days ago, Possible Problem*/ WHEN b.type = 'I' AND DATEDIFF(d, MAX(b.backup_start_date), getdate()) <= 2 THEN 'OK' /*if the most recent diff backup was less than or equal to 3 days ago, we're OK*/ WHEN b.type = 'I' AND DATEDIFF(d, MAX(b.backup_start_date), getdate()) > 2 THEN 'Possible Problem' /*if the most recent diff backup was more than 3 days ago, Possible Problem*/ WHEN MAX(b.backup_start_date) IS NULL THEN 'No Backup Exists' END FROM sys.DATABASES s LEFT JOIN msdb..backupset b ON s.name = b.database_name WHERE s.name <> 'master' AND s.name <> 'model' AND s.name <> 'msdb' AND s.name <> 'tempdb' GROUP BY s.name, b.type ORDER BY s.name
You can see in the results screenshot below that I haven’t done backups of my local databases in a long time, which is why the status column is indicating there’s a “Possible Problem.”
