please give answer to this Interview question

Question

Post reply

please give answer to this Interview question

rraza

SSC Enthusiast

Points: 158
More actions
March 15, 2014 at 9:41 am

#305301

Can anyoine answer this interview question Please??
A DBA accidentally dropped a critical highly transactional table in production. Walk me through the exact procedure to restore it without taking the application offline.
Thanks

Viewing 15 posts - 1 through 14 (of 14 total)

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

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

How about you give your answer, then the experts here can have a discussion point.

rraza SSC Enthusiast Points: 158 More actions · Answer 2

I think single table backup procedure not available in sql .

may be have to restore transaction log backup of that time when table was deleted.

Lowell SSC Guru Points: 323518 More actions · Answer 3

single table backup is a manual process, which is what the question is testing your knowledge for.

since you cannot take the application offline, which means you cannot restore the current database over itself, what do you think is the next step?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rraza SSC Enthusiast Points: 158 More actions · Answer 4

I think we will restore the file backup of that time when table was deleted.

Since Im a beginner I m putting my logic not sure if sql works like that or not.

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 5

The things, you don't want to just restore, because if you overwrite the existing database, you'll probably take the app offline or, lose data. So, you should find out if there is a way to restore other than to the original database.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

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

rraza (3/15/2014)
I think we will restore the file backup of that time when table was deleted.

Why a file restore? What's your justification for choosing that type of restore operation? Do you know the requirements for a file restore?

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

GilaMonster (3/16/2014)
rraza (3/15/2014)
I think we will restore the file backup of that time when table was deleted.
Why a file restore? What's your justification for choosing that type of restore operation? Do you know the requirements for a file restore?

Adding on to what Gail said...

Do you know what the backup process is for this particular database? Knowing what is backed up and when it is backed up is the first thing I would have been finding out for this scenario.

Maybe they are using redgate backup which could make this ordeal a little easier.

If the table is "critical," verify that the application isn't already taken offline due to the drop/truncate/delete or whatever

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

CKX SSCrazy Points: 2519 More actions · Answer 8

rraza (3/15/2014)
Can anyoine answer this interview question Please??
A DBA accidentally dropped a critical highly transactional table in production. Walk me through the exact procedure to restore it without taking the application offline.
Thanks

The question lacks context because the way you might go about restoring such a table would depend on a number of factors which you would need to know.

For instance, what kind of backups are being taken, are snapshots taken, how much data is the company prepared to lose and so on...

That being said, that may well be the whole point of the question... to make it as generic as possible to see if you ask pertinent questions before you attempt an answer because in such cases there isn't just "one procedure" to do it.

Ultimately, you would need to reconstitute that data from somewhere... where that "somewhere" is would "depend".

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 9

CK2 (3/17/2014)
rraza (3/15/2014)
Can anyoine answer this interview question Please??
A DBA accidentally dropped a critical highly transactional table in production. Walk me through the exact procedure to restore it without taking the application offline.
Thanks
The question lacks context because the way you might go about restoring such a table would depend on a number of factors which you would need to know.
For instance, what kind of backups are being taken, are snapshots taken, how much data is the company prepared to lose and so on...
That being said, that may well be the whole point of the question... to make it as generic as possible to see if you ask pertinent questions before you attempt an answer because in such cases there isn't just "one procedure" to do it.
Ultimately, you would need to reconstitute that data from somewhere... where that "somewhere" is would "depend".

Precisely. It's an open-ended question meant to elicit a set of responses from you that shows your knowledge of the topic. Not just answering questions though, but instead, making assumptions, "Well, it really depends on what we have for backups, but let's assume..." and off you go. One of my favorite interview questions is, "You get a phone call, they say the system is running slow. Now what?" The responses there show troubleshooting, system knowledge, reasoning ability, communication, lots and lots of stuff. It's the best way to interview people.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

dbassassin Default port Points: 1410 More actions · Answer 10

First, recognize the appropriate dbareaction

Here is a good chance for you to some science. Setup your lab, and run through the scenario. Maybe using HammerDB you can run a test, and drop one of the tables during the testing. I'd say (don't ask me how I know) that if you dropped one of the more important tables, then the application is going to have issues, so for the sake of this experiment, you can turn off HammerDB and move forward.

Now you at least have a sandbox to play in and come up with a really good answer (I'd be impressed to hear a job candidate explain to me how they setup their virtual lab and tested a successful restore of the table).

and when you are done, post your results 🙂

Why is it that people who can't take advice always insist on giving it? - James Bond, Casino Royale

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

sql4gnt (3/18/2014)
(I'd be impressed to hear a job candidate explain to me how they setup their virtual lab and tested a successful restore of the table).

t.b.h. if a candidate mentioned he had a lab set up at home, I'd hire him on the spot (though that may be more an artifact of the average level of job candidates here than anything else)

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

sri2015 SSCrazy Points: 2780 More actions · Answer 12

if you have backup file, restore into development server then migrate the table to Production Database.

rraza SSC Enthusiast Points: 158 More actions · Answer 13

Thank u everyone,, Now I understand how to react to these questions at first.

Plus I will experiment it at home , will get back

Thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

rraza (3/18/2014)
Thank u everyone,, Now I understand how to react to these questions at first.
Plus I will experiment it at home , will get back
Thanks

Good, glad it was useful information for you.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events