August 1, 2012 at 12:22 am
i am confused in one scenario please help
every Saturday full back : 10 PM
daily differential backup : 10AM
every hour transaction Log backup started at 10:30
DB crashed at 5 PM Wednesday. what will be the recovery process ?
August 1, 2012 at 12:38 am
What did your server crash?
1 - Restore Saturday's Full Backup (with no recovery)
2 - Restore last Differential Backup (also with no recovery)
3 - Restore all the Transaction Log Backups that occurred AFTER the last Differential Backup (also with no recovery) - DO NOT RESTORE THE VERY LAST Transaction log)
4 - Restore the final Transaction Log backup with Recovery
This should put your DB into the state it was in when the very last TRN file was written.
You might want to find out why it crash (error logs, etc) in case there's something in that last log file that caused it to crash...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 1, 2012 at 12:47 am
thats wat i thought but main question is that diff. backup taken at 10 AM and Tlog Bckups taken at 9:30 and 10:30 and 11:30 and so on
so after restoring diff bakup, T log bakup of 10:30 wont create any issues?
August 1, 2012 at 12:55 am
You would restore Saturdays full backup, Wednesdays differential backup from 10am, and then the 10:30, 11, 11:30, 12, 12:30, 1p, 1:30, 2, 2:30, 3, 3:30, 4, and finally your 4:30p transactional log backup using the options I mentioned above
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 1, 2012 at 1:05 am
NO NO, Tlog bkup happenes at every 1 hr like 9:30 , 10:30 ,11:30, 12:30... so on
so i am confused if i restore diff. bkup which was taken at 10:00 am, will it allow me to restore Tlog backup of 10:30? because according to concept of LSN numbers, 10:30's Tlog Bkp will luk for 9:30's Tlog Bkp in sequense right?
August 1, 2012 at 2:39 am
please help on this if any body can through light on it.
August 1, 2012 at 2:43 am
You would restore Saturday's full backup, the Wednesday morning differential, then all log backups since the diff until the point of failure (so 10:30, 11:30, 12:30, 1:30, 2:30, 3:30, 4:30)
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
August 1, 2012 at 2:49 am
i am confused if i restore diff. bkup which was taken at 10:00 am, will it allow me to restore Tlog backup of 10:30? because according to concept of LSN numbers, 10:30's Tlog Bkp will luk for 9:30's Tlog Bkp in sequense right?
August 1, 2012 at 2:53 am
Yes, of course it will. That's why we've been saying to restore full then latest diff then all logs since the 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
August 1, 2012 at 5:44 am
Zeal-DBA (8/1/2012)
i am confused if i restore diff. bkup which was taken at 10:00 am, will it allow me to restore Tlog backup of 10:30? because according to concept of LSN numbers, 10:30's Tlog Bkp will luk for 9:30's Tlog Bkp in sequense right?
I believe what you are missing is the idea that the t-log backup is not mutually exclusive from the differential. The t-log restore from 10:30 is not going back to 9:30 as if the differential never happened. Logically, it is going back to 10 when the diff happened. Got it? 🙂 Physically, the log backup is from log to log, but on the restore it knows when the diff was run and will not "duplicate" restore operations from 9:30-10.
Jared
CE - Microsoft
August 1, 2012 at 7:38 am
Zeal-DBA (8/1/2012)
i am confused in one scenario please helpevery Saturday full back : 10 PM
daily differential backup : 10AM
every hour transaction Log backup started at 10:30
DB crashed at 5 PM Wednesday. what will be the recovery process ?
Is it possible to take tail log back up?....I mean, what exactly you mean DB crashed, no access at all?....if possible, take another tlog backup and do a point in time recovery.
Regards,
TA
Regards,
SQLisAwe5oMe.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply