April 1, 2012 at 5:37 am
Hi,
I have the below scenario. Can you please suggest with your update?
1. There is a database backup job running and the backup is completed <50% of the total database. Suddenly some of the dev team members have inserted 1 million records in the table and the transaction is there in the Tlog file.
Question: Will this backup job will consider taking this in the full backup?
2. If the backup is completed around 80%, will the full database backup will consider this insertion operation details, where this backup job was started before the data were inserted in the tlog file.
Please respond with a logic per the architecture.
Thanks.
April 1, 2012 at 6:27 am
If the transaction completes during the database backup process, it will be included in the backup. Data that is changed during the backup process is logged at the end of the process. Open transactions will be rolled back and completed transactions will be rolled forward, all during the final RECOVERY process of the database restore operation.
"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
April 1, 2012 at 9:04 am
If the transaction commits before the data-reading portion of the full backup finishes, the transaction will be included in the backup and present in the restored DB. Otherwise not.
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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply