Identify the last full, log and differential backup date and times for each database.

The following script is very useful if you just want to find out when each database was last backed up. It returns the database name, it’s recovery type, (important so that missing log backups for SIMPLE recovery can be ignored), and the date and time of each full, log and differential backup. This query could be used in an Agent job which could be scheduled to run each day and look for any databases that (a), haven’t had a full backup for more than a day and (b), any databases which are using the full recovery model, which haven’t had a transaction log backup for more than a day.


SELECT DISTINCT D.database_id AS Database_Id,
                D.NAME        AS Database_Name,
D.recovery_model_desc,
Isnull(CONVERT(VARCHAR(25), Max(F.backup_start_date)
OVER (
partition BY F.database_name ),
113), ‘NEVER’)
AS Last_Full_Backup_Date,
CASE D.recovery_model_desc
WHEN ‘SIMPLE’ THEN ‘N/A’
ELSE Isnull(CONVERT(VARCHAR(25), Max(L.backup_start_date)
OVER (
partition BY L.database_name ), 113),
‘NEVER’)
END           AS Last_Log_Backup_Date,
                Isnull(CONVERT(VARCHAR(25), Max(I.backup_start_date)
OVER (
partition BY I.database_name ),
113), ‘NEVER’)
AS Last_Diff_Backup_Date
FROM   sys.databases D
LEFT OUTER JOIN msdb..backupset F
ON F.database_name = D.NAME
AND F.type = ‘D’
LEFT OUTER JOIN msdb..backupset L
ON L.database_name = D.NAME
AND L.type = ‘L’
LEFT OUTER JOIN msdb..backupset I
ON I.database_name = D.NAME
AND I.type = ‘I’
ORDER  BY D.database_id; 

Advertisements