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’ll talk about that in later posts.

You arrive at the office in the morning, tasting your first sip of coffee while your mailbox is loading all weekend emails 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 on the @DateSinceDBsNotBackedUp variable. 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 @BackupType variable to ‘L’ instead of ‘D’ at the beginning. This variable can receive all backup types and one more added by me that is ‘ALL’, this value considers all types of backups.


/******************************************************************************/******************************************************************************
Author: Victor Sosa
Blog: www.wisesql.com
Description: Find DBs not backed up since specific date.
******************************************************************************/
USE msdb
GO
DECLARE @DateSinceDBsNotBackedUp smalldatetime = '2017-07-14 23:00:00'
DECLARE @BackupType varchar(3) = 'D' --ALL (All types), Full backup (D), Log backup(L) (Rest of types on Books OnLine)

SELECT
d.[name] AS [Databases_Not_BackedUp],
d.recovery_model_desc AS [Recovery_Model]
FROM
sys.databases d
LEFT JOIN dbo.backupset b ON d.[name] = b.[database_name]
AND b.backup_start_date >= @DateSinceDBsNotBackedUp
AND b.[type] = IIF(@BackupType='ALL',b.[type],@BackupType)
WHERE
b.[database_name] IS NULL
AND d.[name] <> 'tempdb' --We don't need tempdb for this matter
AND d.recovery_model_desc <> IIF(@BackupType='L','SIMPLE','') --If Log backup type is selected, DBs with SIMPLE recovery model is not needed
ORDER BY
d.recovery_model_desc, d.[name]

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

Database_Not_Backed_Up

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

See you next time.



Categories: BACKUPS

Tags: , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: