question

zillabaug avatar image
zillabaug asked

How do I copy 2 most recent bak files from one share to another over the network

I have a request from a vendor to copy two most recent backup files from a network share to a different network share.Does anyone have a PowerShell script to accomplish this task? I tried using robocopy to copy the files from share A to share B using windows batch file scheduled as a job in SQL server agent but the job keep failing. However, the batch file works fine when run manually by double clicking on it.Any pointers folks? Thanks, folks. Here is a snippet of the code used in my batch file: ROBOCOPY \\shareA\site\servername\Databasename\FULL \\shareB\SQLDumps /maxage:2
sql server 2014powershellsql-script-filesql server agentrobocopy
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What's the error when the job fails?
0 Likes 0 ·
Executed as user: NT Service\SQLSERVERAGENT. ...Process Exit Code 3. The step failed. The error msg in the history isn't detailed to troubleshoot
0 Likes 0 ·

1 Answer

· Write an Answer
KenJ avatar image
KenJ answered
The SQL Agent account, NT Service\SQLSERVERAGENT, is a local computer account which doesn't have permission to the file shares within the network. As the [documentation][1] mentions, > The SQL Server Agent service startup account defines the Windows account that > SQL Server Agent runs as, **as well as its network permissions** There are several ways to correct this. Here are the first few ways that come right to mind... * set up SQL Server Agent to run under a domain account with appropriate permission to the shares ([ https://docs.microsoft.com/en-us/sql/ssms/agent/set-service-startup-account-sql-server-agent-sql-server-configuration-manager][2]) * set the job step up to run under a proxy account for a domain account with sufficient permission ([ https://docs.microsoft.com/en-us/sql/ssms/agent/create-a-sql-server-agent-proxy][3]) * add a step to the SQL Agent job that maps the share as a local drive with NET USE, providing domain credentials for an account with appropriate credentials ([ https://technet.microsoft.com/en-us/library/bb490717.aspx][4]) [1]: https://docs.microsoft.com/en-us/sql/ssms/agent/set-service-startup-account-sql-server-agent-sql-server-configuration-manager [2]: https://docs.microsoft.com/en-us/sql/ssms/agent/set-service-startup-account-sql-server-agent-sql-server-configuration-manager [3]: https://docs.microsoft.com/en-us/sql/ssms/agent/create-a-sql-server-agent-proxy [4]: https://technet.microsoft.com/en-us/library/bb490717.aspx
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.