Archive for category Technology
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