SQL Server DBA

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:proxyImage

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

jobProxyImage

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.

Advertisements

10 thoughts on “Copying / Moving Files using SQL Server Agent Jobs

  1. Pingback: 70:462 Administering SQL Server 2012 – Reflection | :: NickBurns

  2. Pingback: Transfer files between network path using SQL Server agent job | SQL Server

    • Thanks Aaron. It is always interesting to see the many many ways people are moving data around. No matter how high-tech (e.g. AlwaysOn or replication) or how low-tech (agent jobs, cron jobs) they all come unstuck on permissions. We are dealing with a thorny one at the moment as well, and once again it is all to do with permissions!

      Like

  3. Thanks for this article. I tried to copy the file with the logic of vb scripting. That didn’t do the trick. Then I used the command ‘Copy’ as described in your article and it worked.

    Can we use vb scripting in the command editor or do we’ve to use sql commands

    Like

    • Sorry, realised I didn’t totally answer your question. To e specific – you can only enter T-SQL into the command editor shown above. So you wouldn’t be able to use VB or C# directly into that window. But there is no reason (that I know of) why that T-SQL couldn’t be a call to a compiled CLR assembly that exists within your database.

      Like

  4. Great blog you have here but I was curious if you knew of any community forums that cover the same topics talked about here?

    I’d really love to be a part of online community where
    I can get advice from other experienced individuals that share the
    same interest. If you have any recommendations, please let me know.
    Thank you!

    Like

    • Hi there, sorry for the delay. In terms of SQL Server, SQL Server Central has to be the best community out there. Google them and sign up for their newsletters. Also worth following Brent Ozar and his team.

      Regards,
      Nick

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s