Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
arameth
Starting Member
2 Posts |
 Posted - 2009-03-20 : 09:20:47
|
| Hi there,I have a question on how a change in data would be handled during a backup. Hopefully I'll provide enough info..so here is the scenario (assuming SQL 2005):1. Full backup for a DB starts at at 5pm.2. at 530pm, a user makes a change to the DB (deletes data)3. Full backup finishes at 6pm.So the simple question is: Does the backup of the DB reflect this change? Or since the change occured after the backup started, is it not captured? And to further expand, is there a difference if the backup was a differential? And what about a transaction log backup? We're trying to determine exposure of data loss during backups and need clarification. If there are any good articles or links on this topic, they'd be much appreciated.Thanks! |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-03-20 : 09:53:52
|
| Read Booksonline for backups.http://msdn.microsoft.com/en-us/library/ms186289.aspx |
 |
|
|
arameth
Starting Member
2 Posts |
Posted - 2009-03-20 : 10:46:55
|
| OK...that clears things up a bit. Thanks for the link!So to further clarify, SQL BOL states: "Full database backups represent the database at the time the backup finished."So if the change occured at 530pm, and DB backup finishes at 6PM, then the change should be captured. Does anyone have any more detailed or in depth info on the backup process? For example, using my earlier example, it's pretty neat that the backup could capture changes as lates as 559pm, given the backcup completes at 6pm...would be nice to know the mechanism that guarnantees this happens.Cheers. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-03-20 : 15:35:14
|
| Correct. |
|
|
|
ahmad.osama
Posting Yak Master
183 Posts |
Posted - 2009-03-21 : 04:57:55
|
quote:
Originally posted by arameth
OK...that clears things up a bit. Thanks for the link!So to further clarify, SQL BOL states: "Full database backups represent the database at the time the backup finished."So if the change occured at 530pm, and DB backup finishes at 6PM, then the change should be captured. Does anyone have any more detailed or in depth info on the backup process? For example, using my earlier example, it's pretty neat that the backup could capture changes as lates as 559pm, given the backcup completes at 6pm...would be nice to know the mechanism that guarnantees this happens.Cheers.
u can easily verify this practically on test enviromentRegards,Ahmad Osama |
|
|
|
cshah1
Constraint Violating Yak Guru
347 Posts |
Posted - 2009-03-21 : 20:28:36
|
| Arameth, to clarify >> it's pretty neat that the backup could capture changes as lates as 559pm, given the backcup completes at 6pm...CHECKPOINT occurs during the beginning and end of the backup.So if you have a transaction that commits before your backp ends it should be included in your backup. But for e.g. if you have a transaction that started at 5:59PM and did not commit before backup completed(last checkpoint) that transaction will not be in your backup. |
|
|
|
|
|
|
|
|
Backup question?