Are you testing your database backups, If so how? Sure there are a couple options that you can use to help validate backups but does this mean you are 100% sure, without a doubt, that it can be restored?
Have you ever seen a backup file not restore?
Recently I was working with a client on a server that was having a little bit of a storage issue, the vendor was pretty sure after we took a couple steps that the storage would return to normal behavior. Management was not satisfied with the vendors forecast of “pretty sure”, and as a side note, I could not agree more with them. Management knew that a restore process was going to take some time, so rather than wait for the maintenance window when the storage fix was going to be attempted a decision was made to start a restore on a different server. This restore was being done as a just in case… Just in case the fix on the storage side did not work, and they lost a database. So I was off and running with a restore database task.
About two hours into the restore I received an error that stated unexpected end of file. I wasn’t too sure what exactly caused the short file, but I did know it did not sound good, and I could not restore from it. After I did some research it looked like the backup file I was restoring from was much smaller than the backup file SQL Server had created a few days before.
So here is my point…
It does not matter what you do on the backup side of the process, if you do not restore the file, you are at risk of having a bad backup file and then what?
It is good practice to restore your backups on a frequent basis, I may even be as bold as to say that you may want to restore each backup just to verify it. Either way, when you restore these backups it may not be good enough to copy the file to another server and then restore that backup. When you do a test restore you should test it from end to end. This means if you store your backups off-site then request a backup from that off site location and restore that file. If you restore your files with that methodology, not only are you testing that SQL Server created a good backup file, but that all the other pieces work as well. I like to back up and keep only one week’s worth of backups on local storage. As soon as the backup is done, it either goes to tape or another data center. So there are a number of pieces in that backup process, those are the same pieces that are part of the restore process.
Test your backups. Do this by restoring your databases from end to end. If you need it, we can help.