Copy files using script to a network share

Question

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

Answer 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