Tail-Log Backup

A tail-log backup captures any log records that have not yet been backed up (the tail of the log) to prevent work loss and to keep the log chain intact. Before you can recover a SQL Server database to its latest point in time, you must back up the tail of its transaction log. The tail-log backup will be the last backup of interest in the recovery plan for the database.

Not all restore scenarios require a tail-log backup. You do not need a tail-log backup if the recovery point is contained in an earlier log backup. Also, a tail-log backup is unnecessary if you are moving or replacing (overwriting) a database and do not need to restore it to a point of time after its most recent backup.

--restoring into an existing database with replace, and tail-log

BACKUP LOG [AdventureWorks2012] TO  DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS2012_2\MSSQL\Backup\Fullbkp_test_log.bak'

WITH NOFORMAT, NOINIT,  NAME = N'Fullbkp_test_log', NOSKIP, NOREWIND, NOUNLOAD, STATS = 5

RESTORE DATABASE [TestDB] FROM  DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS2012_2\MSSQL\Backup\Fullbkp_test.bak'

WITH  FILE = 1,  MOVE N'AdventureWorks2012_Data' TO N'D:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS2012_2\MSSQL\DATA\TestDB.mdf', 

MOVE N'AdventureWorks2012_Log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS2012_2\MSSQL\DATA\TestDB_Log.ldf',  NOUNLOAD, REPLACE, STATS = 5

Scenarios That Require a Tail-Log Backup

We recommend that you take a tail-log backup in the following scenarios:

·         If the database is online and you plan to perform a restore operation on the database, begin by backing up the tail of the log. To avoid an error for an online database, you must use the … WITH NORECOVERY option of the BACKUP Transact-SQL statement.

·         If a database is offline and fails to start and you need to restore the database, first back up the tail of the log. Because no transactions can occur at this time, using the WITH NORECOVERY is optional.

·         If a database is damaged, try to take a tail-log backup by using the WITH CONTINUE_AFTER_ERROR option of the BACKUP statement. On a damaged database backing up the tail of the log can succeed 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. If a tail-log backup cannot be created, any transactions committed after the latest log backup are lost.