October 8, 2010 at 4:02 am
Hi,
when testing log shipping I wondered if a full backup of the primary would break the log shipping as the backup chain would then be broken as the next transaction log backup would be expecting the LSN of the full backup taken.
I tested this and my log shipping did not break. So this led me onto thinking about my understanding of the backup chain where upon I found this thread:
http://www.sqlservercentral.com/Forums/Topic986932-20-1.aspx#bm987186
My question is around backup chains. I had always though that you had to restore transaction logs to the last full backup in order to keep the chain in tact. But if I am understanding this correctly now, then that doesn't have to be the case. You could restore transaction logs to a database from last month if you required (as long as you have all the transaction logs from that long ago) and the only reason why we use the last full backup is to save time on the restores (Assuming a nightly full backup strategy). Is this correct?
Further to this my understanding of Copy_Only backups has been destroyed as I had thought it was used as a way to not break the backup chain in terms of restoration. I am not sure what resetting the base differential actually means.
Thanks
October 8, 2010 at 4:20 am
Kwisatz78 (10/8/2010)
My question is around backup chains. I had always though that you had to restore transaction logs to the last full backup in order to keep the chain in tact. But if I am understanding this correctly now, then that doesn't have to be the case. You could restore transaction logs to a database from last month if you required (as long as you have all the transaction logs from that long ago) and the only reason why we use the last full backup is to save time on the restores (Assuming a nightly full backup strategy). Is this correct?
Yup. As long as you have a full backup and an unbroken log chain, you can restore
Further to this my understanding of Copy_Only backups has been destroyed as I had thought it was used as a way to not break the backup chain in terms of restoration. I am not sure what resetting the base differential actually means.
The differential base refers to which full backup a differential is based on. A differential backup is based on the last full backup taken on the server. The copy only is a way to take a full backup so that it doesn't change which full backup further differential; backups are based on.
Eg:
Full backup 1
Diff backup 1
Diff backup 2
Full backup 2
Diff backup 3
Diff backup 4
With that sequence of backups, diff 1 and diff 2 are based on full backup 1 and diff 3 and 4 are based on full backup 2. Restore Full backup 1 and then diff backup 4 and you'll get an error
Full backup 1
Diff backup 1
Diff backup 2
Full backup 2 WITH COPY_ONLY
Diff backup 3
Diff backup 4
In this case all the differentials are based on Full backup 1. Restore Full backup 1 and then diff backup 4 and it will work
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
October 8, 2010 at 4:40 am
Top explanation again Gail thankyou 🙂
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply