SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


restore db from multiple diff vs last diff


restore db from multiple diff vs last diff

Author
Message
Thanh Ngay Nguyen
Thanh Ngay Nguyen
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 480
which option (below) will the restoring the 2nd (last) diff be faster?
option 1: restore full (10TB), 1st diff (3TB), 2nd (last) diff 5TB
option 2: restore full (10TB), 2nd (last) diff (5TB)

With option 1: I already tried this and it know it worked. I'm wondering behind the scene: when restoring the last diff, does sql engine know where it left off, so it only applying 2TB (5TB - 3TB) changes, instead of applying the whole 5TB and overwrite the 1st diff?

This is useful to know when I have a huge db and want to migrate to new server... and I want to start the restore (w/ norecovery) ahead of time

Thanks,
Thanh
GilaMonster
GilaMonster
SSC Guru
SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)

Group: General Forum Members
Points: 225207 Visits: 46321
You only ever need to restore the last differential backup. They're differentials, not incrementals. Restore the full backup, the last differential then, if you're restoring log backups as well, all log backups since that last diff

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


GSquared
GSquared
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57947 Visits: 9730
Unless you need to restore to an earlier state (point-in-time restore), the last diff is the only one you need.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Thanh Ngay Nguyen
Thanh Ngay Nguyen
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 480
Thanks for the replies.
My question was in which option would my 2nd-diff restore finish faster? Or they would take the same amount of time to restore the 2nd-diff?

For example: My full back taken Sunday 10TB, Wed 1st diff 3TB, Friday 2nd diff 5TB. Saturday afternoon is the migration day. Just make things simpler, just leave TL out of the picture for now. My key point here is the time it would take to restore the 2nd diff. So which option would make my 2nd-diff restore complete faster in term of duration?

Thanks,
GilaMonster
GilaMonster
SSC Guru
SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)

Group: General Forum Members
Points: 225207 Visits: 46321
Let me put it this way:

A differential backup contains all the changes since the last full backup. It has no reference, checks, no knowledge of what changed since a previous differential backup. When you restore a differential backup, that differential is restored in its entirety because there is no other possible option.

Hence, first option:
Restore full backup, restore 3TB of differential, then restore 5 TB of differential
Second option:
Restore full backup, restore 5 TB of differential

If you want incremental restores, you want log backups. They are incremental, each one contains changes since the last log backup.

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


Thanh Ngay Nguyen
Thanh Ngay Nguyen
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 480
Thanks Gail.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search