Solved

dumping to a network drive

Posted on 1998-05-12
2
483 Views
Last Modified: 2008-04-04
how can one dump database to a network drive?

when i try
dump database qbtest to DISK = '\\atg251a1\share\sajal\qbtest.dat'

i get the following message
Msg 3201, Level 16, State 1
Can't open dump device '\\atg251a1\share\sajal\qbtest.dat', device error or device off line. Please consult the SQL Server error log for more details.

i understand why it is not dumping as the network drive is not being recognized by SQL Server as a dump device.

this is what i read somewhere which i do not understand as to how to implement:

If SQL Server is running as LocalSystem (which it will be if you haven't changed it), you'll need to modify the registry and add the share of the remote dump device to the NullSessionShares registry key.

i appreciate the help.
thanx.
sajal.
0
Comment
Question by:sajalk
2 Comments
 
LVL 9

Accepted Solution

by:
cymbolic earned 100 total points
ID: 1089575
About Disk Dump Devices
SQL Server supports both local and network disk dump devices. A local disk dump device is created on a disk drive attached to the local computer. A network disk dump device is created on a shared network directory.
When creating a network disk dump device, you can specify the physical name by using a UNC name (for example, \\TERRIER\PUBLIC\DATABACK.DAT).

Important  When creating a dump device on a remote network location, be sure that the account under which SQL Server runs has appropriate write capabilities on the remote machine. Ownership and permission problems can interfere with the use of disk dump devices on network shares. Make certain that appropriate file, directory, and share permissions are given to the account under which SQL Server runs.


If SQL Server is running under the LocalSystem account (which is the default configuration), you need to add the share of the remote dump device to the NullSessionShares Registry key.
\\HKEY_LOCAL_MACHINE
      \System
            \CurrentControlSet
                  \Services
                        \LanmanServer
                              \Parameters
Name: NullSessionShares
Type: REG_MULTI_SZ
Data: COMCFG
In order to access some network resources, you can assign a user account to the SQL Server service (MSSQLServer).

t is best to keep live data and the backups on separate computers or, better yet, in different buildings. If the databases and/or logs are dumped to physical disk devices, it is often optimal from a performance standpoint to dump them to local hard drives on the server, as opposed to drives on some other computer on the network. However, from that point they should be copied to tape or to another server, so that the data and backups are not on the same computer. For convenience, most people choose to have their backups close at hand so that they can quickly get to them if needed. If this is done, keep another copy somewhere offsite (such as a bank safety deposit box) so that in the event of damage to the building, the backup of the data will remain safe.

Disk devices can be defined on shared network resources. If the dump is to be performed over the network, you must supply the UNC (universal naming convention) name in the form \\servername\sharename\path\filename or use a locally redirected drive letter. Make sure that you have appropriate permissions on the destination device. To ensure proper permissions, SQL Server may need to be started under a domain account (not the local system). To define the domain account under which SQL Server should be started, use the Services application in the Windows NT Control Panel.

0
 

Author Comment

by:sajalk
ID: 1089576
'll try the solution proposed.
thanx much.
sajal.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now