Restore Sequence
-
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>
Can anyone say this sequence 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'
-
log backups are cumulative, and are not affected by differential, so you cannot go from full + diffs + one last log. it's an either log-or-differential process.
full backup, plus any log backups up to the stop at timestamp; no differentials required; you should try to take a tail log backup , and see if you can do a point in time restore from that to get the best coverage.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thank You
I get your point : 1 FULL Backup + Most Recent Diff Backup + All Cumulative log backups since Last full backup is this correct.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
When life puts into problem don't say 'God Why Me' say:w00t: 'Try Me'
-
info.sqldbamail (6/8/2015)
Thank YouI get your point : 1 FULL Backup + Most Recent Diff Backup + All Cumulative log backups since Last full backup is this correct.
No, that's not right.
To do a point in time restore, its
FULL Backup + ALL Log Backups, and you decide on a point in time.
Ideally, it's Take a log backup right now, and then
FULL Backup + ALL Log Backups + That Last Backup to be sure you have the point in time.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell (6/8/2015)
log backups are cumulative, and are not affected by differential, so you cannot go from full + diffs + one last log. it's an either log-or-differential process.Errr...?????
Log backups aren't cumulative, they form a sequence, and you can definitely restore full + diff + logs since the diff, that's half the point of a differential, to reduce the number of logs that would need to be restored.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
info.sqldbamail (6/8/2015)
I get your point : 1 FULL Backup + Most Recent Diff Backup + All Cumulative log backups since Last full backup is this correct.Almost.
Full + latest differential that's based on that full backup + log backups since the differential
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thank you every one for your replies.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
When life puts into problem don't say 'God Why Me' say:w00t: 'Try Me'
-
Now for a question to you... Your avatar is of a book cover for a "Complete Guide to Database Administration". Isn't this information in that book?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply