Find Databases w/o Backup

Imagine you have multiple databases on a server (maybe even hundreds or thousands?), each in full recovery mode, and a scheduled job that performs a log backup every fifteen minutes. All of a sudden the job starts failing. What could be the cause?

Very likely, a database was added to the server and has not yet had a full database backup performed. A log backup can only be performed after a full backup has been taken, since log backups are by definition incremental from a full backup.

How are we going to figure out which of our thousands of databases is missing a backup? Since it must have been recently created, we could simply sort sys.databases by created_date in descending order. However, there is no guarantee that the most recently created database (or any of the top X, for that matter) is, in fact, the one missing the full backup.

The following view will help us find the databases missing full backups. They will be identified by having a NULL value for backup_finish_date. This view extends that logic further to identify any database with an “old” backup as well. In this case, old is defined as over one day ago. Obviously that threshold should be adjusted to suit your environment.

-- Select databases without a current database backup
SELECT
    'DatabaseName' = db.name
    , 'LastBackup' = MAX(backup_finish_date)
FROM
    sys.databases AS db
    LEFT OUTER JOIN msdb.dbo.backupset AS bak
        ON db.name = bak.database_name
        AND bak.[type] = 'D' -- D = Database
WHERE db.recovery_model_desc <> N'SIMPLE'
GROUP BY db.name
HAVING
    ISNULL(MAX(backup_finish_date), '1900-01-01')
      < DATEADD(DAY, -1, GETDATE());

Also of note, in this use-case we are excluding databases with a “simple” recovery model since they would not be eligible for a log backup.

UPDATE 08 May 2015: There is a flaw in the query originally presented above. This flaw is exposed through the following use case. If a database is backed up and then deleted, without removing the backup history, the record of it’s back remains. If a new database is created with the same name as the deleted database, the query above will indicate that it has a backup when, in fact, there is none. This is due to the join between databases and backupsets having been solely on the database name field.

To correct this flaw, we can add a second join criterion for the database creation date, thereby ensuring we only consider backupsets that were created after the database itself was created. See updated query below.

-- Select databases without a current database backup
SELECT
    'DatabaseName' = db.name
    , 'LastBackup' = MAX(backup_finish_date)
FROM
    sys.databases AS db
    LEFT OUTER JOIN msdb.dbo.backupset AS bak
        ON db.name = bak.database_name
        AND DATEADD(SECOND
            , DATEDIFF(SECOND, CONVERT(DATE, db.create_date), db.create_date)
            , CONVERT(DATETIME2(0), CONVERT(DATE, db.create_date))
            ) <= bak.database_creation_date
        AND bak.[type] = 'D' -- D = Database
WHERE db.recovery_model_desc <> N'SIMPLE'
GROUP BY db.name
HAVING
    ISNULL(MAX(backup_finish_date), '1900-01-01')
      < DATEADD(DAY, -1, GETDATE());

Advertisements
Post a comment or leave a trackback: Trackback URL.

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: