Moving and restoring the db from latest backup -- Priority

Question

Post reply

Moving and restoring the db from latest backup -- Priority

ramana3327

SSCoach

Points: 19700
More actions
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

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Lynn Pettis

SSC Guru

Points: 442467

September 15, 2015 at 11:07 am

#1826830

Look at Robocopy.

vedau SSC Veteran Points: 289 More actions · Answer 2

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!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

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.

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 4

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.

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 5

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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

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.

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 7

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 🙂

Perry Whittle SSC Guru Points: 233866 More actions · Answer 8

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" 😉

Perry Whittle SSC Guru Points: 233866 More actions · Answer 9

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" 😉

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 10

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)

Gazareth One Orange Chip Points: 27737 More actions · Answer 11

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 🙂

Sqlraider SSCertifiable Points: 5441 More actions · Answer 12

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.

ramana3327 SSCoach Points: 19700 More actions · Answer 13

ramana3327

SSCoach

Points: 19700

September 22, 2015 at 1:21 pm

#1828554

This should run daily

DeWayne_McCallie Hall of Fame Points: 3617 More actions · Answer 14

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.