Apparent Back Up Database Task Failure

Some time ago, I encountered an apparently failing backup job on a SQL Server 2008 R2 instance. The job used the Back Up Database Task in a maintenance plan to back up multiple databases & the apparent failure was due to the removal of one of the databases from the instance without it being removed from the maintenance plan. I say “apparent failure” because the job was backing up the remaining databases just fine.

The job log did not give much information about what caused the failure but a quick look at the log for the maintenance plan identified the issue of the missing database.
01_LogFileError_0102_LogFileError_02
The solution was to edit the Back Up Database Task & re-save the maintenance plan so that it updated the metadata about the available databases. Checking the specified database list showed that the deleted database (in this case Test02) was no longer available.
03_MaintPlanDBs

More recently, I came across the same problem on a SQL Server 2012 instance. However, the job no longer fails in the newer version of SQL Server. The error in the maintenance plan log does exist though.

So on the plus side, there are no more (kind of pointless) job failures. But on the downside, there’s nothing to remind the admin to clean up after themselves when they remove a database.

Note: The screenshots above are from a SQL Server 2012 instance because I didn’t have a 2008 R2 instance to hand at the time of writing.

Advertisements

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

Kevin Kline

Career and Technical Advice for the IT Professional

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert

Powershellshocked

A blog about PowerShell and general Windows sysadmin stuff

Simon Learning SQL Server

I'm trying to become "better" at SQL Server and data - here's how I'm doing it!

%d bloggers like this: