Post reply

Always test your backups!!! err...how?

  • January 12, 2007 at 10:07 am

    #68133

    We've all heard the sage advice - a backup made does not make a reliable backup - or something like that.  Simply, you don't know if your backup works until you restore it.   How would one go about testing backups on a server with ~50 databases?  Our dev server cannot be used for this. 

    Do people just restore a backup on a production server?  For some reason, I doubt it.

  • January 12, 2007 at 10:22 am

    #682975

    Sometimes yes, just make sure you give it a different name and use 'with move'...

  • January 12, 2007 at 10:28 am

    #682981

    You mean, restore to production, but to a different db?

  • January 12, 2007 at 11:09 am

    #683004

    Yes.   You backup your database "PRODUCTION_DB" and then do a restore, and in the TO DATABASE box you put the new DB name "PRACTICE_RESTORE"

  • January 12, 2007 at 11:16 am

    #683006

    And after you've done that - how would you compare to be assured everything matches?  Do most people go that far.  I know there are 3rd party tools for comparing, but is there anything codeable?  I suppose the production database changing during the backup and restore would reduce the validity of such a test. 

  • January 12, 2007 at 12:22 pm

    #683031

    I don't check that deeply. I make sure it suceeded, no errors, data is fully accessible, connections work .... etc ...

  • January 12, 2007 at 1:26 pm

    #683042

    I don't think any one compares the data after restore...because for smaller dbs fine but what about the dbs >300gb...

    When you compare the data with restored copy with production data will be different because you may insert/update/delete data on production which wll not match with restored copy.

    If restore is successful and don't see any issues accessing the data it should be fine. If you are not running Consistency checks on production you run the Consistency checks on restored copy to check data/object/db corruption.

     

    MohammedU
    Microsoft SQL Server MVP

  • January 12, 2007 at 1:32 pm

    #683044

    Thanks everyone for your comments - I appreciate them VERY much.

  • January 13, 2007 at 10:09 am

    #683094

    Verify the backups after they complete.

  • January 13, 2007 at 11:17 am

    #683095

    A verify is as good as a test restore?

  • January 14, 2007 at 8:39 am

    #683124

    you have a option called restore with verify only which just verifies ur backup and does not restore the database. u can use that to verify ur backup thats made.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • January 15, 2007 at 3:57 am

    #683174

    verify does not verify the backup only the backup file header. There is only one way to verify a backup and that is restore it, that's one reason so many DBA's like log shipping.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • January 15, 2007 at 3:58 am

    #683176

    sorry just thinking - especially considering new owners - redgates sql and data compare will allow you to compare a database - yes there may be soem data changes but you'd see them anyway.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply