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.
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.
Have a question about SQL Server that you’ve always been afraid to ask?