Tail-Log Backups
This topic is relevant only for databases
that are using the full or bulk-logged recovery models.
In most cases, under the full or bulk-logged
recovery models, SQL Server 2005 and later versions require that you
back up the tail of the log to capture the log records that have not
yet been backed up. A log backup taken of the tail of the log just
before a restore operation is called a tail-log backup.
SQL Server 2005 and later versions usually
require that you take a tail-log backup before you start to restore a
database. The tail-log backup prevents work loss and keeps the log
chain intact. When you are recovering a database to the point of a
failure, the tail-log backup is the last backup of interest in the
recovery plan. If you cannot back up the tail of the log, you can
recover a database only to the end of the last backup that was
created before the failure.
Not all restore scenarios require a tail-log
backup. You do not have to have a tail-log backup if the recovery
point is contained in an earlier log backup, or if you are moving or
replacing (overwriting) the database and do not need to restore it to
a point of time after the most recent backup. Also, if the log files
are damaged and a tail-log backup cannot be created, you must restore
the database without using a tail-log backup. Any transactions
committed after the latest log backup are lost. For more information,
see "Restoring Without Using a Tail-Log Backup" later in
this topic.
Backing Up the Tail of the Log
Like any log backup, a tail-log backup is
taken by using the BACKUP LOG statement. We recommend that you take a
tail-log backup in the following situations:
If the database is online and you plan to
perform a restore operation on the database, before starting the
restore operation, back up the tail of the log using WITH NORECOVERY:
BACKUP LOG database_name TO <backup_device>
WITH NORECOVERY
Note
To avoid an error, the NORECOVERY option is
necessary.
If the database is offline and does not
start.
Try to take a tail-log backup. Because no
transactions can occur at this time, using WITH NORECOVERY is
optional. If the database is damaged, use WITH CONTINUE_AFTER_ERROR,
as follows:
BACKUP LOG database_name TO <backup_device>
WITH CONTINUE_AFTER_ERROR
If the database is damaged, for example, if
the database does not start, a tail-log backup succeeds only if the
log files are undamaged, the database is in a state that supports
tail-log backups, and the database does not contain any bulk-logged
changes.
The following table summarizes these
options.
BACKUP LOG option
Comments
NORECOVERY
Use NORECOVERY whenever you intend to
continue with a restore operation on the database. NORECOVERY takes
the database into the restoring state. This guarantees that the
database does not change after the tail-log backup.
The log is truncated unless the NO_TRUNCATE
option or COPY_ONLY option is also specified.
Important
We recommend that you avoid using
NO_TRUNCATE, except when the database is damaged.
CONTINUE_AFTER_ERROR
Use CONTINUE_AFTER_ERROR only if you are
backing up the tail of a damaged database.
Note
When you use back up the tail of the log on
a damaged database, some of the metadata ordinarily captured in log
backups might be unavailable. For more information, see "Tail-Log
Backups with Incomplete Backup Metadata," later in this topic.
To create a transaction log backup when the
database is damaged
How to: Back Up the Transaction Log When the
Database Is Damaged (Transact-SQL)
How to: Back Up the Tail of the Transaction
Log (SQL Server Management Studio)
Tail-Log Backups That Have Incomplete
Backup Metadata
Tail log backups capture the tail of the log
even if the database is offline, damaged, or missing data files. This
might cause incomplete metadata from the restore information commands
and msdb. However, only the metadata is incomplete; the captured log
is complete and usable.
If a tail-log backup has incomplete
metadata, in the backupset table, has_incomplete_metadata is set to
1. Also, in the output of RESTORE HEADERONLY, HasIncompleteMetadata
is set to 1.
If the metadata in a tail-log backup is
incomplete, the backupfilegroup table will be missing most of the
information about filegroups at the time of the tail-log backup. Most
of the backupfilegroup table columns are NULL; the only meaningful
columns are as follows:
backup_set_id
filegroup_id
type
type_desc
is_readonly
Restoring Without Using a Tail-Log Backup
Restore scenarios in which a tail-log backup
is unnecessary include the following:
Restoring a database to a point in time that
is contained in a previous log backup.
A tail-log backup is unnecessary if you
restore a database and specify the STOPAT, STOPATMARK, or
STOPBEFOREMARK option in every RESTORE statement in your restore
sequence.
To restore a database to an earlier point in
time
To use Transact-SQL to restore to a specific
point in time, see How to: Restore to a Point in Time (Transact-SQL),
Recovering to a Marked Transaction, or Recovering to a Log Sequence
Number (LSN).
To use SQL Server Management Studio, see How
to: Restore to a Point in Time (SQL Server Management Studio) or How
to: Restore a Database to a Marked Transaction (SQL Server Management
Studio).
Restoring a copy of a database to a new
location.
When you restore a database, you can use the
same database name only if you restore the database to a different
server instance, such as when you create a mirror database for
database mirroring or a secondary database for log shipping. If you
move a database on the same server instance, you must specify a new
name for the database.
To restore a database to a new location
Using Transact-SQL, in every RESTORE
statement in your restore sequence, specify the MOVE option. For more
information, see How to: Restore a Database to a New Location and
Name (Transact-SQL) or How to: Restore Files to a New Location
(Transact-SQL).
Using SQL Server Management Studio, specify
the new location of each file in the Restore As field on the Restore
Database (Options Page). For more information, see How to: Restore a
Database Backup (SQL Server Management Studio).
Completely replacing (overwriting) the
database.
Caution
Restoring with the REPLACE option should be
used rarely and only by experienced database administrators, after
careful consideration. For more information, see Using the REPLACE
Option.
To replace a database
Using Transact-SQL, specify the REPLACE
option in your RESTORE statements.
Using SQL Server Management Studio, specify
the new location of each file in the Restore As field on the Restore
Database (Options Page). For more information, see How to: Restore a
Database Backup (SQL Server Management Studio).
Comments