Microsoft SQL Server 7.0 System Administration Training Kit

Data loss and data corruption are major concerns for any database administrator. SQL Server provides a sophisticated backup mechanism that makes it possible to minimize and even eliminate data loss and data corruption. This lesson introduces the SQL Server backup process and the types of backup plans that can be implemented. You should carefully analyze the data protection requirements of your organization and produce a backup strategy that meets these requirements.

After this lesson, you will be able to

• Describe the SQL Server online backup mechanism
• Determine the appropriate times to perform backups

Estimated lesson time: 45 minutes

Preventing Data Loss

The need to prevent data loss is one of the most critical issues that system administrators encounter. You can minimize data loss by having a backup strategy and by performing regular backups.

Have a Backup Strategy

You must have a backup strategy to minimize data loss and recover lost data. You can lose data as a result of hardware or software failures or due to any of the following mishaps:

• Accidental or malicious use of the DELETE statement
• Accidental or malicious use of the UPDATE statement—for example, not using a WHERE clause with the UPDATE statement (all rows are updated rather than a single row in a particular table)
• Destructive viruses
• Natural disasters, such as fire, flood, and earthquakes
• Theft

If you have an appropriate backup strategy, you can restore data with minimal cost to production time and minimize the chance of permanent data loss. Think of a backup strategy as an insurance policy. Your backup strategy should put your system back to where it was before a problem occurred. As with an insurance policy, ask yourself, "How much am I willing to pay, and how much loss is acceptable to me?"

The costs associated with a backup strategy include the amount of time spent designing, implementing, automating, and testing the backup procedure. Although you cannot prevent data loss completely, you should design your backup strategy to minimize the extent of the damage. When you plan your backup strategy, consider the acceptable amount of time that the system can be down, as well as the acceptable amount of data loss (if any) in case of a system failure.

Back Up Regularly

How frequently you back up your database depends on the amount of data that you are willing to lose and the volume of database activity. When you back up user databases, consider the following facts and guidelines:

• You should back up your database frequently if your system is in an online transaction processing (OLTP) environment.
• You can back up your database less frequently if your system has little activity or is used primarily for decision support.
• You should try to schedule backups when SQL Server is not in the process of being heavily updated.
• You can back up different databases and parts of databases independently; this means that you can design your databases to support more regular backups of frequently modified data.

Using Backup and Restore for Other Purposes

Backing up and restoring databases is useful for other purposes, such as moving or copying a database from one server to another. By backing up a database on one computer and restoring it to another, you can quickly and easily copy a database.

SQL Server Backup

SQL Server allows you to perform a number of different types of backups. This section describes the types of backups you can perform and gives an overview of the backup process.

NOTE

Complete Database Backups

When you perform a complete backup of a database, SQL Server backs up

• The schema and file structure
• The data
• Portions of the transaction log files

The portion of the transaction log that is backed up contains database activities occurring since the start of the backup process.

A complete database backup records all active data pages from the database. Unused pages are not backed up, so the backup will usually be smaller than the database. SQL Server records the specifications of the original database files. This type of backup is used to re-create all files of a database in their original locations, complete with objects and data, when you restore a database.

Transaction Log Backups

In a transaction log backup, only the transaction log is backed up. Transaction log backups record the transactions that have modified a database since the last complete database, differential database, or transaction log backup.

After the transaction log is backed up, the inactive portion of the transaction log is truncated (removed); this frees up space in the transaction log so that it does not become full. Transaction log backups cannot be used if nonlogged transactions are performed on the database (that is, if the trunc. log on chkpt. database option is true). Transaction log backups cannot be restored without a previous database backup.

Differential Database Backups

Differential database backups record the data pages that have changed since the last complete database backup, making a differential backup smaller than a database backup. Differential backups allow you to make less-frequent database backups. They cannot be restored without a previous complete database backup. If you need to restore a database, you must restore both the most recent complete database backup and the most recent differential database backup.

Understanding Complete, Transaction Log, and Differential Backups

To understand the differences among complete, transaction log, and differential backups, think of a manual in a three-ring binder. You can store a copy of the manual or changes to the manual in a number of ways:

• You could keep a copy of the entire manual in the binder; this represents a complete database backup.
• You could keep a list of all changes made to the manual—for example, you could make a note to replace all occurrences of the phrase "raises an alert" with the phrase "fires an alert." You would then go through the entire manual looking for the phrase "raises an alert," scratching it out, and writing in the phrase "fires an alert." This is similar to applying a transaction log backup to a database.
• You could keep copies of replacement pages representing all changes to the manual as of a given date. You would then remove the old pages from the binder and replace them with the new ones. This is similar to restoring a differential database backup.

File or Filegroup Backups

File or filegroup backups are a specialized form of database backup in which only certain individual files or filegroups from a database are backed up. This is usually done when there is not enough time to perform a database backup. To make use of file and filegroup backups, transaction log backups must be created as well.

Performing and Storing Backups

You can back up databases by executing Transact-SQL statements or by using SQL Server Enterprise Manager. When planning a backup strategy, assign someone the responsibility of performing the backups and checking that the backup process is completing correctly. Also consider where your backups will be stored.

Who Performs Backups

Members of the following roles have permission to back up a database:

• Members of the sysadmin fixed server role can back up all databases on the server.
• Members of the db_owner fixed database role for a database can back up that database.
• Members of the db_backupoperator fixed database role for a database can back up that database.

Additional roles can be created and granted permission to back up one or more databases.

Where to Store Backups

SQL Server can back up to hard disk files, tapes, or named pipe devices. To determine which method of storing backups is right for you, consider the following:

• Disk files (local or network) are the most common medium for storing backups. Once a database has been backed up to a disk file, the disk file can in turn be backed up to tape as part of the regular file system backup. In case of media failure, the disk files will have to be restored from tape and then the database restored in SQL Server from the disk files.

IMPORTANT

---

If you want to back up to a network disk file, you must use the Transact-SQL BACKUP command. You cannot perform the backup using SQL Server Enterprise Manager.
• When you back up to a tape, the tape drive must be attached locally to SQL Server. If you back up to tape, backups can be stored at another location.
• SQL Server provides the ability to back up to a named pipe to allow users to take advantage of the backup and restore features of third-party software packages.

The SQL Server Online Backup Process

When SQL Server backs up an online database (one that is actively being utilized by clients), it performs the following steps:

1. Issues a checkpoint on the database and records the log sequence number (LSN) of the oldest active transaction log record.
2. Writes all pages to the backup medium by reading the disks directly (bypassing the data cache).
3. Writes all transaction log records from the LSN captured in step 1 through the end of the log.

Activities That Are Restricted During Backup

You can back up a database while the database is online and active. However, a few operations, listed here, cannot take place during the backup process:

• Creating or deleting database files
• Creating indexes
• Performing non-logged operations
• Shrinking a database

NOTE

If you attempt to start a backup operation when one of these operations is in progress, the backup operation aborts. If a backup operation is already in progress and one of these operations is attempted, the operation fails and the backup operation continues.

If the Transaction Log Becomes Full

If you do not perform regular transaction log backups, the transaction log will eventually become full; either it will grow to its MAXSIZE or the disk will run out of space, preventing further automatic growth. When this happens, SQL Server will prevent further database modifications until you clear the transaction log. If you do not plan to use transaction log backups as part of your backup strategy, do one of the following:

• Clear the transaction log regularly, using the BACKUP LOG statement with the WITH TRUNCATE_ONLY option.

CAUTION

---

If you use the WITH TRUNCATE_ONLY option with the BACKUP LOG statement, you cannot restore from the backup, as the backup is not written to a backup device.
• Set the trunc. log on chkpt. database option to true.

When you use this option, the transaction log is truncated automatically whenever a checkpoint occurs. The transaction log does not contain the changes that were made to the database since the last database backup.

CAUTION

---

If you set the trunc. log on chkpt. option to true, you cannot use transaction log backups as part of your backup strategy, since the log will not contain all of the transactions.

When to Back Up Databases

Your decision as to when and how often you back up your database depends on your particular business environment. There are also times when you may need to perform unscheduled backups. For instance, after loading data or performing database maintenance, you may need to back up a specific user database or the system databases.

Backing Up System Databases

System databases store important data about SQL Server and all user databases. Therefore, you should back up system databases regularly, as well as before performing actions that modify them.

The master database contains system information and high-level information about all databases on a SQL Server. If the master database becomes damaged, SQL Server may fail to start, and user databases may be unavailable. In this case, the master database has to be restored from a backup before user databases can be restored or referenced.

NOTE

When you execute certain statements or system stored procedures, SQL Server modifies the master database. Therefore, back up the master database after using any of the following:

• The CREATE DATABASE, ALTER DATABASE, and DROP DATABASE statements, which create, alter, and remove databases. Automatic file growth does not affect the master database. Adding or removing files or filegroups does affect it.
• The sp_addlogin, sp_addremotelogin, sp_droplogin, sp_dropremotelogin, sp_grantlogin, and sp_password system stored procedures, which work with logins and other operations related to login security. Operations related to database security, such as adding roles or assigning database permissions, do not affect the master database.
• The sp_addserver, sp_addlinkedserver, and sp_dropserver system stored procedures, which add or drop servers.
• The sp_addumpdevice and sp_dropdevice system stored procedures, which add and remove backup devices.
• The sp_renamedb system stored procedure, which renames a database.
• The sp_dboption, sp_configure, and sp_serveroption system stored procedures, which change serverwide or database configuration options.

You should also back up the master database after using SQL Server Enterprise Manager to perform any of the operations just listed.

TIP

NOTE

The msdb Database

Back up the msdb database after modifying information about jobs, alerts, and operators that are used by SQL Server Agent. If you do not have a current backup of the msdb database, you must rebuild all of the system databases if a system failure occurs and then re-create each job, alert, and operator.

TIP

The model Database

Back up the model database if you modify it to include the default configuration for all new user databases. If the master or msdb databases are rebuilt, the model database is also rebuilt, and therefore changes are lost. You can restore a backup of your customized model database in case of a system failure.

NOTE

The tempdb Database

SQL Server does not allow the tempdb database to be backed up, as it contains only temporary data that will never need to be restored.

Backing Up User Databases

You should plan to back up user databases regularly. You should also perform a backup after a database or index is created and when certain nonlogged operations are executed.

After Creating Databases

Back up a database after it has been created or loaded with data. Without a complete database backup, you cannot restore transaction log or differential database backups, because you must have a baseline for these backups.

After Creating Indexes

The transaction log records only the fact that an index was created, not the actual data page modifications. Therefore, although you are not required to do so, you should perform a backup of the database after creating indexes on large tables.

Backing up a database after an index is created ensures that the database backup device contains the data and the index structures. This will save you time during the restore process if a database is lost.

If you back up only the transaction log after an index is created, SQL Server must rebuild the index when you restore that transaction log. For large tables, the amount of time required to do this may be longer than the time it takes to restore a database backup.

After Clearing the Transaction Log

You should perform a complete backup of a database after clearing the transaction log with the BACKUP LOG WITH TRUNCATE_ONLY statement. After this statement executes, the transaction log no longer contains a record of database activity and cannot be used to recover changes to the database.

After Performing Nonlogged Operations

Operations that are not recorded to the transaction log are called nonlogged operations; they are usually used to prevent the transaction log from filling rapidly and to enhance performance during large operations.

You cannot recover changes made by the following nonlogged operations:

• Use of the WRITETEXT or UPDATETEXT statements. These statements modify data in text columns. By default, due to the size of text modifications, this activity is not recorded in the transaction log. Note that you can specify the WITH LOG option to have these activities logged normally.
• Use of the SELECT INTO statement when the trunc. log on chkpt. database option is set to true.
• Fast bulk copy using the bcp command-line utility when the trunc. log on chkpt. database option is set to true.

You should make a backup of a database after performing a nonlogged operation because the transaction log has no record of the data that has been added to or modified in the database.

Lesson Summary

An appropriate backup strategy will allow you to both restore data with minimal cost to production time and minimize the chance of permanent data loss. SQL Server backups can be performed by using Transact-SQL or the SQL Server Enterprise Manager.

There are three types of backups; complete, differential, and transaction log backups. When deciding which to use and how often to use them, consider your business environment. At times you will need to perform unscheduled backups because of activities that modify the system databases or nonlogged operations.