The following illustration shows the easiest backup strategy under the full recovery model. In the illustration, a database backup, Db_1, and two routine log backup, Log_1 and Log_2, have been taken. Some time after the Log_2 log backup, data loss occurs in the database. Before these three backups are restored, the database administrator must back up the active log (the tail of the log). The database administrator then restores Db_1, Log_1, and Log_2 without recovering the database. Then the database administrator restores and recovers the tail-log backup (Tail). This recovers the database to the point of failure, recovering all the data.

After the first full database backup is completed and regular log backups start, the potential work-loss exposure is narrowed to the time between when the database is damaged and the most recent regular log backup. Therefore, we recommend that you take log backups frequently enough to keep your work-loss exposure within the confines required by your business requirements.

After a failure, you can try to back up the tail of the log (the log that is not yet backed up). If the tail-log backup succeeds, you can avoid any work loss by restoring the database up to the point of failure.

You can use a series of log backups to roll a database forward to any point in time that is contained in one of the log backups. To minimize the risk, we recommend schedule routine log backups. Be aware that to minimize your restore time, you can supplement each full backup with a series of differential backups of the same data.

The following illustration shows a backup strategy that supplements full database backups with differential database backups and also a series of routine log backups. The presence of transaction log backups reduces potential work-loss exposure to the time after the most recent log backup. After the first database backup, a series of three differential backups is taken. The third differential backup is large enough that the next backup is a full database backup. This establishes a new differential base.

Before first database backup in this figure, the database is exposed to potential work loss (from time t0 to time t1). Thereafter, routine log backups reduce work-loss exposure to the risk of losing changes that were made after the latest log backup (taken at time t14 in this figure). If a failure occurs, the database administrator should immediately try to back up the active log (the tail of the log). If this tail-log backup succeeds, the database can be restored up to the point of failure.

By logging all operations, including bulk operations such as SELECT INTO, CREATE INDEX, and bulk loading data, the full recovery model lets you recover a database to the point of failure or to an earlier point in time, called a point-in-time restore.

Many users of the full recovery model temporarily switch to bulk-logged recovery model when bulk loading data and increasing performance outweighs the risk of possible data loss. The bulk-logged recovery model minimally logs bulk operations, though fully logging other transactions. For more information about bulk-logged recovery model, see Backup Under the Bulk-Logged Recovery Model

Note:

In SQL Server 2000 and later versions, using the select into/bulkcopy database option of sp_dboption resets the recovery model to BULK_LOGGED. In SQL Server 2000, this option is required to create a permanent table with SELECT INTO. However, in SQL Server 2005, this option is never required and should always be avoided. You should use ALTER DATABASE instead. This sp_dboption stored procedure will be removed in a future version of SQL Server.

Restoring a database requires a sequence of restore operations (a restore sequence). A restore sequence starts with restoring at least one full backup, optionally followed by a corresponding differential backup.

Each full and differential backup contains just enough log records to let you use them to recover the database. However, typically you will want to restore the subsequent log backups, in sequence, ending with the tail-log backup, if any. Therefore, before you start to restore a database, you must create a tail-log backup. The tail-log backup lets you restore the database up to the point of a failure. When the last log backup is restored, you must recover the database.

Note:
Under the full recovery model or bulk-logged recovery model, the SQL Server 2005 Enterprise Edition supports restoring files or pages, or both, while a database is online. This is known as an online restore. The RESTORE syntax for restoring the files or pages is the same whether the database is offline or online.

For more information, see Overview of Restore and Recovery in SQL Server.

Concepts

Other Resources

Help and Information