Moving a backup file to another location as part of a SQL Job

Question

Moving a backup file to another location as part of a SQL Job

adam.spittles

SSC-Addicted

Points: 407
More actions
September 20, 2012 at 7:40 am

#264599

Hi all,
First post so hopefully this goes OK!
I have a SQL Server 2008 instance with 2 150GB databases which we do a full backup of every night.
Every night we back these databases up onto tape. When these were originally set-up (before my time), the full SQL Backups are dropped into the same folder in which the tape backs up from, so the tape is trying to backup this location while we are hammering the folder with writes from SQL. Not only is this causing the tape to estimate backup times of over 30 hours (not ideal for daily tape backups), it is also causing the SQL job to over run and slow other database tasks which we need to run overnight.
In order to get past this I want to experiment with SQL backups to a local drive on the server and then once the SQL backup has completed, move this backup file into a location where the tape can backup all as part of one SQL Server job.
Before I posted I done some searching and some people have used the xp_cmdshell procedure to do the copy. I wanted to see if this was the best way, or is there a far more efficient way of doing this.
I can happily write VB scripts, do the SQL maintenance plans and write the DOS commands to make the xp_cmdshell work, I just wanted a second opinion.
Thanks
Adam

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

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

Ant-Green SSC Guru Points: 113565 More actions · Answer 1

Number of options really

Write a bat file which uses Robocopy / XCopy (or another DOS copy command) to do the copies, add that in as an extra step in the job under an operating system cmdexec step and use a proxy if the Agent service account doesn't have access rights.

Change the tape backup to only backup certain folders at certain times, so if you know the backups finish at 2am then set the tape to run at 2:10am

Change the tape backup to backup the local drive instead of the remote drive again at a time which is just after the time the backup finishes.

adam.spittles SSC-Addicted Points: 407 More actions · Answer 2

anthony.green (9/20/2012)
Number of options really
Write a bat file which uses Robocopy / XCopy (or another DOS copy command) to do the copies, add that in as an extra step in the job under an operating system cmdexec step and use a proxy if the Agent service account doesn't have access rights.
Change the tape backup to only backup certain folders at certain times, so if you know the backups finish at 2am then set the tape to run at 2:10am
Change the tape backup to backup the local drive instead of the remote drive again at a time which is just after the time the backup finishes.

Hi Anthony and thanks for the quick reply.

Option 1 fits exactly what I am looking for. I knew I'd seen an option somewhere, I just couldn't remember where!

Probably should have read the forum rules a bit better before posting but do I need to mark this thread as resolved, or is that not required?

Thanks

Adam

Ant-Green SSC Guru Points: 113565 More actions · Answer 3

You can't mark a topic as resolved on here.

Another option would be to enable xp_cmdshell and run the DOS command directly in the job step.

adam.spittles SSC-Addicted Points: 407 More actions · Answer 4

Ok,

I will experiment with both and see which one I get on better with.

Thanks again

Adam

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

Just an aside, you can also setup RoboCopy to act as a service and automatically move the files to another location when the backup has completed. I did this at a previous employer as it simplified the backup process.

Ant-Green SSC Guru Points: 113565 More actions · Answer 6

Lynn Pettis (9/20/2012)
Just an aside, you can also setup RoboCopy to act as a service and automatically move the files to another location when the backup has completed. I did this at a previous employer as it simplified the backup process.

Never knew that, will have to take a look at that as I do a lot of robocopy bat files across all of our server estate.

adam.spittles SSC-Addicted Points: 407 More actions · Answer 7

Lynn Pettis (9/20/2012)
Just an aside, you can also setup RoboCopy to act as a service and automatically move the files to another location when the backup has completed. I did this at a previous employer as it simplified the backup process.

Hi Lynn,

Thanks for this. I have never set it up as a service before, but it's something I will do some research on and indeed give a go.

Because this server is a 2003 instance I don't believe RoboCopy is a valid command, however I am sure with a bit of help from google I can get around it!

Ant-Green SSC Guru Points: 113565 More actions · Answer 8

Ah, you need the RKTool kit found here http://www.microsoft.com/en-us/download/details.aspx?id=17657

adam.spittles SSC-Addicted Points: 407 More actions · Answer 9

anthony.green (9/20/2012)
Ah, you need the RKTool kit found here http://www.microsoft.com/en-us/download/details.aspx?id=17657

Love it.

Just added that to my toolbox!