Restore Sequence

Question

Post reply

Restore Sequence

info.sqldbamail

SSCommitted

Points: 1741
More actions
June 8, 2015 at 6:28 am

#398558

If i have a FULL backup on sunday evening @ 8:00 PM and succeeding a Differential backup @ 7:00 AM everyday(MON,TUE,WED,THU,FRI) with this LOG Backup for every 4 hours each day and my database(EX: TESTDB) had crashed at 6:00 PM on Friday what is the Restore sequence
Ans : First a Full backup with no recovery,
step 2 : All the Differential backups since last full backup with no recovery,
step 3: All the log backups since last full backup with recovery, STOPAT <time or LSN number>
is this correct or not?
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Save a tree... Please don't waste paper unless you really need to!
When life puts into problem don't say 'God Why Me' say:w00t: 'Try Me'

Viewing 5 posts - 1 through 4 (of 4 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

You don't need all the differentials since the last full, just the latest one.

This is what you need:

1. Last full backup

2. Last differential

3. All the log backups taken after the differential

-- Gianluca Sartori

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 2

spaghettidba (6/8/2015)
You don't need all the differentials since the last full, just the latest one.
This is what you need:
1. Last full backup
2. Last differential
3. All the log backups taken after the differential

Restoring just the last differential doesn't sound correct to me, but I've been wrong before.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Alvin Ramard (6/8/2015)
spaghettidba (6/8/2015)
You don't need all the differentials since the last full, just the latest one.
This is what you need:
1. Last full backup
2. Last differential
3. All the log backups taken after the differential
Restoring just the last differential doesn't sound correct to me, but I've been wrong before.

Diff backup takes all the pages marked for the differential base, which is the pages changed since the last full backup. A new full backup resets the differential base. See here for details: https://msdn.microsoft.com/en-us/ms175526.aspx

-- Gianluca Sartori

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

Alvin Ramard (6/8/2015)
spaghettidba (6/8/2015)
You don't need all the differentials since the last full, just the latest one.
This is what you need:
1. Last full backup
2. Last differential
3. All the log backups taken after the differential
Restoring just the last differential doesn't sound correct to me.

It is correct. Differentials contain all changes since the full that they're based on, hence it's a pointless waste of time to restore multiple diffs one after the other (though you can do it)

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