Solved

dumping to a network drive

Posted on 1998-05-12
2
482 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
Comment Utility
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
Comment Utility
'll try the solution proposed.
thanx much.
sajal.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

763 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

9 Experts available now in Live!

Get 1:1 Help Now