1

Is there a simple script I can use to copy files in a particular folder to a network share? This is for use with SQL Server Agent so I can run it as a step after backups.

Thanks

medina
  • 197
  • 1
  • 2
  • 7
  • 1
    Not exactly what you're asking, but I blogged something very similar just today: http://spaghettidba.com/2013/02/08/manual-log-shipping-with-powershell/ – spaghettidba Feb 08 '13 at 15:44

1 Answers1

1

This is (part of) a script that I use to "archive" files that were processed by SQL Server. I actually mapped the network drive instead of moving to a UNC because it was just more stable.

A few things to note:

  • The service account under which the SQL Agent runs must have permissions on both the source and the target (I'm assuming the target would be the one you'd really need to check, unless the Agent account can't delete from the local store).
  • This script MOVEs the files. If you want to copy them, then I'd recommend looking into replacing the command in the last step with XCOPY


    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', 
       @type=N'LOCAL',@name=N'[Uncategorized (Local)]'

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    END

    DECLARE @jobId BINARY(16)
    EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'File Mover', 
            @enabled=1, 
            @notify_level_eventlog=0, 
            @notify_level_email=2, 
            @notify_level_netsend=0, 
            @notify_level_page=0, 
            @delete_level=0, 
            @description=N'No description available.', 
            @category_name=N'[Uncategorized (Local)]', 
            @owner_login_name=N'[username]', 
            @notify_email_operator_name=N'alertteam', @job_id = @jobId OUTPUT

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Archive Files', 
            @step_id=3, 
            @cmdexec_success_code=0, 
            @on_success_action=1, 
            @on_success_step_id=0, 
            @on_fail_action=2, 
            @on_fail_step_id=0, 
            @retry_attempts=0, 
            @retry_interval=0, 
            @os_run_priority=0, @subsystem=N'CmdExec', 
            @command=N'move \source\path\*.csv \dest\path
    ', 
            @flags=0
swasheck
  • 10,665
  • 4
  • 47
  • 88
  • 1
    swasheck's answer will create you a new job with a copy step inside. If you just want to add a new step to an existing backup job then take the last part (with sp_add_jobstep), but be sure to add it in the context of the already existing backup job. – Marian Feb 08 '13 at 21:37
  • good point. i missed the "backup" part – swasheck Feb 08 '13 at 21:38