Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a large (in the tens of millions of records) database that I am going to perform a full database backup on.

However, the database is large enough that transactions can start before and during, as well as commit during and after the backup takes place.

For example:

T0 = Transaction A start
T1 = Full database backup start
T2 = Transaction B start (will not deadlock with A)
T3 = Transaction A commit/rollback (does not matter, does it?)
T4 = Full database backup end
T5 = Transaction B commit/rollback (again, does not matter, does it?)

T0          T1          T2          T3          T4          T6

My understanding is that no locks are used during a backup (although other performance problems may arise due to say, high I/O), but I'm not sure what I can guarantee what will be committed or not.

Also, my concern isn't that the database will be in an inconsistent state, but rather, what that state will be (even if it's not deterministic, if there's a set of rules that can be consistently applied) and how it got there (for example, how much of the data file is used along with the transaction log to create a backup file)?

share|improve this question
I removed my first comment and expanded it into an answer. I'm still trying to find out how to find exactly when the data read of a backup finished. –  Simon Righarts May 17 '12 at 0:47

2 Answers 2

up vote 3 down vote accepted

Essentially, the backup will be of the state of the database when it finishes the data-reading portion of the backup (so all of the data will be backed up), plus whatever amount of transaction log is required to ensure transactional consistency (the start time of the included log is MIN(most recent checkpoint time, oldest active transaction start time)). Paul Randal covers this here (with aid of a diagram, which makes it all so much easier). In your example, A would be committed (or rolled back if a ROLLBACK TRANSACTION was issued instead of a COMMIT) and B would be rolled back (regardless of the end result of that transaction).

(The other reason you try and do backups at a quiet time, aside from I/O contention, is that all of the transaction log generated during a backup normally has to be included with the backup.)

The recovery phase of a database restore takes all the committed transactions from the log included in the backup and applies them to the database, and rolls back all the un-committed transactions. (This is why WITH RECOVERY/WITH NORECOVERY is important. WITH RECOVERY and you can use the database, but you can't apply any further log backups, you need to restore it WITH NORECOVERY in order to roll in log backups. Recovery breaks the log chain by rolling back uncommitted transactions.)

Further reading:

share|improve this answer

A full backup will restore to the point in time when the data-reading portion of the backup completes, minus any uncommitted transactions at that time.

As you mentioned, the database stays online and available for writing while the backup occurs. How this works is the backup system backs up an inconsistent set of data pages (since it takes a non-zero amount of time to read the data) -- it takes a copy of each data page as it comes to it, whatever state it's in. A full backup also includes transaction log records starting from the start of the oldest active transaction at the beginning of the backup, to the last log record when the data-reading portion completes.

When the backup is restored, the data and log are reconstituted as-is (remember the data pages are in an inconsistent state), then the redo process occurs from the beginning of the backed-up transaction log (again, inside the full backup), all the way to the end; then, if you restored with RECOVERY, undo occurs to roll back any uncommitted transactions at the time the backup finished. The undo operation is what leaves the database in a transactionally-consistent state, ready for use. Restoring with NORECOVERY skips the undo process, allowing you to restore additional backups (differential or transaction log).

Note that if the database is quite large with a heavy write workload, the transaction log may need to grow during the backup if there is insufficient space currently allocated. The log cannot (internally) clear during a full backup, even if you're taking transaction log backups, as the log records are required for the full backup. If you are taking transaction log backups while a full backup occurs, log clearing is automagically deferred until the full backup has completed.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.