Database Reference
In-Depth Information
■
a full database backup does not truncate the transaction log in the FuLL or BuLk-LOggeD recovery
models. you should perform log backup to truncate the transaction log.
Important
if log backup were running at the same time as a full database backup, log truncation would be deferred until the full
backup completes.
For a database in the BULK-LOGGED recovery model, log backups also store the data from extents that were
allocated during minimally logged bulk-copy operations, such as
CREATE INDEX
,
ALTER INDEX REBUILD
,
BULK INSERT
,
INSERT INTO
,
INSERT SELECT
, and few others. Those extents are tracked with another set of allocation map pages
called
Bulk Changed Map (BCM)
. SQL Server must be able to access data files with those extents for the log backup
to succeed.
In contrast to differential backups, log backups are incremental. Each subsequent log backup stores the portion
of the transaction log starting at the point where the previous log backup finished. You must apply all log backups
one-by-one during restore process.
The sequence of log backups contains log records for all operations performed by SQL Server since last full
backup. This allows you to redo the work and recover the database, even when database files are corrupted or
unavailable. Moreover, it supports point-in-time recovery and allows you to recover the database up to a particular
time. One case when this is beneficial is upon the accidental deletion of the data or a database object. We will talk
about such a situation later in the chapter.
■
the database in the BuLk-LOggeD recovery model does not support point-in-time recovery if the transaction
log contains bulk-logged operations running at the same time.
Note
The special kind of log backup, called
tail-log backup
, is used when you need to recover a database after a
disaster. It backs up log records that have not been backed up since the last log backup, and it prevents potential data
loss during recovery. We will talk about tail-log backups in detail later in this chapter.
A continuous sequence of log backups is called a
log chain
. A log chain starts with a full database backup, and it is
required to restore database up to the point of failure and/or a point in time.
Figure
30-1
shows an example of a log chain and a tail-log backup.
Figure 30-1.
Log chain and tail-log backup
