Discussion Locked

SQL 7 will not backup to network drives

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
At some point I changed the server configuration properties and now when trying to backup to a UNC path or network drive it gives the error - device error or device off line. I have no problems accessing these paths through explorer.

5 replies

by talli

Your problem is with the insufficient privileges your SQL server account has on that share u attempt to dump to.Even if u r logged in as an admin in that domain-it doesn't matter, since ,as u know ,MSSQLSERVER runs under it's own account, and this account is the one performingthe backup.So even if U can access those shares-the server can't. By default, MSSQLSERVER runs under LocalSstem account.(U can check what userid it's running by looking at Control Panel>Services, highlighting MSSQLSERVER and choosing the start-up option).
By it's nature, the LocalSystem account has no access to network shares and it isn't an authenticated network account.So to perform a successfull backup over the network u can either change the MSSQLSERVER account to a user account with a sufficient write privileges (I wouldn't use YOUR domain account forthat, I'd create a separate one), or u can amend the following Registry value to the target server and add the sharename u want to dump to. The effect of this will be that this share won't authenticate and check who's coming in and writing to it, so LocalSystem will work.U'll have to restart the server service on the target location for that change to take place (if necessary).But notethat by doing that u eliminate a pretty basic layer of security, so I'd take the first option. Here's the value:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters\Nu
llSessionShares
Whichever u choose-u must use the UNC name to reference thefile in question, notthe drive name, like:
DUMP DATABASE pubs to DISK='\\server01\share\backupdir\backup.dmp'
HTH
Good luck.
Talli

This discussion is closed to new replies.