asked
Backup Failed yet the history shows backup completed.
Hello, 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.
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.
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.
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.
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. :-(
15 People are following this question.
