Q. Why can’t I backup/restore my SQL Server databases to a share on another server?
R. 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 nobody 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 SYSTEM 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 SYSTEM 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 share name
you want to dump to – the share does not then authenticate who is coming in and
so a SYSTEM 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 or anything have access. This is
probably not something you want to do with production business data.
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic es\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’
e.g. (7., 2000) BACKUP DATABASE pubs to DISK=’\\server01\share\backupdir\backup.dmp’