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.
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.
answered Mar 27, 2013 at 07:31 PM