I am new to SQL DBA and i have one question on disaster recovery.

Question

I am new to SQL DBA and i have one question on disaster recovery.

BSLChennai

SSCrazy

Points: 2981
More actions
November 17, 2014 at 5:20 am

#298559

Hi
I am new to SQL server DBA, i am started learning SQL server 2008r2 DBA. i have one question on disaster recovery. below is the scenario.
Database: SQL Server 2008R2
1. Every sunday will take FULL database backup
2. Every Wednesday will take DIFFERENTIAL Backup
3. Every day every one hour transactional log backup.
my question is:
on Friday 9:30AM database was crashed and we need to recover the database without data loss, because on Friday 9AM log backup was completed so next log backup is 10AM.
in this scenario how can we recover the database without data loss.
please explain me on this.
Regards,
BSL

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

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

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 1

Where would you start with that one yourself? We can advise but it would be good to see how much you have already worked out.

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

Depends on the type of disaster.

If the log file is intact and SQL is still running, you can take a tail-log backup to get the data up to 9:30. If the log file was destroyed, then the best you can do is to restore to 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

HanShi SSC-Dedicated Points: 33506 More actions · Answer 3

This looks like a (sample-)question for an exam. The answer is:

- take a tail log backup

- restore the last FULL backup (using NORECOVERY)

- restore the last DIFF backup (using NORECOVERY)

- restore all LOG backups since the last DIFF backup (using NORECOVERY)

- restore the TAIL backup (using RECOVERY)

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 4

HanShi (11/17/2014)
This looks like a (sample-)question for an exam. The answer is:

Yeah- that's why I didn't want to spoon-feed the answer :-D.

HanShi SSC-Dedicated Points: 33506 More actions · Answer 5

Beatrix Kiddo (11/17/2014)
HanShi (11/17/2014)
This looks like a (sample-)question for an exam. The answer is:
Yeah- that's why I didn't want to spoon-feed the answer :-D.

I hope the OP is learning from (researching) my answer and not blindly take it for granted

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **