Why can't I backup/restore my SQL Server database to a share on another server?

A. A. The reason is that the MSSQLSERVER service is running under a separate set of NT credentials - all services are related to an NT account. It doesn't matter who YOU are logged on as (after all SQL runs quite happily when no-one is logged on locally to the server doesn't it). Therefore your logon account and any mapped drives are irrelevant. It is SQL Server doing the backup, not you. This is the same for backups done via SQL Executive/SQL Agent - they just pass the TSQL to SQL Server to run, so it's still MSSQLSERVER doing the backup/restore.

For this reason the backup gui does not show you mapped drives or allow a UNC path to be typed in. You have to use raw TSQL commands to do the backup.

The default set of NT credentials used by MSSQLSERVER is the Localsystem account. You can check what userid that MSSQLSERVER is running under by looking at control panel/services highlighting MSSQLSERVER and choosing the start-up option.

The Localsystem account has no access to shares on the network as it isn't an authenticated network account. Therefore SQL Server running under this account cannot backup to a normal network share.

So, if you want to backup to a network share you have two choices :-

1. Change the account the MSSQLSERVER service runs under to a user account with the relevant network rights.

or

2. Amend the following registry value on the TARGET server and add the sharename you want to dump to - the share does not then authenticate who is coming in and so a Localsystem account will work. The server service on the target server must be re-started before the change takes effect. Note that this effectively removes ALL security on that share, so you're letting anyone/anything have access. Which is probably not something you want to do with production business data.

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters\NullSessionShares

Whichever method you use, you MUST also use a UNC name to reference the file required and not a drive letter.

e.g. (6.5) DUMP DATABASE pubs to DISK='\\server01\share\backupdir\backup.dmp'
(7.0) BACKUP DATABASE pubs to DISK='\\server01\share\backupdir\backup.dmp'

End of Article

I have the same problems:
1) I can't restore a backup on another server because came wiwith other characteristics, such as missing users, roles, etc.

2) Creating a Network device it says "Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'networkdevice_miadev2'. 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 or restore operation terminating abnormally."

I mapped drive from server and doesn't work, also put it path to server, either.

Please, any help will be welcome...

Francisco

Francisco Hurtado February 20, 2001

You can also do this run this command under the at command scheduler and the drive mapping will be there even when you log off when you create the backup device in enterprise manager you can specify this driver even though it will not show up when browsing for a location it will still work.

cmd /c net use k: \\computer\d$ password /user:domain\user

Bob O'Driscoll April 19, 2001

I was interested to see this problem already highlighted. I am currently having the same problem dumping a database onto another network server.
My SQL service account is running on a network account which has permissions on the remote server. I am able to create the dump device on the remote server but when I try to dump the datbase to that remote device I get the following error:
Cannot open backup device 'remotetest'. Device error or device off-line.
I have checked the device and it's definite ON-Line.

What do you suggest?

Regards

Frank

Frank Gomez November 05, 2001

I CAN NOT NOT MAPPED DRIVE IN ENTERPRISE MANAGER , BUT CAN SEE IN WINDOWS EXPLORER.

Intizar Hassan January 17, 2002

Your answer to this question is not very much clear but still it helped me out up to some extent. I am not clear when u say that a user logged on to SQL Server is not the part of network. I think he is very much part of the network so he/she should be able to take the backup of the database on the network also.
If you can make the things more clear for me then please mail me on my email account i.e suri_neeraj@rediffmail.com

Neeraj Suri February 25, 2002

We want to hear what you have to say! Fill out this form to post your comments with this article. We do, however, review comments before posting them, so it may take a day or so for your comments to appear. Your email is only used if our editors need to contact you. It is not used or stored for any other purpose, nor posted with your comments.

sivaram October 16, 2003

event viewer in windows 2000 server give this message :
(The MySql service failed to start due to the following error:
The system cannot find the file specified.)

shahryar November 04, 2003

have the same problems: 1) I can't restore a backup on another server because came with other characteristics, such as missing users, roles, etc.

2) Creating a Network device it says "Server: Msg 3201, Level 16, State 1, Line 1 Cannot open backup device 'networkdevice_miadev2'. 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 or restore operation terminating abnormally."

I mapped drive from server and doesn't work, also put it path to server, either.

Please, any help will be welcome...

Mani November 12, 2003

When you say "Change the account the MSSQLSERVER service runs under to a user account with the relevant network rights.", what rights are required? I am using an account that has the ability to copy files to the share, however I still get the same error. Does the account also need any additional security profile options, i.e. "log on as a batch job", etc.?

Kelly May 25, 2004

I was having trouble restoring a backup from a shared drive. Your solution worked great! Thanks.

Kevin Smith June 25, 2004

See More Comments 1 2

You must be a registered user or online subscriber to comment on this article. Please log on before posting a comment. Are you a new visitor? Register now