none
Backups - Testing out your backups strategy

    Question

  • How do you guys go about testing out your backups and backup strategy?

    If lets say you pick a random instance on a random day, how do you go about testing your backups?  Do you restore each backup one by one on a test system?

    *I guess what I'm looking for is a script or a way to restore backups from a random system on a random day into a test system.

    • Edited by Diango Wednesday, October 23, 2013 1:37 AM
    Wednesday, October 23, 2013 12:00 AM

Answers

  • Incase you are restoring backup than normal restore command is fine or you can check the backup set from headeronly

    Can cross confirm the
    RESTORE HEADERONLY
    FROM DISK = N'backupsetpath'
    WITH NOUNLOAD;
    GO

    Also, can check below link :

    http://www.mssqltips.com/sqlservertip/1584/auto-generate-sql-server-restore-script-from-backup-files-in-a-directory/


    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Wednesday, October 23, 2013 3:05 AM
  • Hello,

    First of all a very nice though to actually test your backup and see how much time and upto what point you can restore your data in case of disaster,adding to what Adwin said, if you are going to test your backup few points

    1. Make sure your backup is valid/consistent  backup .You can use restore verifyonly to check its consistency but only successful restore can guarentee that backup set is valid.

    2. If you have backup policy like full backup every day and trn log backup every 1 or 2 hr you should be ready with the script to restore large amount of transaction log backup.

    3. Make sure your backups comply with your RPO and RTO.

    5. For script you have to come up with your own,just a dummy is below.

    RESTORE DATABASE Your_DB
    FROM DISK = 'D:BackUpYourBaackUpFile.bak'
    WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',
    MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.ldf'
    with norecovery
    -----then resrtore diff backup
    restore database your_db from disk='location.diff'
    with norecovery
    ---restore logs
    restore log your_db from disk='location.trn' with recovery

    6. restore would be like full backup (NR) latest diff backup taken after full backup (NR) all transaction logs with no recovery but last one with recovery

    7. If you want to stopat at particular point see

    http://www.karaszi.com/sqlserver/info_minimizing_data_loss.asp

    HTH


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    Wednesday, October 23, 2013 5:06 AM

All replies

  • Incase you are restoring backup than normal restore command is fine or you can check the backup set from headeronly

    Can cross confirm the
    RESTORE HEADERONLY
    FROM DISK = N'backupsetpath'
    WITH NOUNLOAD;
    GO

    Also, can check below link :

    http://www.mssqltips.com/sqlservertip/1584/auto-generate-sql-server-restore-script-from-backup-files-in-a-directory/


    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Wednesday, October 23, 2013 3:05 AM
  • Keep in mind that when you test your backups, you need to meet at least these two minimum objectives - that the backups work without issues and that you meet your recovery time objective (RTO.) Automating the restore process is great but make sure that you meet these two objectives.

    Edwin Sarmiento SQL Server MVP Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn
    SQL Server High Availability and Disaster Recover Deep Dive Course

    Wednesday, October 23, 2013 3:59 AM
  • Hello,

    First of all a very nice though to actually test your backup and see how much time and upto what point you can restore your data in case of disaster,adding to what Adwin said, if you are going to test your backup few points

    1. Make sure your backup is valid/consistent  backup .You can use restore verifyonly to check its consistency but only successful restore can guarentee that backup set is valid.

    2. If you have backup policy like full backup every day and trn log backup every 1 or 2 hr you should be ready with the script to restore large amount of transaction log backup.

    3. Make sure your backups comply with your RPO and RTO.

    5. For script you have to come up with your own,just a dummy is below.

    RESTORE DATABASE Your_DB
    FROM DISK = 'D:BackUpYourBaackUpFile.bak'
    WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',
    MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.ldf'
    with norecovery
    -----then resrtore diff backup
    restore database your_db from disk='location.diff'
    with norecovery
    ---restore logs
    restore log your_db from disk='location.trn' with recovery

    6. restore would be like full backup (NR) latest diff backup taken after full backup (NR) all transaction logs with no recovery but last one with recovery

    7. If you want to stopat at particular point see

    http://www.karaszi.com/sqlserver/info_minimizing_data_loss.asp

    HTH


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    Wednesday, October 23, 2013 5:06 AM
  • These tests are all about testing the people that will be performing these steps. This will then point to improvements in your documentation, and other possible "flaws" in your backup/restore strategies. I.e., automating the tests will in large extent make the tests less valuable since they then won't include the people skills part.

    On the other side of the coin you might want to schedule a restore of the database after each backup, so you know if thatbackup restored successfully. That is a different kind of test, and that is just a matter of scheduling the TSQL restore command.


    Tibor Karaszi, SQL Server MVP | web | blog

    Wednesday, October 23, 2013 6:00 AM