SQL Server Database Backup

Windows IT Pro

- Advertise

SQL Server Database Backup
View the book table of contents

Author:	Kevin Cox William Jones
Published:	July 1997
Copyright:	1997
Publisher:	29th Street Press

Abstract
This chapter examines backup devices, the kinds of database dumps (database, log, and table), the commands and menus you use to back up a database, database options that affect backups, and the questions to ask and issues to consider when you're developing a coherent backup strategy.

BACKUP DEVICES

SQL Server backs up databases by dumping data (from a database, a transaction log, or a table) into backup devices it creates and maintains. A backup device can be a disk file, a tape drive, or even a null device.

Special Note: A null device is an output device with no output location, also known as the bit bucket, the vast unknown, or the recycle bin.

You can create a backup device in two ways: through the New Backup Device dialog box ( Figure 12.1) or with a Transact-SQL command. You access the Enterprise Manager’s New Backup Device dialog box by clicking the Backup Devices item on the Server Manager or through the Backup/Restore menu.

The following Transact-SQL command also creates backup devices.

sp_addumpdevice {'disk' | 'diskette' | 'tape'},
   'logical_name', 'physical_name' [, {{cntrltype [, noskip |
   skip [, media_capacity]]} | {@devstatus = {noskip |
   skip}}}]

The logical_name parameter is the name of the backup device (also called dump device) used in the Dump statements. Physical_name is the location of the file to create or the name of the tape device to address. The parameters cntrltype and media_capacity are included for backward compatibility. The noskip|skip option tells SQL Server whether to recognize ANSI tape labels that mark expiration dates and permissions for the tape.

Using either the Enterprise Manager or the Transact-SQL command, you can specify what kind of device to create. Notice that you can create a backup device on a diskette with the Transact-SQL command; however, this option is not available through the Enterprise Manager.

You create a Null dump device by adding a disk dump device and assigning it the physical name NUL. When SQL Server is installed it has a default Null dump device named Diskdump. If you dump to this or any other Null device, you won’t be able to recover the data later. You should use Null dumps only to mark a database or transaction log as having been dumped. For example, to empty the transaction log, you can dump the transaction log to a Null device. However, if you want to recover a database, you should always follow a dump to the Null device by a database dump, because you can’t dump the transaction log again if the last dump was made to a Null device without first dumping the database.

Before you can dump data to a device the first time, you must initialize the device. After you dump to a device, you can choose whether to initialize the device again. If you initialize a backup device after a dump is made to it, the current contents are erased. If you do not initialize a device that has been used for a dump, any new dump will be appended to the previous contents.

Information about backup devices is kept in the sysdevices system table, along with the database device definitions. The backup devices have a cntrltype (or device type) of 2, 3, 4, or 5 representing disk, diskette A, diskette B, or tape devices, respectively. When a backup device is created as a disk file, SQL Server stores the name of the file to use but does not create or open the file until the device is initialized. Be sure to remember that the file has not been created when you’re attempting to do a data dump, because lack of disk space will cause a backup to fail. However, dump files are usually smaller than the database because only those pages that have been allocated are actually backed up.

You can remove backup devices by right-clicking the backup device in the Server Manager and choosing Delete from the menu or by clicking Delete on the Backup/Restore dialog box discussed later in this chapter. They can also be removed using the system procedure

sp_dropdevice logical_name

where logical_name is the device name.

TYPES OF DUMPS

SQL Server creates dumps from which data may be recovered. These dumps come in three forms: database dumps, transaction dumps, and table dumps.

When the whole database is dumped, the current structure, which is contained in the system tables within each database, is saved with the data. Because the structure is saved, the recovery process can be performed starting from a blank database, without the separate step of creating the database structure. Sometimes database dumps are impractical. Databases can be very large, and dumping the complete structure and all the data regularly can become time-consuming and unwieldy.

To avoid complete database dumps, you can also dump only the transaction log, which dumps the list of changes to the database since the last database dump or transaction log dump. Besides being a shorter process than dumping the entire database, transaction dumps have the added advantage of helping you keep the transaction log a manageable size. After a transaction dump, the inactive portions of the log are truncated, or deleted. Inactive portions of the log are completed transactions that have been written to the database at specified checkpoints.

The first two types of dumps are often used together to restore failed databases. In general, recovering a database has two steps. The first step is to restore the structure and initial data from the last database dump. Because this dump might be several hours old, you then need to use the transaction dumps to restore the transactions to the database up to the point of failure.

The third type of dump is for table data only. A table dump stores the current data, but not the structure, of a single table. Table dumps can be useful when one table needs to be kept more current than the rest of the database. For example, if you are managing a sales system, you might want to dump the order table every 15 minutes and dump the rest of the database every two hours. In this way, sales information could be restored up to the last 15 minutes by recovering the database from the last dump, loading the transaction dumps up to the most recent one, and then loading the sales table dumps. The orders could then be reprocessed for shipping or material requirements.

Dumping tables is a new feature to SQL Server 6.5, and you should exercise caution when you use this option. Before dumping a table, be sure to analyze all the dependencies of the table. You must ensure that you will not create stranded records in other tables or references within the recovered table to records that no longer exist when you recover the table. It is very important to ensure that referential integrity can be maintained if individual tables are dumped.

You can dump directly to tape or to disk. Direct tape dumps are often not necessary and, in fact, are often not as useful as disk dumps. Tape dumps usually take longer to perform, and recovery time is longer because tape media devices have slower read and write times. For backing up both databases and transaction logs, we recommend performing a disk dump followed by an external tape backup. This strategy creates the minimum effect on the database and allows very fast recoveries in cases where the failure is not caused by hardware. Backing up the disk dump to tape immediately after the dump makes it possible to recover from hardware errors as well. Disk dumps can be impractical if the database is very large. You need to use different strategies when dealing with very large databases (VLDBs).

When dumping and recovering VLDBs, the optimum strategy is to create a multiple device backup with several tape drives running in parallel on multiple threads. Configuring threads is discussed in Chapter 16, “Performance Tuning,” where we cover SQL Server configuration options. Using multiple devices for stripe or parallel-output backups is discussed below.

PERFORMING DUMPS

SQL Server provides two ways of controlling dumps — through the Enterprise Manager and through a Transact-SQL command. Because the Transact-SQL command gives you more options, we will discuss it first.

TransactSQL Statement
You can dump databases and transaction logs with the following Transact-SQL commands:

DUMP DATABASE {dbname | @dbname_var}
      TO dump_device [, dump_device2 [..., dump_device32]]
   [WITH options [[,] STATS [ = percentage]]]

DUMP TRANSACTION {dbname | @dbname_var}
      [TO dump_device [, dump_device2 [..., dump_device32]]]
   [WITH {TRUNCATE_ONLY | NO_LOG | NO_TRUNCATE} {options}]

Both dump commands let you specify multiple dump devices. When you specify more than one dump device, the dump is striped across the devices, which decreases both dump time and recovery time. Stripe backups have benefits and drawbacks. They can be useful when you are dumping to slow devices. In addition, they are the only feasible option for dumping large databases.

However, stripe backups increase the number of variables that you need to keep track of, and they require more equipment and administration. If you lose one tape of a stripe set, you will not be able to recover from that backup, so you must control your environment very carefully to ensure that you keep a recoverable backup.

You can stripe backups across different types of backup devices, which may be a useful feature if security is an issue, because striping data across a combination of disks and tapes limits recovery to only those machines that have access to those specific disk devices. However, striping across different types of devices prohibits using a set of tapes to build a copy of the database. Relying on disk storage alone for backup is generally a poor idea, because restoring damaged disks and computers is usually the primary motivation for performing backups. If your strategy incorporates the recommended dump to disk followed by a tape backup, you should be aware of the exposure that exists during the time between the disk dump and the tape backup. Making this time span as short as possible helps to minimize the chances of failure occurring while no feasible backup exists.

As you can see from the Transact-SQL statement, you can use one of three options to dump transaction logs: Truncate_Only, No_Log, and No_Truncate. However, for most transaction dumps in production environments, you don’t need to specify an option.

The Truncate_Only option truncates the inactive part of the log without backing it up and logs the transaction dump in the active portion of the log. This option is used to clean the log file of databases that are created on a single device instead of having the data and log on separate devices.

In cases where the log has become full and no logged events can take place until log space is freed up, you can specify the No_Log option. This option truncates the inactive portion of the log without logging the truncation. This dump does not create a backup, either, so neither of these options is useful for creating backups. They should only be used in special circumstances, when you want to clear the log. If the data is important, you should immediately follow one of these dumps with a full database dump.

The third option is No_Truncate. This option was created as a solution for a special problem — the database and log are on different devices, and the data device is lost but the log device is not. In this situation, you have good backups up to a point, and the log file contains the data changed since the last backup, but you cannot get to the log file because the data portion is missing. This condition is known as an orphaned log file and it caused a lot of unhappiness in earlier versions of SQL Server. You can now use the No_Truncate option to dump the transaction log before you drop and re-create the database and restore all of your backups in order. In effect, this option tells SQL Server, “I know the database is broken, but give me this important data before I fix it.”

The other parameters in the Dump command specify whether tapes are to be ejected after dumps, whether the dump devices are to be initialized before dumps, and whether to record a creation date and the number of days the tape is to be retained.

As part of the Dump command, you can also create a temporary device to contain the dump by using the following parameter in place of a device name in the To parameter:

{DISK | TAPE | FLOPPY | PIPE} = {'temp_dump_device' |
@temp_dump_device_var}}

These temporary devices are not recorded in the sysdevices system table, as permanent devices are, and exist as devices within SQL Server only for the duration of the dump that specifies them. You can use dumps to temporary devices to recover data in the same way you can use dumps to permanent devices. However, dumps to temporary devices are used typically to support external backup agents using a Pipe device type. (A pipe is a mechanism that communicates across processes. External agents use named pipes and pipe dump devices to perform dumps out of SQL Server into an externally managed backup device.)

Enterprise Manager
You can also use the Enterprise Manager’s Backup/Restore dialog box to perform dumps. You activate the Database Backup/Restore dialog box, shown in Figure 12.2, by right-clicking the database in the Server Manager and choosing Backup/Restore or by going to the Tools menu and choosing Database Backup/Restore. Notice that the default Null device diskdump has a trash can as its icon in the Backup Devices list box.

Tip: You can also truncate the log with the No_Log option from the Edit Database menu.

The Enterprise Manager’s Backup/Restore interface does not give you all the functions you have with the Transact-SQL statements. You cannot create a temporary backup device, and you can dump the transaction log only with the default truncate option.

The Database Backup/Restore dialog box gives you easy access to the most common backup settings. You can choose a database, transaction log, or a table to back up. You can set options for initialization, tape ejects, and header information and you can specify one or more backup devices to receive the dump, thereby striping the backup set. By clicking the New button in the Backup Devices window, you can create new devices. Clicking Backup Now or Schedule brings up the Backup Volume Label dialog box ( Figure 12.3). Here you can specify a volume label; however, volume labels are not required. In most cases, you gain little benefit from assigning a volume ID, although they can help ensure that a given tape actually contains the backup you think you are restoring.

Page: 1, 2

ADS BY GOOGLE