|
|
|
|
|
Why can't I backup/restore my
SQL Server database to a share on another server? The reason is that the MSSQLSERVER service is running under a separate set of NT credentials. It doesn't matter who YOU are logged on as (after all SQL runs quite happily when no-one is logged on to the console 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. 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 security on that share, so you need to be careful about what is in the share. 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' |
All content on this site, except where noted, represents an original work of Michael R. Hotek and is protected by applicable copyright laws. The SQL Server FAQ is the sole work of Neil Pike. No page, portion of a page, or download may be used for commercial purposes in whole or in part without the express, written permission of the applicable author.