SQL Server Backups – When is it current?

I saw a post recently where someone was asking about the restore sequence for a series of backups. The scenario was this:

  1. Full backup starts at 3:00am, and takes 30 minutes
  2. Log backup 1 starts at 3:05am, and takes 2 minutes
  3. A second log backup starts at 3:35 and takes 2 minutes

What do you restore?

The short answer is that it doesn’t matter. If you use NORECOVERY (Always use NORECOVERY) and restore the logs in order, SQL Server will sort things out. If the transactions from backup 2 (the log backup from 3:05) are in the full backup, they won’t be applied twice and the system will let you know.

The same thing occurs for the second log backup. This is why SQL Server uses the Log Sequence Numbers. They ensure that SQL Server can track which transactions occurred when and in which order.

When is the full backup consistent?

If we are wondering when the full backup is complete, or at what point during your system’s life is the full backup going to return you to, it’s easy.

It is consistent as of the time when the data reading portion of the full backup is complete. We don’t necessarily know when that is, but at that point, the full backup will copy enough log records to get consistent to that point in time. If it’s a lot of log records, it’s possible that this is quite a bit of time before the backup completes and the timestamp goes on the backup file.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.