How can i prove that a backup is readable

Category: sql server streaminsight

Question

Mr.Mnisi on Tue, 26 Aug 2014 12:24:00


Hi.

We recently had an audit review in my company, and some findings arose.

I was requested by my manager to prove that the full SQL backups that are ran daily can be readable and restored.

Is there a way to prove this, without having to restore the DB?

Thanks in Advance.

Replies

Satish Kartan, MCITP on Tue, 26 Aug 2014 12:30:27


Yes, backups can be validated by using RESTORE VERIFYONLY command. This should be enough to satisfy the audit: http://msdn.microsoft.com/en-us/library/ms188902.aspx

RESTORE VERIFYONLY

FROM DISK = 'd:\backups\sample.bak'

WITH STATS=10

Olaf Helper on Tue, 26 Aug 2014 12:31:43


Hello,

The safest way to test a backup if is to restore it, e.g. on a different Server or as a new database.

Other Option is to use the RESTORE VERIFYONLY (Transact-SQL) command to check the backup file.

Mr.Mnisi on Tue, 26 Aug 2014 12:42:02


Thanks Satish.

I think this will suffice. So basically, i just run the three line commands and I will get an output?

Mr.Mnisi on Tue, 26 Aug 2014 12:43:41


Thanks Olaf.

I will first test the command, and if is not enough we will have to create a test DB, and restore the data to it and perform a quick query on it.

Thanks for your assistance

Satish Kartan, MCITP on Tue, 26 Aug 2014 12:58:14


Yes, the output will something like "backup file is valid" if it was readable

Mr.Mnisi on Tue, 26 Aug 2014 13:04:55


Thanks buddy.

Shanky_621 on Tue, 26 Aug 2014 13:11:37


Like to add as Olaf said only a successful restore can gurantee that backup set is complete in all formats. Restore verifyonly does almost all checks but not complete check. Yes it also checks that backup set is readable or not

Bodo Michael Danitz on Tue, 26 Aug 2014 13:16:30


Well, you can only prove that your backup WAS readable...

If you want to make sure that it will be readable when you need it: No chance.

Manong Sr on Tue, 26 Aug 2014 13:19:59


Now you can verify your daily backups are readable or not.

Bodo Michael Danitz on Tue, 26 Aug 2014 13:22:16


... that they WERE readable or not. You can't predict the future.

Satish Kartan, MCITP on Tue, 26 Aug 2014 13:23:22


I concur with other folks that a true test would be to restore the backup file. However, it is not very pragmatic to restore each and every backup file (if you have the time and resources, you can automate this restore process on a separate server). Instead, regular Disaster Recovery exercises every 3 to 6 months will allay any fears of bad backups. 

Shanky_621 on Tue, 26 Aug 2014 13:56:30


I concur with other folks that a true test would be to restore the backup file. However, it is not very pragmatic to restore each and every backup file (if you have the time and resources, you can automate this restore process on a separate server). Instead, regular Disaster Recovery exercises every 3 to 6 months will allay any fears of bad backups. 

Satish Kartan http://www.sqlfood.com/

Satish I agree with you but its very much pragmatic to test your backup by actually restoring it. One should infact include this in his activities. I am not saying you can do it every day but depending on size of backup you can create a plan