November 16, 2008 at 11:26 pm
Suppose my backup takes 3 hrs ... so the transaction during these 3 hrs are also backed up or not....
Regards,
[font="Verdana"]Sqlfrenzy[/font]
November 17, 2008 at 12:09 am
Maybe.
The database backup backed up data as it finds it on disk. Once it's done that, it backs up enough of the transaction log to ensure that the database can be restored in a consistent state.
Have a look at these two posts.
http://sqlskills.com/BLOGS/PAUL/post/More-on-how-much-transaction-log-a-full-backup-includes.aspx
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
November 17, 2008 at 11:20 am
Backups are upto date as of the minute they finish i.e. all the transactions that happen during the backup process are also backed up (if they are commited before the end of the backup process)
Roughly this is what happens:
1: Backup process puts a mark in the TRN log when the process starts
2: Starts backing up data [during this time, transactions continue to happen in the DB]
3: As it approaches the mark, it checks if there are any new committed transactions
4: If it sees any starts backing them up as well.
5: As it approaches the end, makes another mark in the transaction log and locks the database for a split second to finish backing up up to the latest committed transaction.
6: Releases lock and allows other transactions
This is not the complete list of steps... but just to give you an idea about what happens.
So backups are upto date as of the second it finishes
hope this helps,
_Ub
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply