Please help: backup over network 
Author Message
 Please help: backup over network

Hello,
I had problems to create a backup device on a disk over
the network from the Enterprise Manager but was successful
to create one by sp_addumpdevice. The Enterprise Manager
canot locate the network files from the browse dialog box.

Backup by the syntax " backup database northwind to disk1
with format, name='full backup of northwind', stats"
There was an error meesage shown :
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'disk1'. Device error or device
off-line. See the SQL Server error log for more details.
Can anyone help, please?

Thank you.

Joe



Fri, 19 Aug 2005 17:40:41 GMT
 Please help: backup over network

Make sure that the SQL Server service account has permissions on the share.

--
Tibor Karaszi, SQL Server MVP
For help on TSQL, please provide code we can execute in Query Analyzer

Quote:

> Hello,
> I had problems to create a backup device on a disk over
> the network from the Enterprise Manager but was successful
> to create one by sp_addumpdevice. The Enterprise Manager
> canot locate the network files from the browse dialog box.

> Backup by the syntax " backup database northwind to disk1
> with format, name='full backup of northwind', stats"
> There was an error meesage shown :
> Server: Msg 3201, Level 16, State 1, Line 1
> Cannot open backup device 'disk1'. Device error or device
> off-line. See the SQL Server error log for more details.
> Can anyone help, please?

> Thank you.

> Joe



Fri, 19 Aug 2005 18:34:32 GMT
 Please help: backup over network
Joe,

Q.  Why can't I backup/restore my SQL Server databases to a share on another
server?

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 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\Services\LanmanServer\Parameters\Nu
llSessionShares

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.0, 2000)  BACKUP DATABASE pubs to
DISK='\\server01\share\backupdir\backup.dmp'

===

v1.04 2000.01.25
Applies to SQL Server versions  : All
FAQ Categories                  : Database Administration, Connectivity
Related FAQ articles            : n/a
Related Microsoft Kb articles   : n/a
Other related information       : "Creating SQL Server Services User Accounts"
Authors                         : Neil Pike

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 Reply here - no email
 SQL FAQ (484 entries) see
 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq



Sat, 20 Aug 2005 10:08:54 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Please help: Network driver cannot open the network device

2. COMBO BOX, PLEASE HELP, PLEASE HELP, PLEASE HELP!

3. emergency please help me please please please

4. APPROACH QUERY-PLEASE PLEASE PLEASE PLEASE HELP

5. General network error - help please

6. General network error - Please help !!!

7. Default network library - PLEASE HELP

8. Help Please: Directing Output of Scheduled Task to A File on A Network Drive

9. Network Utility Please Help

10. Please help: Any VB and Novell network problems?

11. help please with network database !!

12. Database in Network - please Help


 
Powered by phpBB® Forum Software