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 19:30: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.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: