x

Database Recovery

Hi,

I have a question in database recovery.

Qs is -- I have a database backup on last friday and my server was crashed on tuesday.. In this situation how to recover the data from sql server.

Thanks.
more ▼

asked Dec 25, 2011 at 10:48 PM in Default

sathishkumar gravatar image

sathishkumar
234 22 24 26

(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

Since this is an interview question, I'm kind of loath to provide you with an answer that you can try to reel off to them. The things you need to look at are what kind of backups do they have in place. If only the full backup you mentioned, assuming the database is corrupt, that may be your only option. You need to drill down on what they mean by "the server crashed" because the server can go down and the databases can be fine.

I suspect they're asing an open-ended question to try to engage you and see what you know. I always did the same thing. If you don't know what to do, the best answer is to say that. If you have speculative answers based on knowledge, go with those. But asking for a pat answer on a web site is not the way to go.
more ▼

answered Jan 03, 2012 at 08:32 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

(comments are locked)
10|1200 characters needed characters left

First, +1 to Grant. I agree with everthing he said, including the fact that we should be reluctant to give a pat answer to an interview question here.

But let me add that if you are in a disaster recovery scenario (real or hypothetical) and you find the backups are insufficient, then do not forget to look in unorthodox places. For a couple of examples:

  1. Does a developer have a more recent backup taken to make a local dev environment? This is fairly common in some places, and probably won't get up to the minute, but might get you close.
  2. Does the SAN take automatic images that might include the database files? I read a story about that saving a business from a major problem recently. I think the article was one simple-talk but I can't find it at the moment.
  3. Is the data replicated somewhere, perhaps for reporting purposes?
  4. If only part of the database is damaged, can it be recreated though redundant data? My mentor likes to tell the story of one time he was brought in as a consultant after a developer dropped a table. That small group within a larger business did not have a professional DBA on staff, the database was in simple recovery mode, and there were no backups at all. But the tables were not normalized and they still had some (but not all) of the base data that the database was built from. Combining the redundant data in the database with some of the base data to recreate most of the table and let the division carry on.

Obviously, if you need to look at any of those 4 or other creative options, it means that something has gone very wrong in your backup process, and that needs to be fixed. But looking at unusual options like those is far better than accepting massive data loss.

And speaking of Grant, his Backups, what are they good for? article is fairly apropros here.

more ▼

answered Jul 13, 2012 at 04:54 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.6k 20 23 32

(comments are locked)
10|1200 characters needed characters left
It Depends what kind of crash and how bad. If the server is running and the database is still connected and the log is intact, you can do a tail-log backup and restore the database.Otherwise you will lose that data from crashed time.
more ▼

answered Dec 25, 2011 at 11:12 PM

venkatreddy gravatar image

venkatreddy
562 28 29 32

+1 from here. In addition, it would depend upon your recovery model as well. I guess you could be left with only 3rd party tools to work with.
Dec 25, 2011 at 11:37 PM Usman Butt
Its an interview question..they saying the server is not running to take any backup...in that situation how to recover the database...
Dec 26, 2011 at 12:17 AM sathishkumar
If you are using Full Recovery model you can take the tail log backup to point of database failure.Recover the database using previous backup set and taillog.You may loose the data after server crash as you are not able to connect the database.
Dec 26, 2011 at 01:04 AM venkatreddy
(comments are locked)
10|1200 characters needed characters left
If your database is in full recovery model then you can use DBCC CHECKDB, using this you can recover database, regenerate ldf from mdf and mdf from ldf. but It Depends on what kind of crash and how had.
more ▼

answered Dec 27, 2011 at 12:31 AM

Pratikc5 gravatar image

Pratikc5
28 3 4 6

How does checking the consistency of the database recover it and regenerate the files?
Jan 03, 2012 at 08:33 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

If the database is corrupted, it can be recovered by SQL Database Recovery software. Some of well known software are:

RecoveryFix for SQL Database Recovery

Kernel for SQL Recovery

Stellar for SQL

Data Doctor

systool

Thanks
more ▼

answered Mar 25, 2013 at 11:40 AM

johnwebsoft (suspended)
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x346
x47
x19
x15

asked: Dec 25, 2011 at 10:48 PM

Seen: 2043 times

Last Updated: Mar 25, 2013 at 11:40 AM