How to Perform Tail Log Backup During SQL Server Restore

restoresql servertransaction-log

Typically when doing a restore of a DB from a production server to a non-production server I will use the WITH REPLACE option as when I forget I get an error about the tail log not being backed up.

According to MSDN I should indeed be backing up my tail log before restoring:

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.

What are some dangers or disadvantages of the way I'm doing it? Why is backing up the tail log first of advantage to me?

I'm using SQL Server 2008R2 but I'm presuming this query will be relevant to most newer versions of SQL Server also so have not tagged it as such initially.

Best Answer

If you don't back up the tail of the log you lose any transactions that ocurred since you last backed up the database.

Related Solutions

Sql-server – SQL Server tail log backup fails where it shouldn’t

Paul Randal did a good piece on backing up the tail of the log that includes just this case. In it he says that the method to use when there is no data file is using NO_TRUNCATE. Based on that your command should be:

BACKUP LOG [AdventureWorks2012] TO
DISK = N'C:\sqldata\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012.bak' 
WITH NAME = N'AW Tail Log Backup', NO_TRUNCATE

BOL does say

To perform a best-effort log backup that skips log truncation and then take the database into the RESTORING state atomically, use the NO_TRUNCATE and NORECOVERY options together.

So you could try using both of them together but I'm not sure if NORECOVERY can be used if the data file is missing or not, even with NO_RECOVERY.

BACKUP LOG [AdventureWorks2012] TO
DISK = N'C:\sqldata\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012.bak' 
WITH NAME = N'AW Tail Log Backup', NO_TRUNCATE, NORECOVERY

It's certainly worth testing anyway.

Sql-server – Necessity of backing up tail log when restoring transaction log

At the time of this restore, is it necessary to back the tail log up if we have no need for all the data entered following the final transaction log?

No. If your transaction log backup ran at the top of the hour as expected, and you are certain you don't need any data later than that point, it is not necessary to take a tail of the log backup.

Best Answer

Related Solutions

Sql-server – SQL Server tail log backup fails where it shouldn’t

Sql-server – Necessity of backing up tail log when restoring transaction log

Related Question