Compatible from SQL Server 2008
Quick check if all your databases are backed up. It is important for every DBA that databases you are responsible of are properly backed up. Use this script to identify if there is not any database with missing backups. You backup strategy is very important so take care about filling its requirements. You have to be prepared for every recovery scenarios.
TIP: Script is checking all databases without backup during last 24 hours. This can vary based on you backup strategy so change value in HAVING condition on line 11 to best fit your case.
--Databases with data backup over 24 hours old
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS [Server],
b.database_name AS [Database],
MAX(b.backup_finish_date) AS [Last Data Backup Date],
DATEDIFF(hh, MAX(b.backup_finish_date), GETDATE()) AS [Backup Age (Hours)]
FROM msdb.dbo.backupset b
WHERE b.type = 'D'
GROUP BY b.database_name
HAVING (MAX(b.backup_finish_date) < DATEADD(hh, - 24, GETDATE()))
UNION
--Databases without any backup history
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS [Server],
d.name AS [Database],
NULL AS [Last Data Backup Date],
9999 AS [Backup Age (Hours)]
FROM master.dbo.sysdatabases d
LEFT JOIN msdb.dbo.backupset b ON d.name = b.database_name
WHERE b.database_name IS NULL
AND d.name <> 'tempdb'
ORDER BY b.database_name
Results
Script results screen:
Script results detailed description:
| Column name | Data type | Description |
|---|---|---|
| Server | nvarchar(128) | Both the Windows server and instance information associated with a specified instance of SQL Server. NULL = Input is not valid, or an error. |
| Database | nvarchar(128) | Name of the database involved in the backup operation. Can be NULL. |
| Last Data Backup Date | datetime | Last time when backup operation finished. |
| Backup Age (Hours) | int | Number of hours since last backup was taken. |