Performing a Backup | Microsoft SQL Server 2000 Administrators Companion

3 4

You can perform backups by using Enterprise Manager, T-SQL commands, or the Create Database Backup Wizard. The Create Database Backup Wizard method is the easiest in many cases, but Enterprise Manager is also easy to use. On the other hand, T-SQL commands can be put into SQL scripts that can be replayed over and over again. You should use the method that best suits your needs.

The backup operations themselves can be directed to either a physical device or a logical device. A physical device is an item such as a tape drive or a disk drive. Physical devices are assigned names by the operating system, and you must use these names to access the devices. Because these preassigned names can be hard to remember, you might want to create an alias, or user-defined name, for a physical device. Such an alias is called a logical device. This logical device exists only within SQL Server and can be used only for SQL Server backups, so it is also referred to as a logical backup device. If you want to back up data to a logical device, you must create the device beforehand. Before we look at the various methods of performing a backup, let's look at how to create a logical backup device. We'll use a logical backup device for the examples in this section. (See your system administrator for details about adding physical devices to the system.)

Creating Logical Backup Devices

You can create logical backup devices by using either Enterprise Manager or T-SQL. We'll look at both techniques in this section. The use of multiple backup devices can improve performance. (Backup performance tips are given in the section "Improving Backups" later in this chapter.)

Creating Backup Devices by Using Enterprise Manager

To create a backup device by using Enterprise Manager, follow these steps:

In the left pane of Enterprise Manager, expand the SQL Server Group folder, expand a server folder, and then expand the Management folder.
Right-click Backup and choose New Backup Device from the shortcut menu to display the Backup Device Properties window, shown in Figure 32-2.

Figure 32-2. The Backup Device Properties window.
Simply type a descriptive name for the backup device in the Name text box. The File Name text box is filled automatically. To change the filename path, either type a new path in the File Name text box or click the Browse [...] button to open the Backup Device Location dialog box. In this example, the backup device name is backup_dev_1. If you are adding a tape device, click View Contents to view any backup sets that are currently on the tape device.

Once you have finished these steps, the device is ready for use. You'll learn how to use backup devices later in this section, when you learn how to create a backup. Notice that if you do not have any tape devices connected to your system, the Tape Drive Name option will be unavailable.

Creating Backup Devices by Using T-SQL

To create a backup device by using T-SQL, use the stored procedure sp_addumpdevice. The syntax of sp_addumpdevice is shown here:

 sp_addumpdevice device_type, logical_name, physical_name

The device_type parameter can be disk for a disk drive, tape for a tape drive, or pipe for connecting third-party software to the backup system. The logical_name parameter is the name you assign to the device; this name is used to reference the device in BACKUP and RESTORE statements. The physical_name parameter is the system-assigned name of the device or file.

For example, to create a logical device named Backup_dev_2 that is a disk file, use the following syntax:

 sp_addumpdevice 'disk', 'Backup_dev_2',  'C:\MSSQL2K\BACKUP\Backup_dev_2.BAK'

Creating a Remote Backup Device

In order to back up your database to a remote system, you must first create the backup device by using the system stored procedure sp_addumpdevice. You cannot create a backup device on a remote server by using Enterprise Manager. In order to specify a remote system, you must specify the entire Universal Naming Convention (UNC) name as the physical name, as shown in this example:

 sp_addumpdevice 'disk', 'netbackup1', '\\ptc4\c$\backup\netbackup1.bck'

Once you have created this backup device, you can back up data to it by using either Enterprise Manager or T-SQL commands.

NOTE
To back up data to a remote system, you must have installed SQL Server to run under an account other than "LocalSystem." The "LocalSystem" account does not have privileges to access remote systems, and the backup will fail.

Backing Up Data over Multiple Networks

You can also perform a backup over multiple network interface cards. By backing up data to multiple devices over several LAN segments, you can overcome network bandwidth problems that might limit performance. If you are backing up data to several computer systems, simply specify the system names. If you are backing up data to one system via two LAN segments, you can specify the IP address in the UNC address, as shown here:

 sp_addumpdevice 'disk', 'netbackup1', '\\100.100.100.1\c$\backup\netbackup1.bck' sp_addumpdevice 'disk', 'netbackup2', '\\100.100.200.1\c$\backup\netbackup2.bck'

Once you have created these backup devices, you can back up data to them by using either Enterprise Manager or T-SQL commands.

Backing Up Using Enterprise Manager

Once you create one or more backup devices, you're ready to perform a backup. We'll look at the Enterprise Manager method first. To avoid repetition, the transaction log and database backup methods are presented together where possible, with the specific options for each identified and the differences between them noted.

Performing the Backup

To perform a backup by using Enterprise Manager, follow these steps:

Invoke the SQL Server Backup utility by using one of the following techniques:
- Expand a server folder in the left pane of Enterprise Manager, and then expand the Management folder. Right-click Backup and choose Backup A Database from the shortcut menu.
- Expand a server folder in the left pane of Enterprise Manager, right-click Database, point to All Tasks in the shortcut menu, and then choose Backup Database.
- Expand a server folder in the left pane of Enterprise Manager, and then click the Databases folder. In the right pane, right-click a database, point to All Tasks in the shortcut menu, and then choose Backup Database.
  The SQL Server Backup dialog box appears, as shown in Figure 32-3.
  
  Figure 32-3. The General tab of the SQL Server Backup dialog box.
In the Database drop-down list in the top section of this dialog box, select the database that you want to back up. (If you used the third technique in step 1, the database name will already be selected.) The backup name is based on the database name and is automatically generated for you, although you can override the automatic name by typing a backup name in the Name text box. You can also type a backup description in the Description text box. The description might be important when you are trying to restore the database. For example, if you are creating this backup immediately before dropping a table, a note about that in the description of the backup will be valuable. If you are backing up prior to loading some new data, include that information in your description.
In the Backup area of this dialog box, you must specify the type of backup to perform. The options that are available will vary depending on which database you select. For example, by default the Northwind database has the Truncate Log On Checkpoint option set. With this option set, the Transaction Log and File And Filegroup options are not available to the backup program. The backup options are described here:
- Database - Complete Performs a complete database backup; this will back up all of the data in the database.
- Database - Differential Performs a differential database backup; this will back up all data that has changed since the last backup.
- Transaction Log Performs a transaction log backup; this will also truncate the log.
- File And Filegroup Backs up single filegroups or files; you can specify the filegroup or file to back up.
Only one of these backup types can be selected. To perform a complete database backup and a transaction log backup, you must run the backup program twice.
In the Destination area, you must specify whether the backup is to tape or to disk. You can add more logical or physical backup devices by clicking Add. The Select Backup Destination dialog box appears, as shown in Figure 32-4. In this dialog box, you can specify a filename or select the backup devices from the Backup Device drop-down list. Click OK to return to the General tab of the SQL Server Backup dialog box. In the example in Figure 32-3, two devices are listed in the Backup To list. To remove a device, select the device and click Remove. Click Contents to view the contents of a device.

Figure 32-4. The Select Backup Destination dialog box.

If a backup device has been used previously, the following information about the backup is available:
- Name The name that was chosen by the person who ran the backup
- Server The name of the server that the backup was performed on
- Database The name of the database that the backup was performed on
- Type The type of backup (Complete, Differential, Transaction Log, Filegroup, or File)
- Date The date and time that the backup was performed
- Expiration The expiration date that was specified for the backup
- Size The total size of the backup set
- Description The description of the backup
Remember, multiple backups can be (and frequently are) performed to the same backup device.
In the Overwrite area of the SQL Server Backup dialog box, you can choose to overwrite the medium (such as a tape or a disk) or append to it. A disk device is typically appended to. But if you are using tapes and you are alternating them, you need to remove the previous information. Although you could overwrite that information by clicking Overwrite Existing Media in this dialog box, you should instead get into the habit of erasing the information before you perform a backup. Taking this precaution helps ensure that you will not accidentally overwrite a tape or a disk device.
In the Schedule area, you can choose to schedule the backup for a later time. Scheduling backups can be especially useful for transaction log backups that must occur on a regular basis to prevent the transaction log from filling up. If you want to schedule the backup, select the Schedule check box, and then click the Browse button to display the Edit Schedule dialog box, shown in Figure 32-5.

Figure 32-5. The Edit Schedule dialog box.
Provide a name for the schedule in the Name text box. Naming the schedules allows you to create multiple schedules—a schedule for each of your backups, perhaps.
In the Schedule Type section, you can specify whether the backup will start automatically when SQL Server Agent starts, whether to delay the backup until the CPUs become available, and whether the backup will run once or will recur. If you choose to run the backup once, you use the On Date popup calendar to select the date the backup is performed and the At Time spin box to select the time.

To set the schedule for a recurring backup, click Recurring and then click Change. The Edit Recurring Job Schedule dialog box appears, as shown in Figure 32-6.

This dialog box gives you tremendous scheduling flexibility. Within the Daily, Weekly, and Monthly options, you can schedule the frequency and duration of the job.

Figure 32-6. The Edit Recurring Job Schedule dialog box.
Click OK to return to the Edit Schedule dialog box, click OK again to return to the SQL Server Backup dialog box, and then click the Options tab, shown in Figure 32-7. In this tab, you can specify whether the backup media should be verified on backup completion, and you can indicate whether and how the media should be labeled. The options on this tab are described here:
- Verify Backup Upon Completion Causes the backup media to be verified as readable. Only media integrity is verified; the process does not verify that the data was backed up.
- Eject Tape After Backup (tape devices only) Ejects the tape from the tape device after the backup is complete. This option is useful when multiple applications or users are accessing the tape devices. This option can save your tape from being overwritten by someone else.
- Remove Inactive Entries From Transaction Log (transaction log backups only) Truncates the transaction log after the backup.
- Check Media Set Name And Backup Set Expiration Specifies that the media be checked and not overwritten unless the expiration date has been reached.
- Backup Set Will Expire (tape devices only) Allows you to set the media expiration date.
- Initialize And Label Media (tape devices only) Allows you to specify a label for the media.
Figure 32-7. The Options tab of the SQL Server Backup dialog box.
When you finish setting the options, click OK to begin running the backup you configured.

Managing the Backup

To view, delete, or modify backup jobs that have been scheduled, follow these steps:

In the left pane of Enterprise Manager, expand a server folder, expand the Management folder, expand the SQL Server Agent folder, and click Jobs. The scheduled jobs are listed in the right pane of Enterprise Manager, as shown in Figure 32-8.

Figure 32-8. Jobs displayed in Enterprise Manager.
To delete a job, simply right-click the job name and choose Delete from the shortcut menu.
To view or modify a job, right-click the job name and choose Properties from the shortcut menu to display the job's Properties window. Make your modifications, click Apply, and then click OK.

Backing Up Using T-SQL Commands

Using T-SQL to back up a database can be a little more difficult than using Enterprise Manager at first, but if you're the type of DBA who prefers to automate operations in scripts, this technique should be more to your liking. The TSQL BACKUP command also offers a few more options than does the backup program in Enterprise Manager. In this section, we'll look at the syntax and the options of the BACKUP command. There are actually two T-SQL backup commands; which one you use depends on the type of backup you're performing. These commands are listed here:

BACKUP DATABASE Used for backing up either the entire database or a file or filegroup
BACKUP LOG Used for backing up the transaction log

Because these two commands provide most of the same options, we'll look at them together.

Performing the Backup

The syntax of the BACKUP statement for a full database backup is shown here:

 BACKUP DATABASE database_name TO backup_device [ WITH options ]

This statement requires only the name of the database and the name of the backup device. (Examples of BACKUP statements can be found in the sidebar "Using BACKUP" later in this chapter.) The syntax of the statement for a file or filegroup backup is shown here:

 BACKUP DATABASE database_name  file_name or filegroup_name [,…n]  TO backup_device [ WITH options ]

This statement requires only the name of the database, the filename or filegroup name, and the backup device name. Multiple filenames or filegroup names can be included, separated by commas.

The syntax of the statement for a transaction log backup is shown here:

 BACKUP LOG database_name { [ WITH { NO_LOG | TRUNCATE_ONLY )] } |  { TO backup_device  } [ WITH options ]

This statement requires a database name, and either the WITH NO_LOG or WITH TRUNCATE_ONLY option or a backup device name. You can then add any options you want. The NO_LOG and TRUNCATE ONLY options are synonyms, and both truncate the log without making a backup copy of it.

CAUTION
If you use either of these options in your BACKUP LOG statement, in the event of a failure, you will not be able to recover the database to the state it was in at the point of failure because no log records will be saved. These options are not recommended; use them at your own risk.

In all three of these backup commands, database_name is the name of the database that the backup will be performed on. The backup_device parameter is either a logical backup device name or the name of a physical device. If a physical device is specified, the device name must be preceded by DISK =, TAPE ?, or PIPE ?, depending on the type of device. You can specify either one device or a comma-delimited set of devices, as shown in the following two examples:

 Backup_dev_1, Backup_dev_2, Backup_dev_3 TAPE = '\\.\Tape0', TAPE = '\\.\Tape1', TAPE = '\\.\Tape2'

Options

Table 32-1 lists the options available with the BACKUP command. If an option is available to only the database backup or only the log backup, the exception is noted.

Table 32-1. The BACKUP command options

Option	Description
BLOCKSIZE	This option specifies the physical block size in bytes.
DESCRIPTION	This option specifies the text description of the backup set. It is useful for locating the correct backup set from which to restore.
DIFFERENTIAL	This option specifies a differential backup. It is available with only a full database backup.
EXPIREDATE = date \| RETAINDAYS = days	The EXPIREDATE option specifies the date on which the backup set expires (and can be overwritten). RETAINDAYS specifies the number of days before the backup set expires.
PASSWORD = password	The PASSWORD option allows you to specify a password for the backup. This provides greater security for the backup itself.
FORMAT \| NOFORMAT	The FORMAT option specifies that the media header should be rewritten, thus invalidating the original data on the media. NOFORMAT specifies that the media header should not be rewritten.
INIT \| NOINIT	The INIT option specifies that the backup set be located in the first file on the media and preserves the media header but overwrites all data on the media—in other words, INIT overwrites whatever is on the tape. The NOINIT option specifies that the backup set be appended to the media. If you are reusing tapes, you will need to use this option.
MEDIADESCRIPTION = text	The description of the media set is set by this text field.
MEDIANAME = media_name	This option specifies the name of the media.
MEDIAPASSWORD = password	This option allows you to specify a password for the media set.
NAME = backup_set_name	This option allows you to set the name of the backup set.
NOSKIP \| SKIP	The NOSKIP option specifies that the expiration dates of the backup sets on the media be checked before they are overwritten. The SKIP option disables expiration date checking.
NO_TRUNCATE	This option specifies that the transaction log not be truncated after the backup. It is available with only log backups.
NOUNLOAD \| UNLOAD	The NOUNLOAD option specifies that the media not be unloaded (for example, that a tape not be ejected) after the backup is completed. The UNLOAD option specifies that the media be unloaded after the backup is completed.
RESTART	This option instructs SQL Server to restart a backup that was interrupted.
STATS [ = percentage ]	This option displays a message after the specified percentage of the backup is completed. It is useful if you like to view the progress of operations.

Be sure you specify whether the backup should be appended to the media or the media should be overwritten, as described earlier; the option you choose can affect the amount of data that can fit on a tape. If you are backing up data to a previously used tape device and do not either erase the tape or specify that it should be overwritten, you might soon find yourself out of space on the tape. In append mode, the backup program will use only the space available at the end of the tape.

REAL WORLD Using BACKUP
In this section, we'll look at a couple of examples of using the BACKUP T-SQL command.
The following statement will back up the data files for the Example database:
 BACKUP DATABASE Example TO Backup_Dev_1, Backup_Dev_2 WITH DESCRIPTION = "DB backup of example", STATS = 5 GO 
The backup devices are Backup_Dev_1 and Backup_Dev_2, and a status message will be displayed each time 5 percent of the backup is completed. Notice that a description of the backup is provided in the preceding example.

If you are testing this example on a small database, such as Northwind, you won't see the statistics showing 5 percent increments. Instead, you might see increments such as 7 percent, 16 percent, and so forth. This discrepancy occurs because the backup program is reading and writing more than 5 percent of the entire backup at a time. With larger data sets, the increments written will each be smaller than 5 percent, so the messages will appear as expected.

The following statement will back up the transaction log for the Example database:
 BACKUP LOG Example TO Backup_Dev_3, Backup_Dev_4 WITH DESCRIPTION = "DB backup of example", STATS = 25 GO 
The backup devices are Backup_Dev_3 and Backup_Dev_4, and status messages will be displayed at 25 percent intervals. The resulting output displays the percentage of the operation that is completed, as well as the outcome of the backup. You will be informed how many pages were backed up, how long the backup took, and how fast the backup was performed (in megabytes per second).

Managing the Backup

Because the T-SQL command BACKUP does not run under Enterprise Manager and thus does not run under the SQL Server Agent, you cannot schedule a job from within the BACKUP command. You can, however, schedule a T-SQL BACKUP command by using the SQL Server scheduling features. Once the job has been scheduled, it can be managed in exactly the same way the Enterprise Manager backup is managed.

Backing Up Using the Create Database Backup Wizard

Let's turn now to the third method of performing backups: using the Create Database Backup Wizard.

Performing the Backup

To perform a backup using the Create Database Backup Wizard, follow these steps:

In Enterprise Manager, click the database you want to back up, and then choose Wizards from the Tools menu to display the Select Wizard dialog box. Expand the Management folder in the Select Wizard dialog box, select Backup Wizard, and then click OK. The Create Database Backup Wizard welcome screen appears, as shown in Figure 32-9.

Figure 32-9. The Create Database Backup Wizard welcome screen.
Click Next to display the Select Database To Backup screen, shown in Figure 32-10. In this screen, you specify the database that will be backed up—Figure 32-10 shows the Northwind database selected.

Figure 32-10. The Select Database To Backup screen.
Click Next to display the Type Name And Description For Backup screen, shown in Figure 32-11. Here you provide a name and a description for the backup set by typing the desired name in the Name text box and the desired description in the Description text box. A good description can be helpful later if you have many backups.

Figure 32-11. The Type Name And Description For Backup screen.
Click Next to display the Select Type Of Backup screen, shown in Figure 32-12. Here you select the type of backup you want to perform—a full backup, a differential backup, or a transaction log backup. Figure 32-12 shows a full backup selected.

Figure 32-12. The Select Type Of Backup screen.
Click Next to display the Select Backup Destination And Action screen, shown in Figure 32-13. In the Select Backup Device area, specify whether you want to back up data to tape, file, or a particular backup device, and, if necessary, specify the filename or device name in the appropriate box. In the Properties area, specify whether the backup media should be overwritten or appended to, whether the tape should be ejected after the backup (if you are using tape), and whether the integrity of the backup should be verified. Verifying the integrity of the backup is a good idea because a bad tape can cause the entire backup to be useless. SQL Server verifies the integrity of the backup by reading the tape and verifying that all of the data is readable.
NOTE
Unfortunately, the Create Database Backup Wizard allows you to select only one backup device, which can drastically affect the performance of your backup, as you'll see in the section "Improving Backups" later in this chapter. For this reason, the Enterprise Manager backup method might be preferable to using the Create Database Backup Wizard.

Figure 32-13. The Select Backup Destination And Action screen.
Click Next to display the Backup Verification And Scheduling screen, shown in Figure 32-14. Here you have the options of specifying that the media labels should be checked and setting expiration dates, as described in the section "Backing Up Using Enterprise Manager." You can also schedule backups to be run at a later time by using the Edit Schedule dialog box, also described earlier (shown in Figure 32-5).

Figure 32-14. The Backup Verification And Scheduling screen.
Click Next to display the Completing The Create Database Backup Wizard screen, shown in Figure 32-15. Verify the information in the text box, and click Finish to launch the backup.

Figure 32-15. The Completing The Create Database Backup Wizard screen.

Managing the Backup

You can only perform a backup or create a scheduled backup job by using the Create Database Backup Wizard. If you create a job, you must use Enterprise Manager or T-SQL commands to manage that job. Managing jobs is described briefly in the section "Backing Up Using Enterprise Manager" earlier in this chapter.

Keeping Track of Backups

When you perform a backup, whether it is through Enterprise Manager, T-SQL, or the Create Database Backup Wizard, a record of the backup is saved. This record is stored as a row within the backupfile table in the msdb database. During database recovery, this information is used to determine when the last backup was performed on that database. Other information, such as the backup set ID and the names of files that were backed up, is also saved. It is therefore important that the system databases be backed up periodically as well so that this information can be recovered if necessary.