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.

MS SQL Server 2012... with nightly databases backups to the same/local as SQL Server machine...

I am trying to add another SQL Server Agent Job to copy the .bak files to remote (non-windows, i.e. Linux) share with non-Windows (non-AD) user/password credentials. I do not have any access to configuring or changing that access which is under control of other, quite remote people.

For this (copying) I created local user with the same user name and password, gave it permissions to the (source or local) backup-folders upon which all perfecly works from command line (Win + К or cmd) if to enter the command manually:

RUNAS /user:UserName /savecred "robocopy d:\SQLBACKUP  \\10.195.xx.yyy\backup /S /purge /MAXAGE:7 /MT:1 /Z"   

but fails to run as SQL Server Agent job (type of step is "Operating System(CmdExec)". SQL Service Agent (with standard configuration of running under [NT Service/SQLServiceAgent] account, the job is owned by SA SQL Server superuser).

Can anybody explain me why it is failing and how to correctly make it running (taking into account that I do not have access to domain users configuration)?

share|improve this question
    
Have you considered running the backup using a command line program and a scheduled task or PowerShell job instead of SQL Server Agent? –  Aaron Bertrand Oct 14 '13 at 12:15
    
@AaronBertrand, yes, I eventually was forced to run it through Windows Server 2008 Task Scheduler but it is unfortunate since: 1)it is decentralized; while logically and physically it depends on completion of (source for copy target) local backup through maintenance plans by SQLServerAgent job; 2)difficult to see how long it took or why they failed - the info is buried among thousands of (note it is on heavily used app server) app events (in eventvwr.msc; the copying takes hours); –  Gennady Vanin Геннадий Ванин Oct 14 '13 at 17:27
    
3)the last but not least - I'd like to understand why SQLServerAgent job fails and how to correctly succed with it. It either starts and fails with no specific info in 3-4 min or hangs indefinitely without any errors and really not starting any copying –  Gennady Vanin Геннадий Ванин Oct 14 '13 at 17:27
    
You know that you can run the backup that way too, not just the copy, so you don't have this dependency on some maintenance plan... –  Aaron Bertrand Oct 14 '13 at 17:29

1 Answer 1

up vote 1 down vote accepted

To answer the specific question of why it is failing is that the service account on your server does not have the correct permissions to access the share.

I have an environment where I have 2 servers not on a domain. Using SQL Server Agent proxies is the way I solved this.

I have a special account I use for the 2 machines (Your accounts with the same name and password) which I had to add to the instance level credentials (under security in SSMS Object explorer) (see MSDN article "Create a Credential" ) and add an agent proxy under the Operating System (CmdExec) .

Once those are created you can select the account to run as (the name you gave your proxy) and it will work. However this is on 2008 and I have not played with the 2012 agent that much.
Should still work the same though.

You would then use your robocopy line "robocopy d:\SQLBACKUP \10.195.xx.yyy\backup /S /purge /MAXAGE:7 /MT:1 /Z" as your command (I use xcopy but it works the same).

share|improve this answer
    
Thanks for the edit. Ill use this style in my future postings. –  Wind Raven Oct 15 '13 at 18:43
    
I just wanted to insert the space between source and destination directory in copy-pasted from my answer robocopy command (which originally was with an error) but the edit with less than 6 symbols changed was not accepted. So, I had to expand my editing... –  Gennady Vanin Геннадий Ванин Oct 16 '13 at 4:23

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.