none
How can i prove that a backup is readable

    Question

  • 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.

    Tuesday, August 26, 2014 12:24 PM

Answers

  • 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


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

    • Proposed as answer by A-ZSQL Tuesday, August 26, 2014 1:35 PM
    • Marked as answer by Mr.Mnisi Tuesday, August 26, 2014 1:39 PM
    Tuesday, August 26, 2014 12:30 PM

All replies

  • 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


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

    • Proposed as answer by A-ZSQL Tuesday, August 26, 2014 1:35 PM
    • Marked as answer by Mr.Mnisi Tuesday, August 26, 2014 1:39 PM
    Tuesday, August 26, 2014 12:30 PM
  • 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.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Shanky_621 Tuesday, August 26, 2014 1:10 PM
    Tuesday, August 26, 2014 12:31 PM
  • Thanks Satish.

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

    Tuesday, August 26, 2014 12:42 PM
  • 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

    Tuesday, August 26, 2014 12:43 PM
  • Yes, the output will something like "backup file is valid" if it was readable

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

    Tuesday, August 26, 2014 12:58 PM
  • Thanks buddy.
    Tuesday, August 26, 2014 1:04 PM
  • 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

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Articles

    Tuesday, August 26, 2014 1:11 PM
  • 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.


    Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de

    Tuesday, August 26, 2014 1:16 PM
  • Now you can verify your daily backups are readable or not.
    Tuesday, August 26, 2014 1:19 PM
  • ... that they WERE readable or not. You can't predict the future.

    Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de

    Tuesday, August 26, 2014 1:22 PM
  • 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/

    Tuesday, August 26, 2014 1:23 PM
  • 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

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Articles

    Tuesday, August 26, 2014 1:56 PM