Database Recovery

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.

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.

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.

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.

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