Backup Terminology | Microsoft SQL Server 2000 Administrators Companion

3 4

Before we look at backup techniques, let's review some terminology. In this section, you'll learn some basic facts about backup, restore, and recovery operations.

Backup and Restore

Backup and restore operations are related and involve saving data from the database for later use, similar to the backup and restore operations that can be performed by the operating system. During the backup, data is copied from the database and saved in another location. The difference between an operating system backup and a database backup is that the operating system backup can save individual files, whereas the database backup saves the entire database. Usually, a database is shared by many users, whereas many operating system files belong to individual users. Thus, a database backup backs up all of the user's data at once. Because SQL Server is designed for maximum uptime, the backup process is designed to work while the database is up and running, and even while users are accessing the database.

During the restore, the backed up data is copied back to the database. (Don't confuse restore with recovery; these are two separate operations.) Unlike the backup process, the restore process cannot be done while SQL Server is up and running. In addition, a table cannot be restored separately. If one user loses some data in the database, the lost data cannot be easily restored because the restore operation will restore the entire database or a portion of it. Distinguishing a single user's data from all the data in the database can be difficult.

Recovery

Recovery involves the ability of the relational database management system (RDBMS) to survive a system failure and replay (recover) transactions. SQL Server does not immediately write changes to disk every time a change is made in the database. If it did, a large system (such as the system at a bank) would perform more slowly, because every transaction would have to wait for writes to complete before proceeding. This would cause every transaction to experience a delay.

Because of the delay in writing changes to disk, a system failure might leave the database in a corrupted state, because some changes made to the database might not have been written to disk or changes written to disk might not have been committed. To maintain the integrity of the database, SQL Server logs all changes in a transaction log. (The transaction log is described in detail in the section "The Transaction Log" later in this chapter.) When SQL Server restarts after a system failure, it uses the transaction log to roll forward transactions that were committed but not written to disk and to roll back transactions that were not committed at the time of the failure. In this manner, data accuracy is guaranteed.

SQL Server must be prepared to handle several types of transactions during recovery, including the following:

Transactions that are queries only No recovery is necessary.
Transactions that changed data in the database and were committed but were not written to disk During recovery, SQL Server reads the data pages from disk, reapplies the changes, and then rewrites the pages to disk.
Transactions that changed data in the database, were committed, and were written to disk During recovery, SQL Server determines that the changes were written to disk. No other intervention is required.
Transactions that changed data in the database and were not committed During recovery, SQL Server uses the transaction log to undo any changes that were made to data pages and restores the database to the state it was in before the transactions started.

When SQL Server restarts from a system failure, the recovery mechanism starts automatically. The recovery mechanism uses the transaction log to determine which transactions need to be recovered and which do not. Many of the transactions will not need recovery, but SQL Server must read the transaction log to determine which transactions do require recovery. SQL Server starts reading the transaction log at the point where the last checkpoint occurred. (Checkpoints are covered later in this chapter.)

NOTE
Because the transaction log is crucial for the recovery of transactions in the event of a failure, it should always reside on a RAID 1 (mirrored) volume. (RAID is explained in Chapter 5.)

In the event of a system failure that requires the database to be restored from backup files (such as the loss of a disk drive), the transaction log and transaction log backups are used to restore the database to the state it was in at the point of failure. Thus, restore and recovery operations usually work together. In the event of a power failure, only recovery might be necessary.

NOTE
A transaction that is rolled back by SQL Server is identical to a transaction that ends with the ROLLBACK command. The transaction is nullified, and all the data is restored to its original state. When a transaction is rolled forward, the changes made to the database but not written to disk are replayed, so the data files are returned to the state they were in at the time of failure. In other words, rolling forward transactions brings the database back to the state it was in at the point of failure, minus all uncommitted transactions, by redoing the committed transactions.