backup to network location

dililili · April 11th, 2006, 01:26 PM

I have server1 and server2. I'm trying to backup database of server2 to server1. This is what I wrote:

BACKUP DATABASE my_database TO DISK= '\\server1\DB_backup\my_database.bak'

The two servers maping each other. And I change both MSSQLSERVER log on account to administrator which can access both server.

But it shows me the error message:

Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\\server1\DB_backup\my_database.bak'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Anyone can tell me what should I do? Thanks a lot!!

Kep · April 11th, 2006, 08:08 PM

You can't backup to network drives or shares. Your only option would be to run a backup to the local disk and then copy the file to the network share.

For the copy you have a few choices. A couple that spring to mind are:-

1. Call xp_cmdshell

Code:

EXEC xp_cmdshell 'COPY localbackup.bak \\myserver\sharename\remotebackup.bak'

There are a number of things to consider with this. Firstly, your SQL server service account will need to have network access. Usually this is not the case because it runs under the LocalSystem account. Secondly, by default the only people who can run xp_cmdshell are members of the sysadmin fixed server role.

2. Create a SQL Agent job that copies the file.

This is similar to the above except that you specify that the job step is an 'Operating System Command (CmdExec)'.

This command runs under the context of the SQL Server Agent, so that service would need to be running as an account with network access.

Then you would use:-

Code:

EXEC msdb..sp_start_job 'Copy job name'

I use this solution to move backups to a remote location, just in case something happens locally (like a fire in the server room).

My SQLAgent is also part of replication so it needs network access for that, therefore it runs under a domain account. We called it SQLAgent and we only allow it to log on as a service.

Another option is ActiveX scripts but you'll still end up with the issue of service accounts and network access.

Kep.

KKaufman · May 23rd, 2006, 12:14 PM

Came across this and felt I needed to correct the last comment, you can backup across the network, and it is a common task of any DBA with more then 6 months experience. Permissions come in to play.

Make sure the user account in which SQL is running has permissions to write the backup to the remote folder youâre trying to access. If the sql engine is running under the system account, give folder permissions to the machine account in the domain.

This should work just fine.