Why Backup the Tail?

When you are migrating a database from one server to another, how can you be sure to backup all transactions? Sure, you can notify the client and let them know “there will be a short outage at 8AM so please stay out of the application at that time.” Can you really trust that? Of course not. Let’s demonstrate the steps needed to include all transactions with the tail-log backup.

If You’re Not First, You’re Last

In our scenario, we’re waiting until the top of the hour to take a transaction log backup and restore it to a new server where the database will reside. We’ll simulate the client trying to finish up their work right before the transaction log backup is taken.

SQLQueryStress will be out application and we’ll start the following:

In SSMS we’ll kick off our scripted out backup script:

BACKUP LOG [ExampleDB] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\ExampleDB.log'
WITH NOFORMAT, NOINIT,  NAME = N'ExampleDB-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Our transaction log backup completed right away while the queries being executed continue to run. If we restore this transaction log backup, we’re going to be missing “LastInsert” because it had not happened yet at the time of the backup. How do we make sure the client doesn’t get frustrated when “LastInsert” doesn’t show up after the migration?

By the Tail

Let’s tweak our backup script. When using the GUI in SSMS and the backup type is transaction log, you can see the following option in Media Options:

What we need is to backup the tail of the log. Let’s select that button, script out our query to a new window, and see how our script changes:

BACKUP LOG [ExampleDB] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\ExampleDBTail.log'
WITH  NO_TRUNCATE , NOFORMAT, NOINIT,
NAME = N'ExampleDB-Full Database Backup', SKIP, NOREWIND, 
NOUNLOAD,  NORECOVERY ,  STATS = 10
GO

We now see NO_TRUNCATE as well as NORECOVERY. We’ll run our example again and see the difference when this backup is performed. If we start the INSERT and start our backup:

Note the elapsed time. Our backup waits until it can have exclusive access and then completes:

Since we set NORECOVERY, our database will also be in a “Restoring…” state:

No new changes can sneak their way into the database. If we restore the tail-log backup on the new server, we’ll see data from both inserts instead of only one:

Thanks for reading!