August 17, 2011 at 8:32 am
Was having a discussion with a colleage and just want to make sure I was telling him the correct informatino...
If I have a database which is in simple recovery mode and start a backup at 12pm... then while the backup is running, you perform some transactions- lets say at 12:30pm, then the backup completes at 1pm- that backup will include the 12:30pm transactions since "Each data backup includes part of the transaction log so that the backup can be recovered to the end of that backup. "
correct or incorrect?
August 17, 2011 at 8:36 am
correct - unless I've been getting it wrong all these years 🙂
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 17, 2011 at 8:58 am
The end of the backup process marks transactions as rollforward or rollback depending on if they're completed or not when the backup finishes. So you can get both situations, the ones where it captures those changes and the ones where it doesn't capture the changes because they weren't complete.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 17, 2011 at 9:17 am
gotcha- that makes sense... essentially I was trying to explain (enthusiastically) that even in simple mode, the transaction log is used during the full backup to track changes made since the backup started. My colleague was under the mistaken impression that a backup which starts at 12pm is essentially a snapshot of the database state at 12pm....
He is a good arguer- so I questioned myself for a moment... but I also reviewed this is the MS training kit for 70-432 which describes how this is accomplished:
to paraphrase ,it bascially says that sql server does the following:
"1.)locks the database blocking all transactions
2.)places a mark in the transaction log
3.)releases the lock from step 1
4.)extracts all data pages and writes to the backup device
5.)locks the database again
6.)places another mark in the transaction log
7.)releases the lock from step 5
8.) extracts the portion of the log between the marks and appends it to the backup.
"
August 17, 2011 at 10:04 am
It's consistent as of the end of the data-copying portion of the backup. Not necessarily the end of the backup, thought usually close to it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply