Microsoft SQL Server 7.0 System Administration Training Kit by Microsoft Press, Microsoft

From the Publisher

The target audience of this title is SQL Server administrators who install, configure, and support SQL server in an enterprise network. Business owners, contractors, and database administrators will also find all they need to know about Microsoft SQL Server.

Product Details

• Table of Contents
• Read an Excerpt

Table of Contents

Read an Excerpt

Chapter 8: Backup and Restore Overview and Strategy

About This Chapter

Before You Begin

Lesson 1: Backup Overview

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

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 followingmishaps:

• 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

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

SQL Server Backup

Note      Previous versions of SQL Server used the terms dump for backup and load for restore. You will still see these terms in some documentation and Transact-SQL statements -- for example, sp-addumpdevice.

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.

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 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

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.

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

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.

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      Automatic database growth cannot occur during a backup operation.

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

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      Without a current backup of the master database, you must completely rebuild all of the system databases with the Rebuild Master (rebuildm) utility. This utility program rebuilds all system databases as a unit.

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.

Tip      It is recommended that user objects not be created in the master database. Otherwise, it needs to be backed up more frequently. Additionally, user objects compete with the system objects for space.

Note      Transaction log backups and differential backups cannot be performed on the master database. The master database needs to be restored in a single operation, so only complete database backups of this database are allowed.

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      It is recommended that user objects not be created in the msdb database. Otherwise, it needs to be backed up more frequently. Additionally, user objects compete with the system objects for space.

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      User objects created in the model database are added to every new database. Therefore, you should not add user objects to the model database unless you intend for them to be created in every new database.

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

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.

Lesson Summary

There are three types of backups: complete, differential, and transaction log. 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. ...