Automating File Backups for Disaster Recovery | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Automating File Backups for Disaster Recovery

Problem: Create a SQL server agent job to backup a database and copy it to a linked server for disaster recovery purposes Configuration:
Source(primary) server is Windows 2003 server standard edition(SE) / SQL Server 2000 SE
Destination Linked (standby) server is Windows 2003 server standard edition(SE) / SQL Server 2000 SE Server names:
Primary server name: cvsql1
Standby Linked server name: cvsql2 Windows Userid on both servers is administrator and the password is the same on both servers These are standalone servers in a workgroup. No Active directory or DC’s Explaination: (for this example the database name is DB)
Using SQL Server Agent I can create a job that creates DB.BAK without any problems. But in another job I can’t get the following statement to work: EXEC xp_cmdshell ‘copy c:DB.BAK \cvsql2c$*.*’ The job history says "Executed as user: NT AUTHORITYSYSTEM. The step succeeded."
But, \cvsql2c:DB.BAK is not created I’ve mapped a drive to cvsql2 and can manually copy DB.BAK there.
Or, from the command prompt copy c:DB.BAK \cvsql2c$*.* is successful Anyone have ideas or links showing how this might be accomplished?? TIA
Execute permissions for xp_cmdshell default to members of the sysadmin fixed server role, but can be granted to other users. Important If you choose to use a Windows NT account that is not a member of the local administrator’s group for the MSSQLServer service, users who are not members of the sysadmin fixed server role cannot execute xp_cmdshell. Could be?
Luis Martin
Moderator One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
I think I verified that SQL Agent can exec xp_cmdshell by setting up a job with 1 step with the following statement: EXEC xp_cmdshell ‘copy c: est.txt c: estcopy.*’ The job, successfully created c: estcopy.txt But, I cannot create a file on the linked server cvsql2 with the similar statement: EXEC xp_cmdshell ‘copy c:DB.BAK \cvsql2c$*.*’ Thanks for your help… I’m out of ideas to make the copy work. Ceje
Just a bit more information to make it interesting.<br /><br />I mapped drive z: to \cvsql2c$*.* <br />and then tried to <br />EXEC xp_cmdshell ‘copy c:DB.BAK z:*.*'<br /><br />and that also failed to create the desired copy on the standby server.<br /><br />Any thoughts out there???? <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br />
What user is the SQL Server service running under on cvsql1? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
True and ensure both the SQLAgent account has similar name if you’re using them as a local accounts with proper privileges. Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
I set the SQL Agent system startup account to cvsql1administrator. cvsql2administrator has the same windows password. The are both local accounts. I ran the one step job:
EXEC xp_cmdshell ‘copy c:DB.BAK \cvsql2c$*.*’ and the job history shows:
Executed as user: CVSQL1Administrator. The step succeeded. But, the file was not copied to \cvsql2c$. I set permissions on cvsql2administrator for full control and it still failed to copy. As I said in the first OP I can accomplish this from the CMD prompt. I guess I’ll just write a batch file with net use to establish communication to cvsql2 first, and then within that batch file do the copy. I’ll post the results. If you have other suggestions, please let me know. Thanks