SQL DBA Blog

If you’re running a SQL Server and you care about the data held in it, you should already be backing up the databases. But it’s always surprising the number of times you encounter business-critical databases not being backed up as a DBA!

When given the opportunity to look at a new SQL Server instance one of the first scripts I would run is to check when the databases were last backed up. I’m likely being asked to look at this server because there’s an issue. Issues need fixing, and before any of my changes are executed I need a point-in-time revert back to in-case I make things worse.

This blog post is to note a script that’ll return last Full, Differential, and Log backups for all databases on a SQL Server instance.

Get Last Backup Times in SQL Server

-- 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);

The above shows my simple wee test database ‘blah’ is the only database that has had a backup on this instance.