When was the last time you did a backup on your databases? You may need to do this if you manage multiple SQL instances and are responsible for making sure backups happen for all databases. Backups also come into play when you’re connecting to an instance for the first time and need to know when the last backup for each database happened.

This is one of those small but crucial tasks that a lot of developers commonly dread. It’s not hard, but it’s extremely time-consuming to click on each and every database and manually check the date that each database was last backed up, especially if you work for a mid-market company that may have hundreds of separate databases. Too often, developers put off this task simply because it takes so long, putting their data at risk in the meantime.

If only there was a way to check all your databases at once... Well, there is! There is a simple query which will quickly show you the last time each individual database on an instance was backed up. Here it is:

USE [msdb]
GO

SELECT
[database_name] AS [DatabaseName],
MAX([backup_finish_date]) AS [LastBackup]

FROM [backupset]

WHERE [type] = 'D' -- 'D' is for Full backups. List of possible types here: https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/backupset-transact-sql

GROUP BY [database_name]
ORDER BY [database_name]

Run this query once for every instance. That’s it!

It’s important to check and back up your databases, especially after any unplanned outages or incidents. Don’t put it off! This handy trick will save you a ton of time and make sure you’re protected.

If you’re interested in database development and security, or any other business scenario, contact BDO Digital to learn more.