[quoted text, click to view]

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'

Dear all,

I found SQL Server (either 2000 or 2005) Backup & Restore functions have a
great problem Windows 2003 :

They can't see shared network drives !

For example, a server machine has C:, D:, E: local drives and a F network
drive mapping to another machine. In Windows 2003 File Explorer, no problem,
we can see all the C, D, E, F drives, but inside the SQL Server Backup &
Restore functions, I can only see its local C, D, E drives but not the F
drive.

Have you also got such a problem ?