Print - 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'

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

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

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

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

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

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.

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.)

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...

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.?

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

I found that the statement "Whichever method you use, you MUST also use a UNC name to reference the file required and not a drive letter" does not apply. My folder on an IBM Windows systems is called SharedToDell$. I added this name to the NullSessionShares field on the IBM. Onthe Dell, I mapped to the folder on the IBM as drive G. My program refers to G:\filename.ext. The program picks it up properly.

Your solutions is 100% accurate. Those who posted otherwise didn't read your explanation. I had this error becuase someone changed the user account password I have SQL Server runnin as. Thanks

I'm posting this as anonymous because the registration form is giving spurious "password and confirm password do not match" errors. I've made the mssqlserver "service" start under a specific user and created a network share that has all permissions for that user, and I've verified that the mssqlserver service *is* running under the new user, and the user has the network share mapped as a mapped network drive, but SQL Server still gives the exact same error. Are you sure there's not someother double secret config option that makes this work? PublicMailbox at benslade dot com (append 030516 to the subj to bypass spam filters)

I had this trouble too, and it helped for me to do the following: xp_cmdshell 'net use /USER: Anonymous User -July 19, 2005

I was also interested to see this problem already highlighted. I am having a problem backing up a database to a maped drive on another network server (domain controler-DC). My SQL service account is running on a network account which has permissions on the server, which is also a domain controler(DC). I can see shares and drives on the network but not within enterprise manager cant figure out why?

This problem can be solved if the target machine is a windows 2003 server. You can grant de source sql server "computer" rigths to the share. Click to add a user. then click the button "Object Types" and select "Computers". Now you can select the source sql server and grant "Change" permissions to the Share and in the same way you have to grant these rights to the folder (NTFS)

The suggestions in the OP were cryptic and ultimately didn't work. However, the response about using xp_cmdshell to do a "net use" of the drive worked excellent! I did have to figure out how to *enable* xp_cmdshell but that was pretty easy to find. Great idea and made short work of the problem, as nothing had to be done to the "target" machine at all...

I had the same issue and just identified a solution. I first checked the sql service account (windows login that runs the mssqlserver service). Before reading this article, I added this account to the destination server and granted it access to the share, to no avail. That would have solved the problem for most of the people that had this issue if they hadnt already figured it out....for the people that are aggravated because they still have the issue, try this.... exec xp_cmdshell 'dir \\server\share\' When i ran that, i receieved error: Logon failure: the specified account password has expired. Bingo. I wouldnt have known that unless i actually tried to log into the server as the service account. I had only created a mapped drive using the service account credentials to test it, and it did not prompt me to change the password. (the user i created on the destination to match the service account had "change password on next logon" checked. So it was still a valid user with access to the unc path, but i still had an issue.