Backup = Corruptness = What?

Recently, a SharePoint administrator came to the DBA team and asked for a backup to be restored on a certain day. Simple task right – it would have been had the backup he wanted not been corrupted. On to the second backup request, just give me a day earlier he says. Sure no problem only to find out that that backup is corrupted as well. Talk about looking like an idiot.

Business time constraints will not allow for executing the DBCC CheckDB command against the DB, backup the DB with the check sum option, then restore the backup with the verify only option. So; how do we get around this? Our DBA team has decided to take an approach that has not been done before here but I’m sure has been done somewhere.

After completing the space requirement assessment we have decided to spin up a VM. This will allow us to automate our process by taking the backup that was made and restoring it to the VM to ensure it is not corrupt. The automation piece will restore the backup; if failure occurs a notification will be sent to the team allowing us to know which backup could not be restored and then proceed on to the next backup.

The backup after being fully restored will then be removed prior to restoring the next one in line.

DBA’s are often times required to think outside the box; it is a tool that must be a necessity.

Don’t wait until there is a business need for a backup to find out you can’t do a restore. Be proactive about your backups and ensure that you are actively checking them.