Backup Under the Full Recovery Model
The full recovery model uses log backups to prevent data loss in the broadest range of failure scenarios, and backing and restoring the transaction log (log backups) is required. The advantage of using log backups is that they let you to restore a database to any point of time that is contained within a log backup (point-in-time recovery). Assuming you can back up the active log after a disaster occurs, you can restore the database up to the point of failure without data loss. The disadvantages of using log backups are that they require storage space and increase restore time and complexity.
Note
If the benefits of using log backups do not justify the cost of managing the backups, we recommend that you use the simple recovery model.
For a database that regularly uses the full recovery model, you can optimize certain bulk operations by temporarily using the bulk-logged recovery model. The bulk-logged recovery model incurs several restrictions that make it not suited for day-to-day operations. For more information, see Backup Under the Bulk-Logged Recovery Model.
Sample Backup Strategy
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.
.gif "Restoring a full recovery model database")
Minimizing Work-Loss Exposure
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.
.gif "Full & differential database backups & log backups")
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.
Bulk Operations and the Full Recovery Model
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.
Using Backups to Restore a Database
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.
See Also
Concepts
Backup Devices
Using Marked Transactions (Full Recovery Model)
Overview of Restore and Recovery in SQL Server
Considerations for Switching from the Full or Bulk-Logged Recovery Model
Other Resources
Understanding and Managing Transaction Logs
Using Differential Backups
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
17 July 2006 |
|
5 December 2005 |
|