Disaster Recovery In SQL SERVER

Question

5.00/5 (3 votes)

See more:

Hello All,

What will be the plan in below disaster case :

"I am having one sql server database with backup plan of FULL Backup on 12:00 AM Daily and Diff Backup after every 2 Hours and Log backup after every 1 hour and that is auto schedule. At 3:40 AM , due to XYZ reason the disaster happen in sql server database "

Posted 25-Nov-11 17:44pm

dr.kitanjal

Updated 25-Nov-11 18:02pm

RaisKazi

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Amir Mahfoozi · Accepted Answer · 2011-11-25T18:22:00

Solution 1

Hi Dr,
I think the recovery plan should be like this :
First restore the full backup of 12:00AM then restore the last differential backup which was happened at 2:00AM and then restore the log backup which was happened at 3:00AM and the data which was manipulated between 3:00AM and 3:40AM is probably lost forever :( (except when you are able to restore database files). And all of this was based on this assumption that all of 3 backup jobs was started at the 12:00AM.

Posted 25-Nov-11 18:22pm

Amir Mahfoozi

Comments

Mehdi Gholam 26-Nov-11 1:13am

5'ed

Amir Mahfoozi 26-Nov-11 4:25am

Thanks ;)

dr.kitanjal 27-Nov-11 13:00pm

Thanks Amir , But as a DBA I can not loss 40 min of data . It is very huge database update. Can I recover it from log file (.ldf) file which are present in data folder along with .MDF file ?

Amir Mahfoozi 28-Nov-11 8:37am

Of course, you can restore them from log files. But I think that its going to be time consuming and expensive for you please review these tools : http://www.red-gate.com/products/dba/sql-log-rescue/ and http://www.apexsql.com/sql_tools_log.aspx

dr.kitanjal 28-Nov-11 14:11pm

Can you please let me know how to restore the data updated between 3 am to 3 40 am from .log file ? Thanks in advance

Amir Mahfoozi 29-Nov-11 0:16am

As I remember the above mentioned viewers show you the exact SQL command which was committed at the specific time, so if you run all of those commands consequently you will rich to the state that was the database were at 3:40. And another question arises here is that if the .ldf and .mdf is available why you don't just attach to them. It will probably bring back all data which was exist until 3:40 for you :-?

dr.kitanjal 29-Nov-11 1:03am

Thanks . So overall we need to use the third party tool anyway because it couldn't attach files and thrown errors.That's why I need sideway.

Amir Mahfoozi 29-Nov-11 1:14am

I wish you will be able to recover the data from the log files. Good Luck.

dr.kitanjal 2-Dec-11 6:37am

Thanks for you help

Amir Mahfoozi 2-Dec-11 13:21pm

You're welcome.