Performing Database Backups

Backups are an important part of any maintenance strategy. In a SQL Server 2005 environment, you can perform backups through the SQL Server Management Studio interface, by using T-SQL code, by using a customized application, or via third-party tools.

Exam Alert

On the exam, you will see questions related to backup theories and the specifics of the options used for the BACKUP T-SQL command and Management Studio interface. Questions will likely pertain to recovery models and the VERIFYONLY, INIT, FORMAT, and SKIP options for performing backups, particularly to tape.

You can perform the SQL Server backup process while users are online and connected to the server. Although it is possible to perform backups any time, it is recommended that the procedure be scheduled for a time when there is little or no database activity. As discussed in the following sections, the type of backups you do and how you implement it depends on the recovery model in which the database is configured.

Recovery Models and Backups

The recovery model setting of each database determines how the database engine interacts with the data and log files. It also controls the backup and restore processes and their abilities. As changes are made to the data in the database, the information is first stored in the log. The committed changes in the log are then periodically pushed into the actual data files.

You have three choices when dealing with the recovery models:

• Simple When you select this option, SQL Server does minimal logging to allow for recovery after a system crash.

• Bulk-logged When you select this option, SQL Server fully logs most transactions and minimally logs bulk operations.

• Full When you select this option, SQL Server fully logs all transactions.

You can set the model through the use of the sp_dboption stored procedure or through the Options page of the Database Properties dialog, as shown in Figure 6.1.

Figure 6.1. You use the Options page of the Database Properties dialog to set the recovery model.

With the simple model, after each backup, the transaction log is truncated. Transaction log backups are neither required nor permitted. An important aspect of this model is that it does not allow for point-in-time recovery. Without transaction log backups occurring, it is not possible to recover to the point of failure. You can restore the data only to the point provided by the last backup.

The simple recovery model is usually not an appropriate model for most production systems because there is definitive work-loss exposure. When a crash occurs, any operations performed between that time and the previous backup would have to be repeated. In some events, this would mean permanent loss of many changes that could not be repeated.

The bulk-logged recovery model provides only minimal logging of bulk operations. Although protecting against media failure and providing the best performance for bulk operations, this model increases the risk of data loss for bulk operations. Because only minimal logging occurs, a point-in-time restore may not be possible when the log backup contains any bulk-logged operations. In this situation, you can recover the database only to the end of the log backup, not to a point in the middle of the log.

A transaction log backup must capture both the log and the results of every bulk-logged operation (as occurs in the full model). Backing up a log that contains bulk-logged operations requires access to the data files containing the bulk-logged transactions, making a log backup potentially quite large.

The full recovery model prevents almost all data loss in almost any disaster scenario. It includes both database backups and transaction log backups, and it protects against media failure. To protect against loss of transactions under the full recovery model, the transaction log must be protected against damage. It is strongly recommend that you use fault-tolerant disk storage for the transaction log.

In the Enterprise Edition of SQL Server 2005, you can restore a database without taking all of it offline. You must set your recovery mode to full or bulk-logged for this to be possible. In Microsoft SQL Server 2005, you can back up the log while a data or differential backup is running.

Exam Alert

Remember that you must get the database out of the simple model to allow for point-in-time restore operations to occur.

Recovery Models Using T-SQL

In previous versions of SQL Server, you could set recovery models by changing the properties of the database options via the sp_dboption stored procedure. SQL Server still supports that option, but using the new SET RECOVERY option of the ALTER DATABASE statement is the preferred technique for setting the recovery model through code.

Using sp_dboption and setting the database option trunc. log on chkpt. (which means truncate log on checkpoint) to TRue sets the recovery model of the database to simple. Setting the trunc. log on chkpt. option to false sets the recovery model to full. If you set the select into/bulkcopy database option to TRue, you set the recovery model to bulk logged.

Note

Using the select into/bulkcopy database option is required to create a permanent table with SELECT INTO. In SQL Server 2005, however, this option is not required, and you should avoid it, using ALTER DATABASE with the SET RECOVERY option instead. The sp_dboption stored procedure will be removed in a future version of Microsoft SQL Server.

If you are making changes to the database options through a query, using ALTER DATABASE is the preferred technique for implementing any of these database changes, including setting the recovery model. The following example sets the Northwind database to the full recovery model:

ALTER DATABASE Northwind SET RECOVERY FULL 

As well as setting the recovery model for a database, the ALTER DATABASE statement is used to modify a database or the files and filegroups associated with the database. You use this statement to add or remove files and filegroups from a database. You can use the ALTER DATABASE statement to change any of the database attributes, change the collation sequence, and set any of the other database options.

Backup Types and Scenarios

Making backups is an important part of a maintenance plan, and the 70-431 exam tests numerous options related to the backup process. The SQL Server backup process can make a backup of an entire database, a transaction log, or one or more files or filegroups. When you perform a backup, you must indicate whether it is to be a full, differential or transaction log backup. You can perform the backup to disk or tape, on one or multiple devices. You can set several options to perform a backup in the manner required by the maintenance plan and configuration.

You can select many of the options available in the backup process through the SQL Server Management Studio interface. The BACKUP T-SQL command, on the other hand, provides more functionality and a considerable number of additional options as discussed in the following sections.

Exam Alert

You can expect the 70-431 exam to include questions on many of the options of the BACKUP and RESTORE T-SQL commands. Although it is possible to perform the operations through Server Management Studio, the exam expects you to know the options for performing backups through use of the T-SQL statements. Pay close attention to the options described throughout the sections to follow.

The Microsoft SQL Server Management Studio provides most of the functionality you would ever need to perform and maintain backups in any production environment. Figure 6.2 shows the options available in Server Management Studio.

Figure 6.2. The Back Up dialog's options.

On the General page of the dialog, you first select the backup type. The backup type has two elements: the type itself and the component to be backed up. It is important to note that the recovery model in Figure 6.2 is full, which allows for all possible backup types. The drop-down list in this dialog allows you to select the following options:

• Full If you select the Full option, SQL Server will back up the entire database, including, at the end of the process, the transaction log. The log is needed so that the database can be recovered in its entirety in the event that a restore is needed. Because this backup includes the log, a full backup represents the database at the time the backup was made, including any operations that were completed while the backup was being created. This process of including the log allows a restore to be performed, using the point-in-time options to bring the database to its most recent form just before the point of failure.

• Differential A differential database backup records only the data that has changed because the last full database backup is also referred to as the differential base. Differential backups are often used to supplement a full database backup because they are smaller and faster than full database backups.

• Transaction Log A transaction log backup backs up only entries that have been recorded to the logs. Log backups generally use fewer resources than database backups because the only thing being backed up is the content of the log files. As a result, you can create them more frequently than database backups to reduce your risk of losing data.

A production environment generally combines all three backup types in a maintenance plan. A typical scenario may be to perform a full backup every Sunday, with daily differential backups and transaction log backups every few hours.

The second element of the backup type is the component. You can either select to back up the entire database or to back up a portion of the database by selecting files and filegroups and then choosing which files or filegroups to include in the backup.

Setting the Options of a Backup

The T-SQL BACKUP statement offers a full array of options for performing a backup. Many of these options are available through Server Management Studio, but there are a considerable number of options available only through the use of the T-SQL BACKUP command.

You need to have a good understanding of many of the options available. However, you don't want to go overboard. This chapter describes the level of detail you need to succeed on the 70-431 exam. Let us first look at the Options page of the Backup dialog and continue through the relevant options of the T-SQL BACKUP command.

You use the first set of elements on the Options page to specify to use existing media or create new media. You can append (that is, apply to the end) to an existing set or overwrite the content. If you are creating a new media set, you need to specify a media set name and description.

When you overwrite media, the current backup overwrites the existing contents. Overwriting always overwrites all backup sets in a media set. The tape headers are left in place. When you overwrite backups, any existing media header is preserved, and the new backup is created as the first backup. If there is no existing media header, a valid media header with an associated media name and media description is written automatically.

The second set of elements relate to ensuring the integrity and reliability of the backup. Backups should always be verified after being created to make certain that the content is valid. In SQL Server 2005, you can also perform a checksum operation to further ensure the validity of a backup. If a checksum fails and produces errors, you can select the Continue on Error check box to continue or abort the operation.

The third set of elements on the Options page is often grayed out because these elements apply only to transaction log backups. In a default transaction log backup, the log is truncated when the backup is complete. The alternative is to only back up the tail end of the file (by making a tail-log backup) that represents entries not previously backed up and to not remove any of the contents.

Note

If logs were never truncated, the log would grow until it filled all available space. The truncation process marks for reuse the space that was used by the old log records. This is why the log file does not reduce in size when truncation occurs. If you need to reduce the size of a log file, you must run a shrink procedure against the file.

A tail-log backup captures the portion of the log that has not been previously backed up. This also represents the last backup of interest in a recovery plan. A tail-log backup is often performed before a point-in-time restore is used. If you were to restore a database without first performing a tail-log backup, you would receive errors. If the tail portion of the log is not needed in a restore operation, you can avoid these errors by using the RESTORE statement and the WITH REPLACE or WITH STOPAT clause. Typically, you make a tail-log backup by using the BACKUP statement's NORECOVERY or NO_TRUNCATE option.

Exam Alert

If you need to get the database back to a specific point in time, you can do so by using a snapshot. Alternatively, assuming that you are in full recovery mode, you can perform a final tail-log backup and use the RESTORE statement WITH STOPAT to utilize a point-in-time recovery.

You use the final section of elements on the Options page of the dialog to indicate what happens to the tape that is used when the backup has completed. You can select to have a tape unloaded after backup and, if unloaded, optionally rewind the tape.

Options of the T-SQL BACKUP Statement

Many options are available with the T-SQL BACKUP statement. For the purpose of the exam, you need to concentrate on the subset of those that you are most likely to use. For more information on other options and more complete syntax examples, consult SQL Server Books Online. The following are some of the most commonly used features of the BACKUP statement:

• INIT, NOINIT, SKIP, and NOSKIP

• FORMAT and NOFORMAT

• UNLOAD, NOUNLOAD, REWIND, and NOREWIND

• STOP_ON_ERROR, CONTINUE_AFTER_ERROR, CHECKSUM, and NO_CHECKSUM

  • NO_LOG and trUNCATE_ONLY

  • NORECOVERY and STANDBY

• COPY_ONLY

Many of these options apply to specific implementations of the backup process and are not available for all types of backups. There are many common scenarios to look at in performing backups. Most operations perform a series of checks against the media before applying the backup. If you specify the SKIP option, you disable the checking of backup set expiration and name. You could also choose to initialize the backup set with INIT.

Overwriting Existing Backups and Preserving the Header (INIT)

SQL Server identifies backup storage media through the use of identification of the backup media as specific objects identified as media family. media family is a set of one or more backups created on a single named device. Each device is supplied with media header to identify the contents of the device. When you use the INIT option, all backup sets are overwritten, but the media header is preserved. You need to ensure that you truly do not want to keep the existing content because any existing backup set on that device is overwritten.

By default, BACKUP does not overwrite the media if any backup set has not yet expired. Also, if the backup set name given does not match the name on the media, no content is removed. INIT overrides this default process.

If SKIP is specified with the INIT option and the volume contains a valid media header, the media password is verified before any backup sets are overwritten on the media, and only the media header is preserved. If the media is empty, a new media header is generated.

When NOSKIP is set with the INIT option and the volume contains a valid media header, the media password is verified, and the media name, if supplied, is verified. The expiration of any set is verified, and the backup does not continue if any of the verifications fail. If there is no valid media header, one is generated.

Appending to Existing Backups (NOINIT)

With many operations, you simply want to add the current backup operation to the media, without destroying the previously saved content. The NOINIT option allows a backup to be appended to the specified set, preserving any existing backup sets. This is the default if no other options are specified.

If SKIP is specified with the NOINIT option and the volume contains a valid media header, the media password is verified before the data is appended to the existing media.

When NOSKIP is set with the NOINIT option and the volume contains a valid media header, the media password is verified, and the media name, if supplied, is verified before the data is appended to the existing media. If there is no valid media header, an error occurs.

Creating a New Media Set and Preparing a New Header (FORMAT)

At some point, you will want to completely reformat a media set and initialize a new header. As with any other format operation, you will lose any content that was previously stored on the media. FORMAT writes a new media header on all volumes used for the operation, overwriting any existing header and backup on the media. When you specify the FORMAT option, it also includes SKIP, so you do not need to specify the SKIP option. The default behavior is NOFORMAT, and the media header is not written.

Rewinding and Unloading the Tape When Finished (REWIND and UNLOAD)

You can use the REWIND option to release and rewind a tape after it fills. This is the default setting. You use NOREWIND to improve performance when performing multiple operations. When you supply this option, SQL Server keeps the tape open after the backup operation. NOREWIND includes NOUNLOAD, which means a tape is not unloaded automatically from the drive. You can use UNLOAD to specify that the tape is to be rewound and unloaded when the backup is finished. UNLOAD is the default setting.

Validating the Data as the Backup Occurs (CHECKSUM)

You should always verify a backup after it has completed, but in some situations, you might want to validate the data as the backup progresses. You can use the CHECKSUM option to specify that prior to writing a page to the backup media, the page should be verified. BACKUP verifies the page against a checksum or torn page, if that information is present on the page.

Regardless of whether page checksums are present, when you specify the use of a checksum the BACKUP operation generates a checksum of its own. A restore operation can use this generated value to validate the backup and ensure that it is not corrupt. The backup checksum is stored on the backup media, not on the database pages. BACKUP will never alter the data pages themselves. Because this is a processor-intensive operation, you can expect its use to affect workload and backup throughput. The default setting is NO_CHECKSUM, which explicitly disables the generation and validation of checksums.

You can use the STOP_ON_ERROR option in conjunction with CHECKSUM to stop further backup processing if a checksum does not verify. This is the default behavior. If a backup contains errors, it is usually best to repeat the backup operation anyway. CONTINUE_AFTER_ERROR instructs the SQL Server BACKUP command to continue despite encountering errors such as invalid checksums. You can use this option in place of NO_TRUNCATE to attempt a log backup on a damaged database.

Removing Inactive Log Entries and Truncating the Log (NO_LOG and TRUNCATE_ONLY)

You can use either the NO_LOG option or the trUNCATE_ONLY option to perform a checkpoint against a database. A checkpoint removes the inactive part of the log without making a backup copy of it and truncates the log by discarding all but the active log. This frees up space in the log, but it risks possible data loss and does not affect the log file size. For recovery purposes, after using either of these options, you must immediately execute BACKUP DATABASE to make a full or differential database backup. (The NO_LOG and TRUNCATE_ONLY options will be removed in a future release.) You do not need to specify a backup device when using these options because the log backup is not saved. You should use manual log truncation in this manner in only special circumstances, and you should create backups of the data immediately following the operation.

Setting Up a Warm Backup Secondary Database (NORECOVERY)

You use the NORECOVERY option when you perform a log backup to back up the tail of the log. NORECOVERY leaves the database in the RESTORING state so that future log backups can be applied. NORECOVERY is useful when failing over to a secondary database or when saving the tail of the log before performing a RESTORE operation. You can use this option in conjunction with NO_TRUNCATE to perform a best-effort log backup that skips truncation and takes the database into the RESTORING state atomically.

Setting Up a Read-Only Secondary Server (STANDBY)

You use the STANDBY option with the name of a file to hold the rolled back changes during a transaction log backup. STANDBY performs a backup of the tail of the log and leaves the database in a read-only and STANDBY state. The STANDBY clause performs a rollback of uncompleted transactions but with the option of further restores. Using the STANDBY option is equivalent to using BACKUP LOG WITH NORECOVERY followed by RESTORE WITH STANDBY.

The standby file becomes part of the database. When you perform the backup operation, the file is overwritten if it exists. If the file does not exist, it is created during the process. Rolled-back changes must be reversed if RESTORE LOG operations are subsequently performed.

Performing an Extra Backup (COPY_ONLY)

You might want to perform a backup operation that does not interfere with the maintenance plans you have in place. When you use the COPY_ONLY option, the backup has no effect on the normal sequence of backups and does not affect the overall backup and restore procedures for the database. Any differential backups taken later behave as if the copy-only backup does not exist.

Using a Backup for a System Database

You might want to periodically back up the master, model, and msdb system databases. There is really no purpose in backing up the tempdb system database because its contents are used only for temporary purposes and constantly change. System database backups are subject to a variety of restrictions, depending on which database is being backed up:

• The master database can only be backed up in full. You cannot perform differential or log backups of any sort.

• You can back up the model and msdb databases as you would other databases. When installation occurs, these databases are set to a simple recovery mode. To perform log backups on model or msdb, you must change the recovery mode to full or bulk-logged.