How to copy backup files to shared drive daily with sql server 2012


  • Hello Friends,

    I have created backup database jobs in sql server 2012, step 1 takes backup for databases which is running fine.

    For example:

    BACKUP DATABASE [master] TO  DISK = N'E:\Backups\master-backup.bak' WITH  INIT ,  NOUNLOAD ,  NAME = N'master',  NOSKIP ,  STATS = 10,  NOFORMAT, CHECKSUM

    BACKUP DATABASE [msdb] TO  DISK = N'E:\Backups\msdb-backup.bak' WITH  INIT ,  NOUNLOAD ,  NAME = N'msdb',  NOSKIP ,  STATS = 10,  NOFORMAT, CHECKSUM

    In job step 2, I wrote code to copy backup to share drive as below:

    copy E:\Backups\master-backup.bak """\\ABC\Database_Backups\Data\master-backup.bak""" /Y

    copy E:\Backups\msdb-backup.bak """\\ABC\Database_Backups\Data\msdb-backup.bak""" /Y

    The problem is job getting run successfully but in step 2, only copying the 1 file which listed 1st in step 2 and it is not copying all the files.

    In step 2 I want to copy all backup files which was in step 1. Is there a way to make this is working without creating more steps?

    please advise.

    Thank you in advance.

    Monday, January 13, 2014 8:26 PM


All replies

  • Similar topic:

    You can try:

    copy E:\Backups\master-backup.bak """\\ABC\Database_Backups\Data\master-backup.bak""" /Y && copy E:\Backups\msdb-backup.bak """\\ABC\Database_Backups\Data\msdb-backup.bak""" /Y

    • Marked as answer by J_Newbie Thursday, January 16, 2014 4:00 PM
    Monday, January 13, 2014 9:41 PM
  • Still doing same thing. && is not working :(
    Monday, January 13, 2014 9:48 PM
  • Put the COPY commands in a .BAT file?

    Erland Sommarskog, SQL Server MVP,
    Monday, January 13, 2014 10:52 PM
  • Hi J_Newbie,

    According to your description, I do a test, if you want to copy backup to share drive via in SQL Server Agent job, you can use extended stored procedure "xp_cmdshell", please refer to the following T-SQL statement in the job steps 2.

    EXEC master.dbo.xp_cmdshell 'copy E:\Backups\master-backup.bak \\ABC\Database_Backups\Data\master-backup.bak’;
    EXEC master.dbo.xp_cmdshell 'copy E:\Backups\msdb-backup.bak \\ABC\Database_Backups\Data\msdb-backup.bak’;

    You need to note before you move the saving location form disk to share drive, you should make sure that your account has been grant read/write permission to the share folder.

    Generally speaking, moving the file is windows work. If your backup job is already scheduled you can modify it and take multiple backups in the same job. Or you can also create a SSIS package which uses the System File task to copy files from one location to another location, then, create a SQL Server Agent job to execute this package, Or you can use Windows Scheduler to copy files.

    Sofiya Li

    Sofiya Li
    TechNet Community Support

    Wednesday, January 15, 2014 9:31 AM
  • When I typed all in 1 line like below it worked.

    copy E:\Backups\master-backup.bak """\\ABC\Database_Backups\Data\master-backup.bak""" /Y && copy E:\Backups\msdb-backup.bak """\\ABC\Database_Backups\Data\msdb-backup.bak""" /Y

    Thanks for help!!!

    Thursday, January 16, 2014 4:03 PM