Has Your Database Been Backed up in the Last 24 Hours?

As a DBA your #1 responsibility is to make sure that each of your SQL Server databases are backed up.  Without recent backups you will not be able to recover your database without potentially losing many days of database updates.  Therefore, you need to make sure on a daily basis that your backups are running and running successfully.  In this tip, I provide a couple of TSQL scripts that will identify the last backup of each database.  By reviewing the output from these scripts, you will be able to identify the last backup for the databases on your instance.

Script #1:  Return the LastBackupDateTime for each database on your Instance

SELECT db.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(19), MAX(bs.backup_finish_date), 120),'None') AS LastBackUpDateTime
FROM sys.sysdatabases db
        LEFT OUTER JOIN msdb.dbo.backupset bs 
     ON bs.database_name = db.name
GROUP BY db.Name; 

Script #2: Show the databases that have never had a backup, or the current backup is over 24 hours old

SELECT db.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(19), MAX(bs.backup_finish_date), 120),'None') AS LastBackUpTime
FROM sys.sysdatabases db
        LEFT OUTER JOIN msdb.dbo.backupset bs 
     ON bs.database_name = db.name
GROUP BY db.Name
HAVING max(bs.backup_finish_date) < dateadd(dd,-1,getdate())
    or max(bs.backup_finish_date) is NULL;

See all articles by Greg Larsen