SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.

Filed under: Blog Tagged: Backup/Recovery, disaster recovery, sql server, syndicated

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


No comments.

Leave a Comment

Please register or log in to leave a comment.