When you migrate a database, it can be useful to prove that you moved all the data and didn’t miss any transactions. Learn how to use a tail log backup in a migration scenario.

Watch this week’s 13 minute video. Subscribe to my YouTube channel, or check out the audio podcast to listen anywhere, anytime.

When you restore a full backup, does it restore to when you started the backup job— or when it completed?

In this episode, I give you the super-short answer. (Spoiler: a point near the end of when the backup was running.) For the full answer, complete with a detailed timeline to help you understand the nitty gritty, read “Understanding SQL Server Backups” by Paul Randal

Tail log backups

Here’s the Book’s Online entry for Tail Log backups. It mostly talks about using this in times of trouble (damaged databases), it also confirms…

NORECOVERY takes the database into the restoring state. This guarantees that the database does not change after the tail-log backup.

And that’s the really useful bit if you want your migration steps to clearly ensure that no changes can be committed after you grab this last log backup.