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,
Isnull(CONVERT(VARCHAR(25), Max(F.backup_start_date)
partition BY F.database_name ),
113), ‘NEVER’)
AS Last_Full_Backup_Date,
CASE D.recovery_model_desc
ELSE Isnull(CONVERT(VARCHAR(25), Max(L.backup_start_date)
partition BY L.database_name ), 113),
END           AS Last_Log_Backup_Date,
                Isnull(CONVERT(VARCHAR(25), Max(I.backup_start_date)
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; 


About djheath
I have been in the IT industry since 2003, working in many different guises from a Unix Admin, System Admin, Software Support, Oracle DBA, SharePoint Developer, to my current role as a Senior SQL Server Database Administrator and Architect for De La Rue. This Blog is primarily about problems I have experienced in my technical work which I have struggled with, and not found a concise answer too, or snipits of useful information I don't want to forget.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: