Creating Copy of backups

  • 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 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.


  • 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.

  • 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.

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

  • How about some details?

  • Actually just found this link - - when looking for Robocopy help. Might be interesting.

    Else, 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!



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

  • The wikipedia entry gives all the command line options :

    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.

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

  • 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?

  • 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 :


    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.

  • 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.

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

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