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 restore a database to any point of time that is contained within a log backup (point-in-time recovery). 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. Be aware that to minimize your restore time, you can supplement each full backup with a series of differential backups of the same data.
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 full 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")
For more information, see Full Database Backups and Working with Transaction Log Backups.
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.
The following illustration shows a backup strategy that supplements full database backups and log backups with differential database backups. The transaction log backups reduce potential work-loss exposure to the time after the most recent log backup, t14. A series of three differential backups is taken to reduce the number of transaction logs that would need to be restored in the event of a failure. 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). In the event of a failure after the most recent backup, the database administrator would try to back up the tail of the log (the log that is not yet backed up). If the tail-log backup succeeds, the database administrator could avoid any work loss by restoring the database up to the point of failure.
For information about differential database backups, see Using Differential Backups.
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 2005 and later versions, the select into/bulkcopy database option of sp_dboption 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 and later versions support 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 Restore and Recovery Overview (SQL Server).
See Also