Automating File Backups for Disaster Recovery - copy to standby server

Question

Automating File Backups for Disaster Recovery - copy to standby server

Ceje

SSC Veteran

Points: 269
More actions
September 17, 2005 at 11:18 am

#110011

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
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 \\cvsql2\c$\*.*'
The job history says "Executed as user: NT AUTHORITY\SYSTEM. The step succeeded."
But, \\cvsql2\c:\DB.BAK is not created
I've mapped a drive to cvsql2 and can manually copy DB.BAK there.
Anyone have ideas or links showing how this might be accomplished??
TIA

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 1

Can you change the sql agent service account to the administrator account instead of NT AUTHORITY\SYSTEM? So far I know local system account has no rights on any networking stuff.

If you run EXEC xp_cmdshell 'copy c:\DB.BAK \\cvsql2\c$\*.*' does it work then?

Ceje SSC Veteran Points: 269 More actions · Answer 2

I've tried that using NT AUTHORITY\administrator, NT AUTHORITY\ADMINISTRATOR and NT AUTHORITY\ANONYMOUS and I get the following errors:

error 15401: nt windows user or group 'NT AUTHORITY\administrator'not found. check the name again.

Clicking ok displays:

error 15007: the login 'NT AUTHORITY\administrator' does not exist.

using just administrator displays:

error 15407: 'administrator' is not a valid window nt name. give the complete name: domain\username.

any other suggestions?? TIA

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 3

Normally it is computername\administrator.

You can find a valid list of users in usermanagement in windows.

(Start->Settings->Control Panel->Users & Passwords)

Ceje SSC Veteran Points: 269 More actions · Answer 4

You'r right!!

I tried cvsql1\administrator and \\cvsql1\administrator and got the same errors:

The sql server agent account could not be verified. Are you sure you want to use it?

I click yes and get

Error 15401: Window NT user or group '\\cvsql1\administrator' not found. Check name again.

Thanks

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 5

cvsql1\administrator should be correct

My account in SQLServerAgent service got abbreviated to

.\administrator

I hope this helps.

Ceje SSC Veteran Points: 269 More actions · Answer 6

I tried setting the system startup account to cvsql1\administrator and this time it worked without errors.

I ran the one step job:

EXEC xp_cmdshell 'copy c:\DB.BAK \\cvsql2\c$\*.*'

and the job history shows:

Executed as user: CVSQL1\Administrator. The step succeeded.

But, the file was not copied to \\cvsql2\c$

I set permissions for administrator for full control and it still failed to copy.

I think I can work around this with a batch file run on cvsql1 that will authenticate to cvsql2. I'll post my results of that testing when finished.

Thanks for all your help. If you have other ideas, please let me know.