dumping to a network drive

Posted on 1998-05-12
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.
Question by:sajalk

Accepted Solution

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.
Name: NullSessionShares
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.


Author Comment

ID: 1089576
'll try the solution proposed.
thanx much.

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

831 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