SQL SERVER – Finding the Last Backup for All Databases – Notes from the Field #016

[Notes from Pinal]: During my presentation, I always ask developer a simple question – When did you take your last database backup? I get two different kinds of answers – 1) few replies with No Idea and 2) few replies with some date time. When I further ask if they are confident that their backup was taken successfully, I see confuse faces. Well, in this episode of Notes from the Field Tim answers the very same question with answer.

Linchpin People are database coaches and wellness experts for a data driven world. In this 16th episode of the Notes from the Fields series database expert Tim Radney (partner at Linchpin People) explains a very simple script to find when was last successful backup of all the database was taken.

As data professionals (those responsible for supporting database environments) one of our most important task is making sure that we have proper backups. I regularly have to perform audits of SQL Server environments and one of my first checks is to make sure that backups are being performed. A quick check is to see when the last full backup was made. You can do this by running the following script.

SELECT a.Name AS [DB_Name],
COALESCE(CONVERT(VARCHAR(12), MAX(b.backup_finish_date), 101), '-') AS LastBackup
FROM sys.sysdatabases a
LEFT OUTER JOIN msdb.dbo.backupset b ON b.database_name = a.name
GROUP BY a.Name


This will only tell you when the last full was completed. If you are running daily full backups then this will be sufficient. The script I personally run can be located on a recent blog post. This post includes a script that will get the database name, recovery model, most recent full, most recent differential, and the last two transaction log backups.

Anytime I talk with someone about backups I have to stress the importance of validating your backups. You need to have a process in place to regularly validate your backups by restoring them to another environment. Backups that can’t be restored when you need them are useless.

If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.

Note: Tim has also written an excellent book on SQL Backup and Recovery, a must have for everyone.

Reference: Pinal Dave (https://blog.sqlauthority.com)