October 28, 2004 at 7:21 am
INSERT, UPDATE, or DELETE statements are allowed during a backup operation.
During a long running backup if updates to a table occur, what is reflected in the backup? Does it have the old row values or the new values. How is INSERT and DELETE handle in the backup?
David Bird
October 28, 2004 at 9:02 am
Well, for sure the backup will contain the new rows if the trxs have been committed by the time the backup ends.
According to Inside SQL2K by Kalen Dalaney, a full db backup stores the state of the database at the time the backup ends, not when the backup starts. No details on how this is done, best guess is that when the backup starts, all new data are only written to the log, not to the data file because the server will be busy dumping the data file contents.
Once this is completed, perhaps the server then dumps out all the data in the trx logs to the backup (incl. uncommitted trxs, which are rolled back if the database is subsequently restored with the recovery option). This is only a guess, so somebody correct me if I'm wrong.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
October 28, 2004 at 10:00 am
I believe the scenario is that during a full backup first the .MDF file is backed up and then at the end of a full backup a log backup is appended to the end. So a full backup contains a "snapshot" of the database at the start of the backup but then all transactions that occur during the backup are recorded and appended to the end of the backup file.
This means that during a full restore, you are actually doing a database restore followed by a restore of the appended log backup at the end.
This is my educated guess about what is going on but I have never seen this in writing. Can anyone comment/confirm this scenario.
Jason Hall
October 29, 2004 at 8:37 am
Jason, that is my understanding. When the log file is restored, the comitted transactions a rolled forward (reading the log file) and the uncomitted are rolled back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply