Backup Failed yet the history shows backup completed.


We have hundreds of SQL Server instances of SQL 2000, SQL 2005, SQL 2008 and SQL 2008R2. I have a powershell script that polls the msdb.dbo.backupset tables of all our instances to show our successfull backups for audit reporting. One of our backups on SQL 2005 instance has failed and we recieved an alert that it failed but the report showed no failure. I can't verify if the file existed because someone already re-ran the backups and any files would have been overwritten. Basically the backupset table shows that the failed backup was successful. Does anyone know why that would be and have you seen it yourself? I've googled it and can't seem to find a similar circumstance. If I can't use that table for reliable proof of backups, what method could I use? Any insight would be much appreciated. Thanks in advance.

more ▼

asked Mar 27, 2013 at 07:25 PM in Default

avatar image

120 1 3 5

What generated the failure alert?

Mar 27, 2013 at 10:59 PM ThomasRushton ♦♦

This particular backup was performed by sqlsafe. It does include verify, but the error stated that it couldn't access the backup file, so I would agree with you that it was the verify that failed. It could be that the backup was fine but the connection was lost as it was trying to verify.

Mar 28, 2013 at 12:56 PM jabeci
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

If MSDB is showing the backup was successful then it was able to successfully dump the data to disk. Can you elaborate on your job that performs the backups, by chance is it also doing a restore verify only and that could be what failed to validate and was letting you know that the file SQL wrote was incomplete or malformed. If there was an issue with the disk subsystem that 'corrupted' the file but SQL didn't have an issue writing to disk because the disk didn't error out, then the only way to detect that would be restore verify or restore the complete file.

Issues like this is why we preach to validate backups. Don't just validate a file exist, test the backups.

more ▼

answered Mar 27, 2013 at 07:31 PM

avatar image

40.9k 39 94 168

Thanks Tim,

I'm definitely doing that. I'm mostly using this table to report for Audit the proof of backup results from hundreds of instances and thousands of databases. It's joined with a CMDB database to list region, application purpose, etc... I used to have my jobs populate a common repository but found that sometimes that function would fail. I thought the backupset table would be reliable. Guess not. :-(

Mar 27, 2013 at 07:38 PM jabeci

Well for the most part it is. It is telling you it successfully created a file. Unfortunately you can't verify right now the file existed and if it was complete or not. We do something very similar to yo at my shop. We audit based on successful or failed backups, but we also audit on database restores. Not every "successful" backup has been a complete file that we could restore. That is why MS built in the checksum checks, restore verify only, etc to help us validate backup files. You are on the right path, you just found one of the gotcha's.

Mar 27, 2013 at 08:10 PM Tim

Ah. That makes sense. Thank you very much for your response. I'm going to have to use another approach for our Audit reporting. Maybe I'll record success on the checksum checks and gather that data for the Audit reporting. Much appreciated sir.

Mar 28, 2013 at 12:45 PM jabeci
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 27, 2013 at 07:25 PM

Seen: 1533 times

Last Updated: Mar 29, 2013 at 04:46 AM

Copyright 2018 Redgate Software. Privacy Policy