April 9, 2003 at 1:41 pm
How does SQL Server determine when to end a full backup when continuous transactions occur and are recorded in the Transaction log.
April 9, 2003 at 2:15 pm
When SQL starts a full backup it checks wich is the LSN (Log Sequence Number), and backup all the datafiles pages plus the transactions pending to be saved into the datafiles up to the LSN. The rest of the transactions in the Log, that will execute during the full backup, will enter into the next log backup or full backup.
April 9, 2003 at 2:46 pm
Okayyyyy, That's what I also thought....
I did a couple of tests. I hope my example is good enough. I ran a test where I retrieved the max(id) of a table. I started a procedure that increments the row ID by 1 over a period of time overlapping a full database backup. Stopped the procedure and then checked the MAX(ID) again. I then restored the backup to another database and looked at the max(ID) then. Interesting results.....
-- 2225320 max(ID) @ before start of test
-- 2234056 max(ID) @ at run procedure time
-- 2234392 max(ID) @ another check
-- 2235400 max(ID) @ start of backup
-- 2236408 max(ID) @ 50% of backup done
-- 2237080 max(ID) @ end of database backup
-- 2237416 max(ID) @ end of procedure
-- 2236744 max(ID) in restored database
Edited by - despag on 04/10/2003 11:56:06 AM
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply