Microsoft SQL Server 7.0 System Administration Training Kit
An important part of an administrator's strategy for backup and recovery of data is the type of device that will hold the backed-up data—the backup device. A backup device is used by Microsoft SQL Server to back up databases, transaction logs, and data files. Backup devices include disk, tape, and named pipe devices.
After this lesson, you will be able to
- Create named backup devices
- Describe temporary backup devices
- Describe striped backups
Estimated lesson time: 45 minutes
Overview of Backup Devices
Backup devices always have a physical name that refers to the name used by the operating system to access the device. They can also have a logical name stored in the system tables. Devices that have a logical name are called logical, permanent, or named backup devices. Devices that do not have a logical name are called physical or temporary backup devices. There is no difference in the devices themselves, only in whether you can use a logical name to refer to the device. When backing up or restoring data, you can use physical or logical names.
NOTE
The term backup file is sometimes used instead of backup device. Backup device is preferred because backups can be made to tapes and named pipes as well as to disk files. When you see the term backup file, it will usually be referring specifically to a disk backup device, which is stored as a file in the file system of the SQL Server computer or on a shared network drive.
Creating Named Backup Devices
If you want to use a logical name for a backup device, you must create the named backup device before using it. When using the device for backups and restores, you then reference the device by its logical name only.
Why Create Named Backup Devices?
Named backup devices are simpler to use than physical devices because they have shorter names. SQL Server Enterprise Manager lists only named backup devices in the console tree. (You can use physical names for backup devices elsewhere in SQL Server Enterprise Manager when necessary.) You can create named backup devices with SQL Server Enterprise Manager or by executing the sp_addumpdevice system stored procedure.
Using the sp_addumpdevice System Stored Procedure
Execute the sp_addumpdevice system stored procedure to create a named backup device on disk or tape, or to direct data to a named pipe. When you create named backup devices, consider the following facts:
- The device is not physically created until it is used. For example, if you look in the C:\Mssql7\Backup folder after creating a new device with a physical name of C:\Mssql7\Backup\Mydev.bak, you will not see a file called Mydev.bak. Only after you make the first backup to the new device will Mydev.bak be present in the folder.
- SQL Server creates logical and physical names in the sysdevices system table of the master database.
- You must specify the logical and physical names of the backup device.
- If you want to create a backup device that uses a network disk file, you must specify the network disk file location. The location can use a UNC path name or a mapped drive letter.
When you create a backup device with SQL Server Enterprise Manager, SQL Server executes the sp_addumpdevice system stored procedure for you.
The syntax for the sp_addumpdevice statement is as follows:
| sp_addumpdevice [@devtype = ] 'device_type', [@logicalname = ] 'logical_name', [@physicalname = ] 'physical_name' |
where device_type is {DISK | TAPE | PIPE}.
The following example creates a backup device on a local disk using the sp_addumpdevice statement.
| USE master EXEC sp_addumpdevice 'disk', 'mybackupfile', 'C:\Mssql7\Backup\Mybackupfile.bak' |
The following example uses the sp_addumpdevice statement to create a backup device on a network disk.
| USE master EXEC sp_addumpdevice 'disk', 'mynetworkbackup', '\\servername\sharename\path\mynetworkbackup.bak' |
The next example creates a named backup device on a tape with the logical name Mytape1 and the physical name \\.\tape0, using the sp_addumpdevice statement.
| USE master EXEC sp_addumpdevice 'tape', 'mytape1', '\\.\tape0' |
Exercise: Creating Backup Devices with SQL Server Enterprise Manager
In this exercise, you will use SQL Server Enterprise Manager to create two named backup devices.
- To create backup devices with SQL Server Enterprise Manager
- Log on to your computer as Administrator or another account that is a member of the Administrators local group.
- Start SQL Server Enterprise Manager.
- In the console tree, expand Management, right-click Backup, and then click New Backup Device.
- Use the information in the following table to create two new named backup devices.
- Close SQL Server Enterprise Manager.
| Name | Filename |
|---|---|
| NwA | C:\Mssql7\Backup\Nwa.bak |
| Nwlog | C:\Mssql7\Backup\Nwlog.bak |
Exercise: Creating Backup Devices with Transact-SQL
In this exercise, you will create two new named backup devices with the sp_addumpdevice system stored procedure. You will find the script for this exercise in C:\Sqladmin\Exercise\Ch09\ Makedev2.sql.
- To create a permanent backup device with Transact-SQL
- Open SQL Server Query Analyzer and log on to the (local) server with Microsoft Windows NT authentication. Your Administrator account is a member of the Windows NT Administrators group, which is automatically mapped to the SQL Server sysadmin role.
- Write and execute a Transact-SQL statement to create two named backup devices based on the information in the following table.
| Device type | Logical name | Physical name |
|---|---|---|
| Disk | NwstripeA | C:\Mssql7\Backup\NwstripeA.bak |
| Disk | NwstripeB | C:\Mssql7\Backup\NwstripeB.bak |
Deleting Named Backup Devices
You can delete named backup devices using SQL Server Enterprise Manager or the sp_dropdevice system stored procedure. If you use SQL Server Enterprise Manager to delete a named backup device and the device is a disk, the backup device's file will not be removed from the disk. You must remove the file manually from C:\Mssql7\Backup (or the location it was created in, if not the default location). If you use sp_dropdevice to delete a named backup device and the device is a disk, you can specify the DELFILE option to have sp_dropdevice delete the file, as conveyed in the following example.
| USE master EXEC sp_dropdevice 'mybackupfile', DELFILE |
Creating Temporary Backup Devices
While it is preferable to create a named backup device and use a logical name to reference it, you can also reference a backup device with its physical name only.
Why Create Temporary Backup Devices?
If you do not plan to reuse a backup device, you can use a temporary backup device. This makes it unnecessary to use the sp_addumpdevice system stored procedure to add the device before using it. For example, if you are performing a one-time-only backup of a database or are testing the backup operation that you plan to automate, you may want to use a temporary backup device.
Creating a Named Backup Device from a Temporary Backup Device
When specifying the location for a named backup device in SQL Server Enterprise Manager or as the physical name parameter of the sp_addumpdevice system stored procedure, you can specify an existing temporary backup device location. The temporary backup device then becomes a named backup device that can be referenced by its logical name and managed in SQL Server Enterprise Manager. If you try to add a device that is already a named backup device, the operation will fail.
Initializing a Device
SQL Server initializes a backup device the first time it is used. For named backup devices, the device is initialized with the physical name that was specified when the device was created. For temporary backup devices, the device is initialized with the physical name specified in the BACKUP statement.
If you create a temporary backup device, you must
- Specify a media type (DISK, TAPE, or PIPE).
- Specify the complete path and filename for disk and tape devices. If you want to use a network disk file, you must specify a network disk file location, using a UNC pathname or a mapped drive letter.
- Specify the name of the pipe for named pipe devices.
The partial syntax for the BACKUP DATABASE statement is as follows:
| BACKUP DATABASE database_name TO { backup_device_name| {DISK | TAPE | PIPE} ='temp_backup_file' [, …n] |
The following example uses a named backup device to back up the Northwind database.
| USE master BACKUP DATABASE northwind TO mybackupfile |
The example that follows uses a temporary backup device on a disk to back up the Northwind database.
| USE master BACKUP DATABASE northwind TO DISK = 'C:\Temp\Mynwind.bak' |
Using Multiple Backup Devices to Store Backups
SQL Server can write to multiple backup devices at the same time (in parallel). When you have multiple backup devices, data is striped across all devices that are used to create the backup. These devices store a striped backup set. A backup set is a result of a single backup operation on one or more devices. Figure 9.1 shows how backups can be made to multiple backup devices and how the parts of a single backup across the devices together make up a backup set.
Figure 9.1 Using multiple devices to store backups
Storing Backups on Multiple Backup Devices
Backing up to multiple devices decreases the total time required to back up a database. For example, if a backup operation that uses one tape drive normally takes four hours to complete, you can add a second tape drive and possibly reduce the length of the backup operation to only two hours.
When you use multiple devices to store your backups, consider the following facts:
- All devices that are used in a multiple backup operation must be of the same media type (disk or tape).
- Devices that are used in a multiple backup operation do not need to be the same size or operate at the same speed.
- You can use a combination of named and physical devices together.
- When restoring from a multiple-device backup, it is not necessary to use the same number of devices as was used to create the backup.
Media Sets
A media set is a collection of backup devices used to contain one or more backup sets. A media set can be a single backup device. If the backup devices in a multidevice media set are disk drives, then each backup device is a single file. If the backup devices in a multidevice media set are tape drives, then each backup device is made up of one or more tapes, together called a media family. The first tape in a media family is called the initial media; other tapes are called continuation media.
When using media sets,
- Tapes used as part of a media set for a multiple-device backup can be used only by SQL Server.
- If you define a number of backup devices as members of a media set, you must always use the backup devices together.
- You cannot use only one member of the media set for a backup operation unless you reformat the backup device.
- If you reformat one member of a media set, the data contained in the other members of the media set is invalid and unusable.
If a striped backup set was created on two backup devices, those two backup devices are now part of a media set. All subsequent backup operations that involve this media set must use these same two backup devices. You can append additional striped backup sets to this media set by using these two backup devices. However, if you want to use one of these backup devices on its own to back up another database or as part of another media set, you must reformat the backup device.
Lesson Summary
One of the first steps in planning a successful backup strategy is deciding on the medium. Will the backup be to disk, tape, or named pipe devices? Once the type of device is selected, the backup process can be made more efficient by using striped devices.
SQL Server can write to multiple backup devices at the same time (in parallel). When you have multiple backup devices, data is striped across all devices that are used to create the backup. These devices store a striped backup set. A backup set is a result of a single backup operation on one or more devices.