July 25, 2010 at 2:40 am
hi,
i want to know the process of a full backup
acorrding to update/delete/insert raws during
the backup example :
1 )backup start
2) new record insert/updated/deleted/
3) sql will backup the new record?
i want to understand the logic .
thanks sharon ofer
July 25, 2010 at 3:13 am
Any FULL backup operation will complete in such a way that when it is restored the Database will be in a consistent state.
This article by Paul Randal explains it better.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
July 25, 2010 at 6:05 pm
Paul Randal is quite good at explaining stuff, but that article may be a little intimidating - but well worth the read.
In short, because the full backup process backs up the database over time (consider a 100 GB database over 20 minutes), and data can change ANYWHERE in the file at any time, whether that portion has been backed up or not. So, at the end of the backup, the active portion of the transaction log is included. This means that any operation that occurred during the backup can be replayed, to bring the database back to a consistent state - consistent as of the time the backup completed.
So, to answer your question, your rows that were inserted/updated/deleted MAY be in the backed up data (if they occurred before those pages were written to the backup file), and they will definitely be in the portion of the transaction log back up either way. As long as the transaction was committed prior to the backup finishing, they will be included in the full backup.
July 25, 2010 at 6:51 pm
Jim has a good explanation. In short
a) backup started
b) transaction committed (insert/update/delete)
c) backup complete.
Anything that happens before the backup is complete is included.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply