Performing data update operations while backing up a large SQL Server database

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)?

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:

• 30 SQL Server backup myths

• Debunking a couple of myths around full backups

• Logging and recovery in SQL Server

StackExchange Database Administrators Q#18024, answer score: 8