Menu & Search
Show Last Backup Times in SQL Server

Show Last Backup Times in SQL Server

One of the first things you’d want to know about a SQL Server if you’re touching it for the first time, is when the databases were last backed up. Always remembering, if you’re making a change to a production server, ensure backups are happening… in-case you break something of course.

The following script will show last Full, Differential and Log backups for all databases on a SQL Server instance.

-- Show last backups on all databases
SELECT 
  ISNULL(d.[name], bs.[database_name]) AS [Database], d.recovery_model_desc AS [Recovery Model], 
    MAX(CASE WHEN [type] = 'D' THEN bs.backup_finish_date ELSE NULL END) AS [Last Full Backup],
    MAX(CASE WHEN [type] = 'I' THEN bs.backup_finish_date ELSE NULL END) AS [Last Differential Backup],
    MAX(CASE WHEN [type] = 'L' THEN bs.backup_finish_date ELSE NULL END) AS [Last Log Backup]
FROM 
  sys.databases AS d WITH (NOLOCK)
  LEFT OUTER JOIN msdb.dbo.backupset AS bs WITH (NOLOCK)
  ON bs.[database_name] = d.[name] 
  AND bs.backup_finish_date > GETDATE()- 30
WHERE d.name <> N'tempdb'
GROUP BY ISNULL(d.[name], bs.[database_name]), d.recovery_model_desc, d.log_reuse_wait_desc, d.[name] 
ORDER BY d.recovery_model_desc, d.[name] OPTION (RECOMPILE);

Share

0 Comments