After you install a SQL Server, you will need to create the dump devices necessary to back up the system. When you create a dump device, you provide both a logical and a physical name. The logical name must follow the SQL Server rules for identifiers and can be up to 30 characters. The physical name is an operating-system filename that includes the full path and follows the rules for operating-system filenames, or the universal naming convention (UNC) for network devices.
Following are examples of logical and physical names for dump devices.
| Type of dump device |
Example of a logical name | Example of a physical name |
|---|---|---|
| Disk | TLOGDUMP | C:\SQL60\DATA\TLOG01.DAT |
| Disk (network) | DATADUMP | \\TERRIER\BACK\DUMP11.DAT |
| Tape | TAPEDUMP | \\.\TAPE0 |
| Diskette | DISKETTEDUMPA | A:\SQLTABLE.DAT |
| Named pipe | PIPEDUMP | \\.\PIPE\SQL\DUMP |
| Null | DISKDUMP | NUL |
Following is information about each type of dump device.
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). You can still run SQLMail in this case. For more information see Chapter 3, Configuring Servers, and Chapter 18, Setting Up Mail.
SQL Server supports local tape dump devices (on tape drives attached to the local computer). Network tape dump devices are not supported.
To create a tape dump device, you must first install a tape drive using Windows NT. For information about Windows NT - supported tape drives, refer to the hardware compatibility list for Windows NT. For information about installing a tape drive, see the drive manufacturer's documentation and the documentation for the Windows NT Setup program.
When creating a tape dump device for SQL Server, you must use the physical name that has been assigned to the tape drive by Windows NT. For example, Windows NT will give this physical name to the first tape drive installed on a computer:
\\.\TAPE0
Additional tape devices on a computer will have a similar name, with the number incrementing for each (\\.\TAPE1, \\.\TAPE2, and so on). If more than one tape drive is installed on a computer, you can determine the number of a particular tape drive by using the Windows NT Backup utility from the Administrative Tools program group.
When dumping to a tape dump device, use only the recommended tapes for the specific tape drive, as documented by the drive manufacturer. If you are using DAT drives, use computer-grade (Digital Data Storage-DDS) DAT tapes. If you do not use a computer grade tape, SQL Server backups will be restricted to a single tape; a database dump that spans multiple tapes will not be allowed.
Two diskette (floppy disk) dump devices are created when SQL Server is installed: DISKETTEDUMPA and DISKETTEDUMPB.
Diskette dump devices are supported in order to provide compatibility with earlier releases. SQL Enterprise Manager cannot be used to initiate a diskette dump, because the console command-line utility must be used to prompt the user and then wait for responses. For information about creating and using diskette disk dump devices, see the Microsoft SQL Server Transact-SQL Reference.
Named pipe dump devices allow third-party vendors a flexible and powerful way to connect their own software and provide specialized backup and restoration capabilities.
Named pipe dump devices are not created or administered by using SQL Enterprise Manager or the sp_addumpdevice stored procedure. To use a named pipe dump device, you providežas part of a DUMP or LOAD statementžthe name of the named pipe that will be used by a client application.
For more information about using named pipe dump devices, see the information about the LOAD and DUMP statements provided in the Microsoft SQL Server Transact-SQL Reference.
A special device, named DISKDUMP, is created when SQL Server is installed. Backing up to DISKDUMP sends the data to the NULL device. This dumps data without saving it. You can use the DISKDUMP device name as a bit bucket in conjunction with database or transaction log backups.
Important You cannot retrieve dumps sent to the NULL (DISKDUMP) device.