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