Before you learn to back up your database, you need to become familiar with some terms that are used with SQL Server 2000 backups and restorations. Next, you will learn about the types of backup media on which you can store your backups, including how to choose among them. You will then learn how to create reusable backup devices.
You should become familiar with a variety of terms that are important to understand when performing database backups and restorations. Table 9.1 explains the most important terms.
For example, if two backup devices (such as tape drives) are used to record a backup of a database and the backup set uses three tapes per backup device, there are six tapes in the media set (consisting of three tapes in each media family). A media set can contain multiple backup sets, for example, the appending of one backup set to another backup set on the same tape or set of tapes.
You can select to use either disk or tape for your backup media. The SQL Server 2000 backup program supports local tape drives, local disk drives, network disk drives, and named pipes. Named pipes provide an interface for use by third-party backup solutions.
Table 9.1 Backup Terminology
A full or partial copy of a database, transaction log, file, or filegroup forming a backup set. The backup set is recorded on backup media (either tape or disk) using a backup device (a tape drive name or physical filename).
The physical file (such as C:\SQLBackups\Full.bak) or specific tape drive (such as \\.\Tape0) that you use to record a backup onto backup media.
A file that stores a backup set.
The actual physical media (either disk or tape) used to store a backup set using a backup file. Backup media can store multiple backup sets (such as from multiple SQL Server 2000 backups and from Windows 2000 backups).
The backup from a single backup operation that resides on backup media. The backup set may reside on a single backup media, a media family, or a media set.
All media (physical files or tapes) in a media set written by a single backup device for a single backup set.
Provides information about the contents of the backup media. A media header must be written before a backup set can be recorded on the backup media (this is also called initializing the backup media). Usually, the media header is written one time and remains on the media for the life of the media.
All media involved in a backup operation. Examples of media sets are: a single tape, a single disk file, one backup device writing a set of tapes, or a set of tapes written by more than one backup device.
Traditionally, administrators have used tape for database backups because it was cheaper than hard disk space. However, tape drives are relatively slow and have limited capacity. The limitation for SQL Server 2000 backup speed is usually the tape drive itself. You can improve tape backup performance by writing to two tape drives simultaneously. This will effectively cut your backup time in half because the backup is written in parallel to the tape drives. The problem with limited tape capacities for large databases is that if your backup will not fit on a single tape; someone must be there to switch tapes (and insert the correct tape). If not, the backup never completes. Simultaneously writing to multiple tapes helps solve the problem of limited capacity per tape for large databases.
In the past, disk space was too expensive to use for database backups. However, this is no longer the case. Backup to a local disk is frequently the backup method of choice because it is generally the fastest method. Backup times as fast as eight minutes for a 20-GB database have been reported. If you do back up your data to a local disk, be sure to use a separate physical disk from your data or transaction log files. After being backed up to a local disk, backup files are generally themselves automatically backed up regularly (for example, nightly) to tape to be archived.
For smaller databases, performing a backup to a network drive is also a common scenario. For additional performance in this scenario, administrators sometimes segment the network to minimize or eliminate network contention. You may use the network drive for multiple databases and by multiple SQL Server 2000 installations. This network drive will generally be regularly (and automatically) backed up to tape for archiving. This allows the archiving of backup files to tape from multiple SQL Server 2000 instances to be consolidated to one network location.
You can create one or more permanent backup devices that you can use for regular backups, or you can create a new backup file each time you perform a database backup. Generally, you will want to create backup devices that you can reuse, particularly for automation of database backups. Having permanent backup devices allows you to refer to them in backup and restore commands using only a logical name, rather than the complete physical name. Backup devices are recorded in the sysdevices table in the master database. Backup files created on the fly are not recorded in the sysdevices table and thus are not reusable, but rather must be specified each time they are referred to.
To create a backup device using SQL Server Enterprise Manager, expand the Management container, right-click Backup, and then click New Backup Device. In the Backup Device Properties - New Device dialog box, specify a logical name for the backup device and define a tape drive name or a filename for the backup device. See Figure 9.1.
To delete a backup device using SQL Server Enterprise Manager, click the Backup container in the console tree (in the Management container) to display a list of all backup devices in the details pane. Right-click the backup device and then click Delete to drop the device.
Creating a backup device using SQL Server Enterprise Manager.
To create a reusable backup device using Transact-SQL, use the sp_addumpdevice system stored procedure.
Sp_addumpdevice 'disk' , 'FullBackupDevice' , 'E:\SQLBackups\Full.bak'
The preceding example creates a disk backup device with a logical name of FullBackupDevice using a file on the local disk.
Sp_addumpdevice 'tape' , 'TLogTapeBackupDevice' , '\\.\Tape0'
This example creates a tape backup device with a logical name of TLogTapeBackupDevice using tape drive 0 (this refers to the first tape drive on the system).
Sp_addumpdevice 'disk','TLogBackupDevice' , '\\NetSrv\SQLBak\TLog.bak'
The preceding example creates a disk backup device with a logical name of TLogBackupDevice using a network file referenced using a Universal Naming Convention (UNC) path.
With Transact-SQL, you can create and save a script that creates all of your backup devices at one time. Saving the script is important because you can use it to re-create the backup devices in the master database if you need to (or duplicate this backup device structure on other SQL Server 2000 computers in your enterprise).
To use Transact-SQL to view a list of all devices on your SQL Server instance, use the sp_helpdevice system stored procedure. To drop a backup device using Transact-SQL, use the sp_dropdevice system stored procedure.
The preceding example drops the FullBackupDevice backup device, but does not drop the associated physical file.
Sp_dropdevice 'FullBackupDevice' , 'DELFILE'
The preceding example drops the FullBackupDevice backup device and also drops the associated physical file.
In this practice you use a Transact-SQL script to create multiple backup devices using the sp_addumpdevice system stored procedure.
To create backup devices using Transact-SQL
The Open Query File dialog box appears.
A Transact-SQL script appears that will create five backup devices using the following logical names: MasterFullBackup, MSDBFullBackup, SSEMDBFullBackup, SSEMDBDiffBackup, and SSEMDBTLogBackup. The specified physical path does not yet exist. You will create this folder in just a few moments.
Notice that the script added five disk devices. SQL Server 2000 does not verify the physical path for a backup device until you are ready to use the backup device.
Notice that the five disk devices appear, along with information regarding the master, model, and tempdb databases.
Learning backup terminology is important before you start working with backups, backup sets, and media sets. You need to decide which backup media you will use for your backups, tape or disk. Frequently, administrators make backups to disk for performance and then archive them to tape. Once you have determined your backup media, you need to create permanent backup devices that you will use for your backups. Creating reusable backup devices is useful for automating backups, and for referring to backup files in Transact-SQL scripts.