Archive for category Technology

SQL 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())  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.
backup_history_query_results

No Comments