HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Transaction log in the full backup

Submitted by: @import:stackexchange-dba··
0
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?

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.

Context

StackExchange Database Administrators Q#130409, answer score: 8

Revisions (0)

No revisions yet.