Post reply

Automate refreshing of test server

  • August 2, 2011 at 9:20 am

    #254796

    Hello I wish to Automate refreshing of test server. After every weekend backup of the prod server, I wish the test server to recover the data from prod server. Any suggestions, of how this can be achieved? Right now I am doing it manually every week, but would like to automate it. When I try to set it up as job, I cannot use the other server to read backup from. It uses the same server backup data.

    Please help.

    Thanks

  • August 2, 2011 at 10:14 am

    #1362312

    Put a folder share on the backup folder.

    Then restore with the path \\BACKUPMachinName\SharedFolder\prod-date.back

    I usually give full control access to the machine rather than a specific user. You need to do that both on NTFS and SHARE.

  • August 2, 2011 at 10:14 am

    #1362313

    we tend to prep our backup jobs to have a last step to copy the .bak files to a safe zone.

    Restores towards test servers are being performed using these safe zone files, so it cannot interrupt the actual backup processes of the prod server.

    However, we monitor backup jobs for failures, so we can prevent them for the future.

    ( if it fails, mostly it is the xcopy step and a restore job that got stuck because of the slower media )

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • August 4, 2011 at 8:06 pm

    #1364066

    I have used DTS and SSIS Packages and scheduled them to run as a job.

    You will need a script task to determine the most current backup to restore the database.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • August 25, 2011 at 3:07 pm

    #1373516

    Hello Ninja,

    I have got the backupfolder share. But the thing is if I follow the wizard to restore, it points only to the folder on the same server and not to any other server. How do I put in the n/w path that you suggested? \\BACKUPMachinName\SharedFolder\prod-date.back

    The restore wizard doesn't take this path. Could you please advice?

  • August 25, 2011 at 3:26 pm

    #1373522

    tracmonali (8/25/2011)

    I have got the backupfolder share. But the thing is if I follow the wizard to restore, it points only to the folder on the same server and not to any other server. How do I put in the n/w path that you suggested? \\BACKUPMachinName\SharedFolder\prod-date.back

    The restore wizard doesn't take this path. Could you please advice?

    Try performing this in SSMS. Once you get it to work create a Execute SQL Task and copy and paste your code.

    You should be good to go.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • August 25, 2011 at 3:33 pm

    #1373523

    Hello Welsh Corgi,

    I did try with SSMS. That where it asks for path of the same server. Maybe I will try to script it and then tweak it to the n/w path.

    I checked your post and you did mention about the SSIS package. How do I go about doing that? YOur suggestions appreciated

  • August 26, 2011 at 2:01 pm

    #1374093

    Can't use the network share in the wizard.

    What I do to debug this is to run xp_cmdshell 'dir \\share\path\bak.name'

    Once I get that working I know I've solved all the issues (permissions usually #1, #2 needing to wrap the path with double quotes in the dir command, but then you have to take them out for the restore command).

  • August 28, 2011 at 2:18 pm

    #1374326

    You could also create a backup device that refers to the wanted unc backup file and use that in your restore statement.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • August 28, 2011 at 3:36 pm

    #1374341

    You can perform it in T-SQL.

    RESTORE DATABASE DBName

    FROM DISK = '\\Server\Share\Folder\BackupFile.bak'

    WITH REPLACE

    You can add the code to your SSIS Package.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • August 29, 2011 at 11:10 am

    #1374598

    ...

Viewing 11 posts - 1 through 10 (of 10 total)

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