SQL Server 2000 Backup Types and Recovery Models

Applies To: Windows Server 2003, Windows Server 2003 R2, Windows Server 2003 with SP1, Windows Server 2003 with SP2

SQL Server 2000 provides three different backup types and three different recovery models. You can combine these backup types and recovery models in different ways, depending on your data recovery requirements.

SQL Server 2000 Backup Types

You can perform three different types of backups on the MIIS database: database (or full), differential, and transaction log.

Database (or Full)   Full database backups are the default and the starting point for all other types of backups. A full database backup captures the entire database, including all entries in the transaction log—excluding any unallocated extents in the files. Pages are read directly from disk to increase the speed of the operation.

Differential Differential backups capture all the data that has changed since the last full database backup. Differential backups will increase the speed of the backup operation as well as the restore. Because only the changed or newly allocated extents (bitmap tracking) are captured, differential backups are faster and smaller than full database backups. During a recovery, the last differential backup can be restored without applying a series of the individual transaction log backups or differential backups. All changed data is captured in the last differential backup.

Transaction log Transaction log backups serially capture modifications to the database. Backups of the transaction log provide a history of the transactions that have taken place within the database. The backups of the log are then used in the recovery process to restore the database fully, to a point in time. Transaction log backups are applied to recover a database by rolling forward (redoing) any committed changes not reflected in the database and rolling back (undoing) uncommitted transactions. Log backups are smaller and are taken more frequently than full or differential backups.

For more information about these backup types, see “Chapter 3 - Backing Up and Restoring Databases” on the Microsoft TechNet Web site at https://go.microsoft.com/fwlink/?LinkID=15117.

SQL Server 2000 Recovery Models

SQL Server 2000 provides three different recovery models: Full Recovery, Bulk-Logged Recovery, and Simple Recovery. The recovery model determines whether and how transaction logs can be backed up. The recovery model that you choose has a significant impact on system performance (especially during heavy data loads) and also on your ability to recover data.

By default, the MIIS database is configured to use the Simple Recovery model. Administrators can choose to enhance their data recoverability options by using the Full Recovery or Bulk-Logged Recovery model. These enhanced methods can dramatically improve disk performance by reducing the amount of disk space used by the log. However, using the Full Recovery or Bulk-Logged Recovery model also makes backup and restoration procedures more complex.

Be sure that you thoroughly understand the differences between the recovery models before you apply the Full Recovery or Bulk-Logged Recovery model. Table 2 lists and describes the SQL Server 2000 recovery models.

Table 2   SQL Server 2000 Recovery Models

Model Benefits Work-Loss Exposure Recovery Point

Full Recovery

No work is lost due to a lost or damaged data file.

Can recover to a specified point in time (for example, prior to an application or user error).

None. However, if the log is damaged, changes made since the most recent log backup are lost and must be redone.

Can recover to any point in time.

Bulk-Logged Recovery

Permits high-performance bulk copy operations.

Minimal log space is used by bulk operations.

If the log is damaged, or bulk operations occurred after the most recent log backup, changes made since the last backup must be redone.

Can recover to the end of any backup.

Simple Recovery

Permits high-performance bulk copy operations.

Reclaims log space to keep space requirements small.

Changes made after the most recent database or differential backup are lost and must be redone.

Can recover to the end of any backup.

Note

For more information about recovery models, see “Backing Up and Restoring Databases” on the Microsoft MSDN Web site at https://go.microsoft.com/fwlink/?LinkID=10575.

You can change your recovery model to accommodate changes in your recovery requirements.

To change SQL Server 2000 recovery models

  1. In Microsoft SQL Server, click Enterprise Manager.

  2. Expand Microsoft SQL Servers, expand SQL Server Group, expand servername (where servername represents the computer on which SQL Server 2000 is running), and then expand Databases.

  3. Right-click the MIIS database, and then click Properties.

  4. On the Options tab, in the Model list, click a recovery model.

After you change the recovery model, it is recommended that you perform a full database backup.