Home

	Expand Search

(Workplace UNIX) Introducing SQL Server

Chapter 4: Backing Up and Restoring Data

Chapter 4

Introduction

This chapter provides an overview of backup and recovery procedures, including:

• Using the transaction log
• Recovering databases automatically on startup
• Dumping and loading databases
• The role of Backup Server in backup and recovery
• Creating a backup plan

Tracking Database Changes

Changes made to data stored in SQL Server are called transactions. A transaction consists of one or more Transact-SQL statements that succeed or fail as a unit. A transaction is also defined as SQL Server's unit of work.

Each database has its own transaction log. The transaction log automatically records every transaction issued by each user of the database. You cannot turn off transaction logging.

If any statement in a transaction fails to complete, SQL Server reverses all changes made by the transaction.

Automatic Database Recovery

Each time you restart SQL Server after manually shutting it down or after an abnormal termination, SQL Server automatically performs a set of recovery procedures on each database.

The recovery mechanism compares each database to its transaction log. If the log record for a specific change is more recent than the data page, the change is applied to the database from the transaction log. If a transaction was in process and not committed at the time of the failure, the recovery mechanism reverses, or rolls back, all changes that were made by the transaction, ensuring that the entire transaction succeeds or fails as a unit.

Dumping and Loading Databases

In case of media failure, such as a disk crash or a power failure, you can restore your databases only if you have been making regular backups of the databases and their transaction logs. Full recovery depends on regularly backing up databases and database objects.

Database Consistency

Before backing up a database, you should check the integrity of the internal structures of a database. The System Administrator or Database Owner should run database consistency checks on a regular basis.

The Database Consistency Checker (dbcc) is a set of utilities for checking the logical and physical consistency of a database. Use the dbcc commands:

• Before backing up a database
• As part of regular database maintenance to detect, and often correct, errors before they affect the user's ability to use SQL Server
• If you suspect that a database is damaged

  For example, if using a specific table generates the message "Table corrupt," you can use dbcc to determine if other tables in the database are also damaged.

Routine Database and Transaction Log Dumps

Dumping a database makes a copy of the entire database, including both the data and the transaction log.

You can make routine backups of the transaction log, similar to the incremental backups provided by many operating systems. This operation copies the transaction log, providing a record of any database changes made since the last database or transaction log dump.

Dumping a transaction log takes less time and storage space than a full database backup and it should be done more often. Users can keep making changes to the database while the transaction log dump is taking place.

Transaction logs can become full quickly in development environments or in production environments where there is intensive updating of database records. When transaction logs become full, operations of SQL Server stop and user queries in process are aborted. It is important to monitor transaction log size and to activate thresholds that warn when transaction logs are becoming full. You can back up a transaction log and then remove committed transactions to make more space.

Restoring an Entire Database

To restore an entire database after database corruption has occurred, initiate the recovery by loading the most recent database dump.

After you load the database, load each transaction log dump in the order in which it was made. This process reconstructs the database by re-executing the changes recorded in the transaction logs.

When the entire sequence of transaction log dumps has been loaded, the database reflects all transactions that had been committed at the time of the last transaction log dump.

Backup Server

Backup Server is an Open Server program that backs up and restores SQL Server databases. Backup Server can run on the same computer as SQL Server or you can perform backups over the network, using one Backup Server on a remote computer and another on the local computer.

Backup Server supports a type of dumping referred to as dump striping. This allows you to use up to 32 backup devices in parallel, thus splitting the database into approximately equal portions and backing up each portion to a separate device.

While dumps and loads execute, SQL Server and Backup Server use remote procedure calls to exchange instructions and status messages. Backup Server, not SQL Server, performs all data transfer for the dump and load commands. Using Backup Server requires some setup procedures so that SQL Server and Backup Server can communicate across the network.

Creating a Backup Plan

You should develop and test backup procedures for recovering from data corruption. Determine a reasonable backup schedule and adhere to it. If you develop, document, and test your backup procedures ahead of time, you are better prepared to get databases back online in the event of a system crash or media failure.

User Databases

One of the major activities in developing a backup plan is to determine how often to back up your databases. The frequency of backups determines how much work is lost in the event of a media failure. The following guidelines can help you decide when to dump user databases and transaction logs:

• Dump each user database just after you create it, thus providing a base point.
• At a minimum, back up the transaction log daily and the user databases weekly.
• Back up frequently updated databases often (even as much as every half hour), determining the frequency by deciding how much data you can afford to lose in the event of media failure.
• Back up interdependent databases¾databases where there are cross-database transactions, triggers, or referential integrity¾at the same time, during a period when there is no cross-database data modification activity.
• In addition to routine dumps, dump a database each time you perform any of the following activities:
  • Creating a new index
  • Performing an unlogged operation
  • Dumping the database with a truncated transaction log or no transaction log

System Databases

Follow the guidelines below when backing up SQL Server system databases:

• Back up the master database after each command that affects disks, storage, databases, or segments (for example, creating a user database, mirroring a disk, or resetting a configuration variable).
• For further protection, save the scripts that initialize, create or modify databases and make a hard copy of your sysdatabases, sysusages, and sysdevices system tables each time you issue one of these commands.
• Each time you change the model database, make a new backup.
• If you change permissions on some system procedures, or create your own system procedures, back up the sybsystemprocs system database.

You need to restore the master database if it is damaged by a media failure or by internal corruption in the database. A damaged master database makes itself known in one or more of these ways:

• SQL Server cannot start
• There are frequent or debilitating crashes or input/output errors
• dbcc (the database consistency checker) reports damage during a regularly scheduled check of databases

Other situations may require you to restore other system databases, such as model.

Getting More Information

The chart below lists sources of information for topics discussed in this chapter.

Topic	Source(s) of Information
Backup Server	SQL Server System Administration Guide
Database consistency	SQL Server System Administration Guide
Dumping and loading	Configuring Sybase SQL Server for your platform SQL Server System Administration Guide
Error messages	SQL Server Reference Supplement Error Messages Guide
Global variables	SQL Server System Administration Guide
SQL Server Manager	SQL Server Manager User's Guide
System stored procedures	SQL Server System Administration Guide