Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

restore db from multiple diff vs last diff Expand / Collapse
Author
Message
Posted Thursday, December 13, 2012 10:37 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, October 18, 2014 11:53 AM
Points: 18, Visits: 418
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
Post #1396296
Posted Thursday, December 13, 2012 11:03 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:50 AM
Points: 40,432, Visits: 36,886
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 2008, MVP
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

Post #1396313
Posted Thursday, December 13, 2012 11:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
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
Post #1396326
Posted Thursday, December 13, 2012 12:09 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, October 18, 2014 11:53 AM
Points: 18, Visits: 418
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,
Post #1396338
Posted Thursday, December 13, 2012 12:15 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:50 AM
Points: 40,432, Visits: 36,886
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 2008, MVP
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

Post #1396339
Posted Friday, December 14, 2012 3:58 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, October 18, 2014 11:53 AM
Points: 18, Visits: 418
Thanks Gail.
Post #1396835
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse