Solved

dumping to a network drive

Posted on 1998-05-12
2
487 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

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 …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

696 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