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.)
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.)
To create a backup device by using Enterprise Manager, follow these steps:
Figure 32-2. The Backup Device Properties window.
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.
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'
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.
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.
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.
To perform a backup by using Enterprise Manager, follow these steps:
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.
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.
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:
Remember, multiple backups can be (and frequently are) performed to the same backup device.
Figure 32-5. The Edit Schedule dialog box.
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.
Figure 32-7. The Options tab of the SQL Server Backup dialog box.
To view, delete, or modify backup jobs that have been scheduled, follow these steps:
Figure 32-8. Jobs displayed in Enterprise Manager.
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:
Because these two commands provide most of the same options, we'll look at them together.
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'
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 GOThe 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 GOThe 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).
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.
Let's turn now to the third method of performing backups: using the Create Database Backup Wizard.
To perform a backup using the Create Database Backup Wizard, follow these steps:
Figure 32-9. The Create Database Backup Wizard welcome screen.
Figure 32-10. The Select Database To Backup screen.
Figure 32-11. The Type Name And Description For Backup screen.
Figure 32-12. The Select Type Of Backup screen.
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.
Figure 32-14. The Backup Verification And Scheduling screen.
Figure 32-15. The Completing The Create Database Backup Wizard screen.
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.
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.