November 12, 2009 at 2:56 pm
My understanding is that a backup started after a table in the target database begins a DML operation (DELETE/INSERT/UPDATE) will wait for that operation to complete before backing up that particular table's data.
Assuming that's an accurate stmt (but pls correct me if otherwise), I would appreciate help in understanding what happens when the reverse situation occurs and a backup (full backup in my case) starts and before it completes one or more DML operations is/are run against the same d/b?
Will the results of the DML operation be excluded or included in the backup or is that a "maybe" based on other factors?
Thanks in advance!
Chris.
November 13, 2009 at 12:57 am
No, the backup won't wait for DML actions to complete.
Assuming you're talking about full backups, your backup will reflect the state of the database at the end of the backup.
When you come to restore (assuming you don't leave the database in NORECOVERY, waiting for transaction log and/or differentials to be applied as well), SQL Server will leave your database in a transactionally consistent state. Any transactions that had been committed by the end of the backup will be present in your restored database. Any transactions that had not been committed will be rolled back, and your restored database will be as if those transactions had never been started at all.
The same applies for transaction log backups... once you restore the final log and bring the database into RECOVERED, it will be left transactionally consistent in exactly the same way.
November 13, 2009 at 8:34 am
Thanks...I appreciate the response and explanation.
November 13, 2009 at 8:49 am
Ian is correct. What happens is that any log records written during the backup are included at the end of the backup file. That way changes to pages are reflected in the rollback/rollforward that occurs when a database starts.
November 13, 2009 at 9:24 am
I may be oversimplifying a bit (okay a lot) but after the backup has finished reading data it circles back to the log and looks for any transactions committed that may have occurred while the backup process was "in-flight" and includes those log entries in the backup such that the backup is then considered transactionally consistent? And any transactions that were not committed before the backup completes are excluded from the backup?
Thanks again,
Chris.
November 13, 2009 at 11:07 am
That's roughly what happens. I think it's transactions that affect pages written to the backup, meaning changed while the backup is in process.
November 13, 2009 at 1:56 pm
I may be oversimplifying a bit (okay a lot) but after the backup has finished reading data it circles back to the log and looks for any transactions committed that may have occurred while the backup process was "in-flight" and includes those log entries in the backup such that the backup is then considered transactionally consistent? And any transactions that were not committed before the backup completes are excluded from the backup?
Yes and No.
Yes, at the end of the full backup, it does append any additional changes from the transaction log to the end of the full backup.
No, this phase of the backup includes everything that's been written to the transaction log since your full backup started (committed and uncommitted transactions), right up to the point the full backup completes.
When you restore your full backup, after the database itself has been restored, the next phase then applies (rolls forward) those transaction log changes that were appended to the full backup to the database itself (called the fixup phase). That phase includes any changes made by both committed and uncommitted transactions. As long as you don't specify WITH RECOVERY, that's the end of the process.
That allows you to restore further transaction log backups. The process of restoring log backups is a similar process, but the first phase reconstitutes the transaction log, rather than the database, before going through the fixup phase to roll the changes forward.
When you finally specify WITH RECOVERY, the rollback phase is carried out, using the transaction log to undo any uncommitted changes already applied to the database. It's not until this point that the database is transactionally consistent, which is why you can't read from a database when it's in NO RECOVERY (it could contain uncommitted changes).
Specifying WITH STANDBY instead of NO RECOVERY actually goes through the rollback phase, leaving it transactionally consistent, but read only, and able to restore further log backups.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply