SQL 2005 Log - To Full Backup Gap

Question

SQL 2005 Log - To Full Backup Gap

rickK_

Mr or Mrs. 500

Points: 500
More actions
August 11, 2010 at 9:03 am

#225283

I am trying to understand how the gap between the last log backup prior to a full backup and the next log after the full backup relate.
Example (fictitious)
If i backup the log at 5pm, then do a full at 9pm, then backup the log again again at 11pm, how does SQL handle the period of 5pm until the last 11pm log backup? Basically, what happens to the data in the log prior to the full backup. Does SQL know this data is prior to Full backup when restoring the 11pm log, ignoring data in the log before 9pm full backup.
Thanks for the help

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 1

There is no need to worry about the 5pm log backup if you just took a Full backup later. You will have all the data that you need in the Full, and you will have broken the log chain. You would not want to go back in time like that.

At the Full Backup at 9pm, you can delete the 5pm log backup. It would be useless, unless you have a Full Backup that was taken prior to the 5pm log backup. You could technically use the Full and the 5pm log backup, even after you take a Full backup at 9pm and any log backup after 9pm. But why would you want too. You already now have a complete Full backup with everything.

I suggest that you read the SQL Books Online about backup strategy.

Andrew SQLDBA

rickK_ Mr or Mrs. 500 Points: 500 More actions · Answer 2

My concern is with restoring the 11pm log backup after the full backup at 9pm. If would contain log data prior to the 9pm full backup.

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 3

That does not even make sense. I think that you have been mis-guided about what the Transaction Log is and does.

Read the Books Online about the Transaction Log.

Andrew SQLDBA

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

rickK_ (8/11/2010)
My concern is with restoring the 11pm log backup after the full backup at 9pm. If would contain log data prior to the 9pm full backup.

Yes, it would. It'll contain log records from 5pm to 11pm. Full backups don't truncate the transaction log.

Why is this a concern?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rickK_ Mr or Mrs. 500 Points: 500 More actions · Answer 5

Will sql ignore the data changes prior to the full backup after the previous log backup, that were since the last truncation, and only restore those from the full backup to the 11 pm period?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

If you restore the full backup, then apply the log backup SQL will use the LSNs (Log Sequence numbers) of the full backup and the transactions in the log backup to roll forward only those transactions that were not included in the full backup.

Full explanation is a bit more complex. If you want, I'll go into more detail (or find a blog post explaining) when I get home.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

AndrewSQLDBA (8/11/2010)
There is no need to worry about the 5pm log backup if you just took a Full backup later. You will have all the data that you need in the Full, and you will have broken the log chain.

Broken the log chain? How?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rickK_ Mr or Mrs. 500 Points: 500 More actions · Answer 8

So it will ignore the LSNs previous to the full backup when restoring the tran log?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

If you are restoring the 9pm full backup, then the 11pm log backup, yes.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rickK_ Mr or Mrs. 500 Points: 500 More actions · Answer 10

rickK_

Mr or Mrs. 500

Points: 500

August 11, 2010 at 10:26 am

#1205512

Thanks for the help. You answered my question.