Find Databases not backed up since specific date

One of our daily (and very important) tasks as Database Administrators is to ensure backups are being performed correctly. We also need to ensure that backups are functional but we talk about that in later posts.

You arrive to the office in the morning, tasting your first sip of coffee while your mailbox is loading all weekend mails just to find a bunch of alerts, in any other week you could safely ignore them, but not today. Today, your on-call week starts.

You start reading through the alerts and you have a bunch of servers where the backups failed. You have hundreds of databases in some instances and you don’t want your third party backup tool to just start backing up all databases all over again. Chances are that just one or two databases are missing so you need a quick way to find those out, with this information you could re-run the backup only for those databases and not for the entire instance, all of this in business hours impacting the performance doing backups out of the maintenance window.

You can use the query below to quickly find those databases that were not backed up, you just need to specify the date and time when the backup job/routine started. Let’s say your backups on that server run every night starting at 11 pm, well, the query below will return all DBs not backed up since 11 pm. With this information you can focus your attention only on those returned instead of restarting all databases. Also you can filter by backup type, the script focus by default on Full backups but maybe the alert you received was about Log backups, just change the filter on column “type”, replace letter ‘D’ for an ‘L’, or just comment that line to get the results for both.


/******************************************************************************
Author: Victor Sosa
Blog: www.wisesql.com

Description: Find DBs not backed up since specific date.
******************************************************************************/

USE msdb
GO

SELECT
dbs.name DBName, dbs.recovery_model_desc, dbs.state_desc
FROM
sys.databases dbs
LEFT JOIN (
SELECT
backup_set_id,
database_name,
backup_start_date,
backup_finish_date,
type,
backup_size / 1048576 backup_size_MB,
backup_size / 1073741824 backup_size_GB,
compressed_backup_size / 1048576 compressed_backup_size_MB --SQL 2008 and higher
FROM
dbo.backupset
WHERE
backup_start_date >= '2016-08-01 23:00:00.000' --Change the datetime
AND type = 'D' --Full backup (D) or Log backup(L)
--ORDER BY
--backup_finish_date DESC;
) AS bs ON dbs.name = bs.database_name
WHERE
bs.backup_set_id IS NULL
AND dbs.name <> 'tempdb'
ORDER BY
dbs.name
GO

In the example below I took a full backup of all databases except for “TestDB”, my backup job starts at 11 pm but assume it failed in the middle, I’ll set the datetime filter at 11 pm and run the query, as you can see “TestDB” database is the only one returned.

DBs_Not_BackedUp_Since_Specific_Date

Now that you know “TestDB” is the only one that was not backed up you can only focus your efforts on that one.

Tip: On the LEFT JOIN clause you will find a subquery, you can just select and run this subquery to obtain the opposite result, meaning all databases that were actually backed up since the specified date and time and a bunch of important information about those backups, like backup size, and what time the backup started and what time the backup finished.

See you next time.