patternsqlMinor
Transaction log in the full backup
Viewed 0 times
fullthelogtransactionbackup
Problem
I have a little misunderstanding when I make SQL Server full database backup. How does exactly full backup work? What data does it backup?
Here is a case: I scheduled full database backup supposing at 12.00 it takes about 15 minutes, so it finishes at 12.15, But at 11.58 one transaction has begun and finished at 12.05.
So my question is: Will my full backup contain this transaction or now? And why?
Here is a case: I scheduled full database backup supposing at 12.00 it takes about 15 minutes, so it finishes at 12.15, But at 11.58 one transaction has begun and finished at 12.05.
So my question is: Will my full backup contain this transaction or now? And why?
Solution
The simple answer is yes, it will contain the transaction. Also simply the backup contains anything that happens before the end of the backup, not as of the start. The full backup actually contains the transaction log information as of the completion of the backup. When you do your restore any transactions that were not committed at that time get rolled back.
If you want to learn more about it you can research backup internals.
Here is a technet article on it and the Kin's answer to this question has a number of additional good links.
If you want to learn more about it you can research backup internals.
Here is a technet article on it and the Kin's answer to this question has a number of additional good links.
Context
StackExchange Database Administrators Q#130409, answer score: 8
Revisions (0)
No revisions yet.