Post reply

Test backup restores (full + log) - restore databases automatically

  • April 4, 2015 at 9:40 pm

    #295784

    Hello

    I am working towards automating the process of testing our backups. For the meantime, I do it all manually - I copy the backup files (full + transaction logs) to our test server and then run the restore script. Once database restored I run the DBCC CheckDB. The results of checkdb I manually upload to our Sharepoint portal as proof that the backup file is intact with no errors.

    here are some ideas I have but have not yet tested:

    Create a maintenance plan with each 3 jobs:

    --> Powershell script to copy the files from Prod server to Test server - add this scrip to Job1

    --> Powershell script to restore databases files - add this script to Job2

    --> Run the DBCC in powershell (yet to find if possible in PS) - add this script to Job3

    I would like to use seperate jobs as to get a report on the duration and status of each job

    Would also like to get the results of the DBCC Checkdb as proof that no errors were found for upload to our Sharepoint portal. Dont know if possile via the job

    Do anyone of you have other solutions or have a strategy already in place?

    Thank you in adavnce

  • April 5, 2015 at 12:26 am

    #1788784

    Hello everyone,

    I posted the same question on technet and already got responses that will help me get started. Check it out if you are trying to find a solution as well :

    https://social.technet.microsoft.com/Forums/en-US/90106c54-9611-4398-ad6c-c4c281a3e195/automatically-restoring-a-database-from-prod-to-test-server-restore-test-strategy?forum=sqldatabaseengine

    regards

  • April 5, 2015 at 5:16 am

    #1788795

    I'll just add that you should make sure you still run DBCC CHECKDB on your production server, but that you just do the PHYSICAL ONLY check. Since you're going to be doing the logical checks after the restore, you no longer need to run those on your production server, but the physical checks still have to get done.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • April 5, 2015 at 4:12 pm

    #1788812

    efyuzegeekgurl (4/4/2015)

    Hello

    I am working towards automating the process of testing our backups. For the meantime, I do it all manually - I copy the backup files (full + transaction logs) to our test server and then run the restore script. Once database restored I run the DBCC CheckDB. The results of checkdb I manually upload to our Sharepoint portal as proof that the backup file is intact with no errors.

    here are some ideas I have but have not yet tested:

    Create a maintenance plan with each 3 jobs:

    --> Powershell script to copy the files from Prod server to Test server - add this scrip to Job1

    --> Powershell script to restore databases files - add this script to Job2

    --> Run the DBCC in powershell (yet to find if possible in PS) - add this script to Job3

    I would like to use seperate jobs as to get a report on the duration and status of each job

    Would also like to get the results of the DBCC Checkdb as proof that no errors were found for upload to our Sharepoint portal. Dont know if possile via the job

    Do anyone of you have other solutions or have a strategy already in place?

    Thank you in adavnce

    Skip step 1. It's a waste of time and space. Just go directly to the restores and forget the copies of the files.

    Skip all the PowerShell stuff. It's not buying you anything here and you don't actually need it because you're no longer copying files.

    You say that you were copying files from the prod server to the test server. I hope you're not making the dreadful mistake of storing backups on the same SAN or box as the MDF/LDF files. If the box flames out, so does everything you have... databases and backups.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 5, 2015 at 5:36 pm

    #1788821

    Hi Grant

    Thanks for replying.

    Actually on our production server, we have a standard maintenance plan setup that runs the "Check Database Integrity Task" nightly before taking a full backup. I understand that this task does exactly the same as "DBCC CheckDB", correct me if I am wrong.

    What do you mean by a PHYSICAL ONLY check? What type of command should I run to do this check?

    PS. I just got this new position as a Junior DBA in my company. The backups used to be managed by our sys admins. They never did restore tests so I am trying to put this in place. So please excuse me if I ask basic questions 😀

  • April 5, 2015 at 5:47 pm

    #1788823

    Hi Jeff

    Thanks for replying

    Thats a good point about not copying the backup file. You are right, I decided to go down the Powershell path because of wanting to copy the files to the test server.

    I guess I could get the name of the file to restore by looking up the backup device views in msdb.

    Thanks for pointing out the backup and mdf/ldf file storage locations, I'll confirm with our system admins.

    As mentioned in my other post, I just got this position as Junior DBA 😀 so I am open to any suggestions for improvement

  • April 5, 2015 at 6:42 pm

    #1788826

    efyuzegeekgurl (4/5/2015)

    Hi Jeff

    Thanks for replying

    Thats a good point about not copying the backup file. You are right, I decided to go down the Powershell path because of wanting to copy the files to the test server.

    I guess I could get the name of the file to restore by looking up the backup device views in msdb.

    Thanks for pointing out the backup and mdf/ldf file storage locations, I'll confirm with our system admins.

    As mentioned in my other post, I just got this position as Junior DBA 😀 so I am open to any suggestions for improvement

    In that case, I have two areas of advice for new DBAs and I carry them in my signature line below. There's a third that should go without saying...

    Before you can think outside the box, you must first realize... you're in a box.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 5, 2015 at 7:20 pm

    #1788829

    Jeff Moden (4/5/2015)

    efyuzegeekgurl (4/5/2015)

    Hi Jeff

    Thanks for replying

    Thats a good point about not copying the backup file. You are right, I decided to go down the Powershell path because of wanting to copy the files to the test server.

    I guess I could get the name of the file to restore by looking up the backup device views in msdb.

    Thanks for pointing out the backup and mdf/ldf file storage locations, I'll confirm with our system admins.

    As mentioned in my other post, I just got this position as Junior DBA 😀 so I am open to any suggestions for improvement

    In that case, I have two areas of advice for new DBAs and I carry them in my signature line below. There's a third that should go without saying...

    Before you can think outside the box, you must first realize... you're in a box.

    Thank you for the advice 😀

  • April 6, 2015 at 4:53 am

    #1788865

    efyuzegeekgurl (4/5/2015)

    Hi Grant

    Thanks for replying.

    Actually on our production server, we have a standard maintenance plan setup that runs the "Check Database Integrity Task" nightly before taking a full backup. I understand that this task does exactly the same as "DBCC CheckDB", correct me if I am wrong.

    What do you mean by a PHYSICAL ONLY check? What type of command should I run to do this check?

    PS. I just got this new position as a Junior DBA in my company. The backups used to be managed by our sys admins. They never did restore tests so I am trying to put this in place. So please excuse me if I ask basic questions 😀

    Yeah, your Maintenance Plan is doing CHECKDB.

    If you are testing backups through the restore process, then, you can offload half the checks that DBCC does, the expensive half. DBCC does several sets of checks, but they can be broken down to physical and logical. The physical checks are against the actual underlying storage system, validating that all the page linkages are in place, that all the pages are healthy, etc. The logical check looks at all the internal logical consistency of the storage of the data within the pages. You can read about both in the books online (your bestest friend as a DBA. It should be the the place you start most questions that you have). The physical checks must be done where the database lives. But the logical checks can be done anywhere. If there are logical inconsistencies in storage, they'll be included in the backups. So, by testing your restore, you actually provide the capability to move the logical checks to a different location.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 9 posts - 1 through 8 (of 8 total)

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