Successful Backup Job on a Dropped Database

I’m a big fan of Ola Hallengren’s SQL Server maintenance scripts and would recommend that anyone working with SQL Server check them out. They have served me well over the years. As it relates to today’s blog post, maybe too well…

I recently ran into a strange situation with the DatabaseBackup stored procedure that had me scratching my head: a backup job completing successfully for a database that didn’t exist.

Confused? So was I. Let’s take a look at how it happened.

Magic

We’ll recreate the issue with a BackupTest database:

CREATE DATABASE BackupTest;

And a SQL job called Full Backup Test with this backup command:

EXECUTE dbo.DatabaseBackup
        @Databases = 'BackupTest',
        @Directory = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\BackupTest',
        @BackupType = 'FULL',
        @Verify = 'Y',
        @Compress = 'Y',
        @CheckSum = 'Y',
        @CleanupTime = 24
        GO

If we run the SQL job, we can confirm our backup completes successfully:

All good so far.

Expect the Unexpected

Let’s drop the database and see how that affects the job:

DROP DATABASE BackupTest;

We run the job again and see that it still somehow completed successfully:

The job history matches our job window? Our two back to back jobs show as successful:

But we have no new backup (which makes sense as there is no existing database to backup).

How is this possible? Digging into the job history will show us more information if we expand the latest entry and check the message contents:

[SQLSTATE 01000] (Message 50000) The following databases in the @Databases parameter do not exist: [BackupTest].

Should the job fail in this case instead of completing successfully? In my opinion, not necessarily. If you have two or three databases configured instead of only one, it could be considered better to skip the one that doesn’t exist and try backing up the others.

It Ain’t on the Map

It was a missing directory that led me to take a closer look at the backup job. If we remove the BackupTest folder and run our test job again, the job will fail and note this message in the job history:

[SQLSTATE 01000] (Message 50000) The directory *DIRECTORY HERE* does not exist.

An invalid path means no backups are going to be successful.

I think the example above shows the stored procedure working as designed. Ideally, there are going to be checks in place to know if a database has gone missing or if backups are completing. This was simply a matter of a job that should have been dropped along with the database. Instead, it got overlooked which led to a mystery needing solved.

Thanks for reading!