Creating Copy of backups

Question

Creating Copy of backups

SQLRocker

SSCrazy

Points: 2217
More actions
August 21, 2008 at 10:14 pm

#191711

We have Full backups taken for all dbs every night using maintenance plan of Litespeed. T-log backups are taken hourly , again by the maintenance plans of litespeed. These backups are going to a drive on the server. I need a script/job to automatically make a copy of these backups to another drive on the same server. The version of Litespeed is 4.8.3.00025 which does not have "backup mirror" function for maintenance plans (is there for individual backups). I checked out the "backup mirror" of native SQL but that needs to have "WITH FORMAT" in it, & will not work in my case. Appreciate your suggestions.
Thx

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

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

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 1

You could just use Robocopy or similar, very easy to set up so that only files with the archive flag set are copied (and then unflagged). You could run it on the Windows scheduler at whatever interval suits you.

SQLRocker SSCrazy Points: 2217 More actions · Answer 2

Can you give details?

I have tried ROBOCOPY before however I could not give time to understand its commands , It would save a lot of time for me if you have a example or script detailing the process with the windows scheduler.

homebrew01 SSC Guru Points: 55539 More actions · Answer 3

Could you just add a DOS copy command as the next step in your backup procedure ?? I used to do something similar.

SQLRocker SSCrazy Points: 2217 More actions · Answer 4

SQLRocker

SSCrazy

Points: 2217

August 22, 2008 at 7:50 am

#861893

How about some details?

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 5

Actually just found this link - http://technet.microsoft.com/en-us/magazine/cc160891.aspx - when looking for Robocopy help. Might be interesting.

Else, http://www.mydigitallife.info/2007/05/07/robocopy-syntax-command-line-switches-and-examples/ is a link with a good bit of information.

If you search Robocopy on the net you should get adequate examples for what you are trying to do.

Have fun!

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 6

The wikipedia entry gives all the command line options : http://en.wikipedia.org/wiki/Robocopy

The basic syntax is along these lines: robocopy C:\source C:\target /M /E (where the /M option copies only the files with the archive flag set and the /E option includes subdirectories). You would probably want different options depending on your requirements.

Put the command into a .bat file, and run the .bat file from the windows scheduler.

SQLRocker SSCrazy Points: 2217 More actions · Answer 7

SQLRocker

SSCrazy

Points: 2217

August 22, 2008 at 9:15 am

#861976

Thx for the info. guyz. I will check up on it.

SQL33 SSCrazy Points: 2044 More actions · Answer 8

This sounded like a great plan, but when I tried installing it, I set it up .. click "RUN" and nothing happens...

P.S. .. I'm running it in a Terminal Sevice session. is that allowed?

SQLRocker SSCrazy Points: 2217 More actions · Answer 9

This is what I have tried so far :

I created a test maintenance plan for a test db backup.

I added a new step to the existing job for backup of this test db. Used XP_CMDSHELL as type , The command I am using is :

XCOPY G:\BACKUPS\TEST F:\BACKUPS\TEST /D /Y

This is working fine for one db backup maintainence plan.

Only the new backups are being copied to F which is what I need.

I also created 2 more testdbs & created a maintenance plan for their backups, then I created 2 new steps :

XCOPY G:\BACKUPS\TEST F:\BACKUPS\TEST /D /Y ---------- for step 2

XCOPY G:\BACKUPS\TEST1 F:\BACKUPS\TEST1 /D /Y ----------for step 3

this works fine too.

What I have is a maintenance plan for all user db backups, I do not want to create 7 steps for the copy.

Is there a way I can tweak the above XCOPY command to have it in only one STEP.

I think the way to go is to create a batch file so that its commands can run from a single Job Step, I haven't worked with Batch files so am researching online.

Thanks Matt Stockham for your incisive comments.

SQLRocker SSCrazy Points: 2217 More actions · Answer 10

Issue Solved :

1. Added a new step to the existing Alluserdb backup Job for copying to the different drive. Created the folders required on the drive. Choose, T-sql as TYPE in job, command as :

EXEC xp_cmdshell 'xcopy G:\Tlogbackups\db1 F:\Tlogbackups\db1 /D /Y'

EXEC xp_cmdshell 'xcopy G:\Tlogbackups\db2 F:\Tlogbackups\db2 /D /Y'

and so on for other db's in the same step query.

This copies all files when you first run the job & afterwards just copies the new files to the appropriate folders.

Thanks all for their suggestions especially Matt Stockham & Homebrew01. Have a good one guyz.