Post reply

Moving and restoring the db from latest backup -- Priority

  • September 15, 2015 at 10:30 am

    #307798

    Hi,

    I am working with one of the task automating the db refresh in test server from production backup.

    I need your expertise advice

    The production backup sits on the production server local drive.

    Step: Moving the production server latest backup from its local drive to Test server local drive

    I need help with this above step

  • September 15, 2015 at 11:07 am

    #1826830

    Look at Robocopy.

  • September 15, 2015 at 11:39 am

    #1826849

    I had done something similar but to validate the backups. Here are the steps that I used.

    1. Share PROD backup folder so that you can access it on TEST.

    2. Use XCOPY or ROBOCOPY to move the backups to TEST.

    3. Enable xp_cmdshell SQL server (not recommended but you could just enable it for the duration of the job and then disable it)

    4. Restore all files in that folder/directory. I borrowed a script from Tibor Karaszi and Nucleus Datakonsult. If you cant find it on google, then let me know.

    5. I also ran a DBCC CHECKDB after the restore but because I was doing a backup verification. You dont have to do it.

    6. Use FORFILES to remove the old backup files from Test.

    7. After you have verified that it works, script step #2 to #6 to a job.

    Cheers!

  • September 15, 2015 at 11:48 am

    #1826854

    vedau (9/15/2015)

    I had done something similar but to validate the backups. Here are the steps that I used.

    1. Share PROD backup folder so that you can access it on TEST.

    2. Use XCOPY or ROBOCOPY to move the backups to TEST.

    3. Enable xp_cmdshell SQL server (not recommended but you could just enable it for the duration of the job and then disable it)

    4. Restore all files in that folder/directory. I borrowed a script from Tibor Karaszi and Nucleus Datakonsult. If you cant find it on google, then let me know.

    5. I also ran a DBCC CHECKDB after the restore but because I was doing a backup verification. You dont have to do it.

    6. Use FORFILES to remove the old backup files from Test.

    7. After you have verified that it works, script step #2 to #6 to a job.

    Cheers!

    Depending on server version, XCOPY may not be available as it is deprecated. Found that out years ago while working at another employer and setting a process to move backup files to an off-site server.

  • September 15, 2015 at 5:08 pm

    #1826929

    ramana3327 (9/15/2015)

    Step: Moving the production server latest backup from its local drive to Test server local drive

    Pedantic point but I expect that needs to be COPYING rather than MOVING as presumably the backup file needs to be left on the Production Server.

  • September 15, 2015 at 6:52 pm

    #1826950

    ramana3327 (9/15/2015)

    Hi,

    I am working with one of the task automating the db refresh in test server from production backup.

    I need your expertise advice

    The production backup sits on the production server local drive.

    Step: Moving the production server latest backup from its local drive to Test server local drive

    I need help with this above step

    1. How often do you need to do this to occur?

    2. How big is the backup file?

    3. Can the SQL Server login for the production box "see" the target drive on the test box (which would make this easy)?

    4. Do you know what a UNC is?

    5. Are you allowed to use xp_CmdShell (which would make this easy)?

    Shifting to a pedantic mode of my own, how long do the production backups sit on the production server local drive before they're backed up to tape or other storage? If the answer isn't very close to "immediately", you could experience very stressful moments in the future especially if you've also got the problem of the backup files living on the same disk as the database files.

    --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)

  • September 15, 2015 at 7:33 pm

    #1826951

    Kristen-173977 (9/15/2015)

    ramana3327 (9/15/2015)

    Step: Moving the production server latest backup from its local drive to Test server local drive

    Pedantic point but I expect that needs to be COPYING rather than MOVING as presumably the backup file needs to be left on the Production Server.

    Not necessarily. I worked on with a server where we only had room for one full backup and the daily log files. Moving the files off may be the only option if space is limited. Plus, where I have had issues with backups across the network failing due to a network issue, my restores across the network never had issues.

  • September 16, 2015 at 12:44 am

    #1826976

    Lynn Pettis (9/15/2015)

    Kristen-173977 (9/15/2015)

    ramana3327 (9/15/2015)

    Step: Moving the production server latest backup from its local drive to Test server local drive

    Pedantic point but I expect that needs to be COPYING rather than MOVING as presumably the backup file needs to be left on the Production Server.

    Not necessarily.

    Yes I quite agree that there may be reasons to move the backup file, rather than copy, but as the O/P has described this as an additional process for the existing backup file I thought more likely that the requirement would be for Copy rather than Move. I should have asked the question, rather than proposed the answer 🙂

  • September 16, 2015 at 7:28 am

    #1827058

    Jeff Moden (9/15/2015)

    5. Are you allowed to use xp_CmdShell (which would make this easy)?

    Powershell is even easier 😉

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • September 16, 2015 at 7:29 am

    #1827059

    ramana3327 (9/15/2015)

    Hi,

    I am working with one of the task automating the db refresh in test server from production backup.

    I need your expertise advice

    The production backup sits on the production server local drive.

    Step: Moving the production server latest backup from its local drive to Test server local drive

    I need help with this above step

    Is this a one off or regular task?

    Powershell is by far the easiest option

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • September 16, 2015 at 7:37 am

    #1827064

    Perry Whittle (9/16/2015)

    Jeff Moden (9/15/2015)

    5. Are you allowed to use xp_CmdShell (which would make this easy)?

    Powershell is even easier 😉

    I'll have to try it someday. 😉 Can it be scheduled to run through SQL Server Agent?

    --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)

  • September 16, 2015 at 7:56 am

    #1827069

    Lynn Pettis (9/15/2015)

    vedau (9/15/2015)

    I had done something similar but to validate the backups. Here are the steps that I used.

    1. Share PROD backup folder so that you can access it on TEST.

    2. Use XCOPY or ROBOCOPY to move the backups to TEST.

    3. Enable xp_cmdshell SQL server (not recommended but you could just enable it for the duration of the job and then disable it)

    4. Restore all files in that folder/directory. I borrowed a script from Tibor Karaszi and Nucleus Datakonsult. If you cant find it on google, then let me know.

    5. I also ran a DBCC CHECKDB after the restore but because I was doing a backup verification. You dont have to do it.

    6. Use FORFILES to remove the old backup files from Test.

    7. After you have verified that it works, script step #2 to #6 to a job.

    Cheers!

    Depending on server version, XCOPY may not be available as it is deprecated. Found that out years ago while working at another employer and setting a process to move backup files to an off-site server.

    There's also BITS (Background Intelligent Transfer Service) which, handily, has it's own PoSh module & cmdlets 🙂

  • September 17, 2015 at 9:58 am

    #1827477

    Jeff Moden (9/16/2015)

    Perry Whittle (9/16/2015)

    Jeff Moden (9/15/2015)

    5. Are you allowed to use xp_CmdShell (which would make this easy)?

    Powershell is even easier 😉

    I'll have to try it someday. 😉 Can it be scheduled to run through SQL Server Agent?

    Yes...the tricky part (at least for me) was getting the error codes/messages to pass from Powershell to SQL Server Agent.

  • September 22, 2015 at 1:21 pm

    #1828554

    This should run daily

  • September 22, 2015 at 2:42 pm

    #1828587

    If you have the disk space or the DB is not that large the easiest way is to backup the DB to a shared folder using a static named backup file (with init and copy_only) and restore on the target instance (using Replace). You can even build the job in SSMS and save the script (copy it into an Agent Job and schedule it). Nothing fancy just simple backup/restore commands.

Viewing 15 posts - 1 through 15 (of 18 total)

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