Copying / Moving Files using SQL Server Agent Jobs

Moving backup files from one location to another is a common DBA task. Obviously we could do this manually, but our goal as DBAs is to automate as much of the monkey work as possible, so that we can get on with the really interesting challenges (or just go and get a coffee).

OPTION 1: Windows Task Scheduler
If we want to regularly run a job in Windows, we have the option of using Windows Task Scheduler. But I wonder how you would integrate this consistently and reliably with a SQL Server backup job? What if the backup fails – the windows task will still attempt to run. What is the backup is corrupted – the windows task will still run and you risk getting a corrupted backup chain. And to be certain, you would have to schedule the file move task well after the expected end time of the backup, meaning the backup file will be sitting around for a while, potentially a problem if you are doing very regular backups.

OPTION 2: SQL Server Agent Job
Thankfully, we can use an Agent job to accomplish this and synch the timing inline with the backup jobs. There is just one gotcha: by default SQL Server jobs are run as the default SQL Server Agent Account, which on a default install is the local account:

NT Service\SQLSERVERAGENT

Usually, this account will not have sufficient privileges to copy files on your local machine, and certainly won’t have the privileges to transfer files across network locations. If this is the case, you will see an “Access is denied” error. The solution is to set up a PROXY user account to execute the job. We need to include appropriate security credentials, create a proxy for a certain job and then run the job under the new proxy.

Adding User Credentials
We need to include the username and password to SQL Server’s security credentials, the user needs to have read / write privileges on the source and target filesystems:

USE [master]
GO
CREATE CREDENTIAL [credentialName] WITH IDENTITY = N'username', SECRET = N'password'
GO

Create the job proxy
We then create a PROXY account which will be used to run the job. The current proxy accounts are held in msdb.dbo.sysproxies:


select * from [msdb].[dbo].[sysproxies]


There is an in-built stored procedure to add a new proxy, you will need to supply a new name for the proxy, the credential to associate it with:


EXEC msdb.dbo.sp_add_proxy @proxy_name=N'testProxy2',@credential_name=N'testCredential',
@enabled=1,
@description=N'some description'
GO
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'testProxy2', @subsystem_id=3
GO


Of course, this is probably easier through the GUI:

Run Job as Proxy User
Finally, create yourself a new Agent job and under the move file step select run as :

Make the step type OS(CmdExec), and choose it to run under your new proxy; type in your copy / move command, or link it to a batch file and hey presto you are good to go.