I have SQL Server 2005 installed with a database maintenance plan created to backup all non system databases. I recently created a database snapshot of one of the non system databases and now both the full backups and transaction log backups jobs fail. If I delete the snapshot the backup jobs work properly again.
There are no errors in the event log other than the final “job failed” message. I took a profiler trace of the sql statements being run during the backup job execution and it looks to me as if the backup jobs include the snapshot in the list of databases selected from the master.sys.databases table even though you can’t back up a snapshot.
The job creates a subdirectory in the backup destination for the snapshot but it never tries to run a BACKUP DATABASE statement for the snapshot. It executes msdb..sp_maintplan_update_log to add the message ‘Database ”
I’m guessing that this stored procedure call marks the job as having failed. It’s odd though because if you open the maintenance plan in SSIS and look at the backup tasks, you can’t see the snapshot in the list of databases shown. It seems like the SSIS wizard is smart enough to ignore snapshots because they can’t be backed up, but the jobs created by the wizard aren’t smart enough to ignore snapshots. Odd because one of the queries run as part of the backup job selects a field called “IsDatabaseSnapshot”. I would expect that the maintenance plan jobs would be smart enough to just ignore any snapshots listed in the master.sys.databases table.
Is this a known bug in SQL 2005 or have I done something incorrectly?