Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
1  
Not exactly what you're asking, but I blogged something very similar just today: spaghettidba.com/2013/02/08/manual-log-shipping-with-powershell –  spaghettidba Feb 8 '13 at 15:44

1 Answer 1

up vote 1 down vote accepted

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
share|improve this answer
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 8 '13 at 21:37
    
good point. i missed the "backup" part –  swasheck Feb 8 '13 at 21:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.