How to restore lost data in SQL server 2000 ?

while performing archival activity, one of my colleague mistakenly dropped one column  from one production table which has caused at least 450000 values of a column to be lost. We have full backup of database taken at morning 7 AM. Event has occured at 2:30 PM. Please suggest how to restore data till the last moment of that event ? Is it possible from log to restore it upto that point ?
Prasenjit_DuttaAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I would restore the db under a second name to the point of time before the drop column.
then, add the column to the table in your prod table.
then, with a UPDATE based on the primary key, get the values back from the restored db:
UPDATE t
  SET column_name = b.column_name
 FROM yourprod_db.dbo.table_name t
 JOIN yourrestored_db.dbo.table_name r
   ON r.primary_key = t.primary_key

Open in new window

0
 
Prasenjit_DuttaAuthor Commented:
Hi Angel,
Thnks for ur reply. But I do not know how to restore database to the point ot time before the drop column
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
let's start by reading the support note:
http://msdn.microsoft.com/en-us/library/ms190982.aspx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Anthony PerkinsCommented:
And hopefully your database has Full Recovery model and not Simple or you will be SOL
0
 
Prasenjit_DuttaAuthor Commented:
Yeah, my database has full recovery model
0
 
Anthony PerkinsCommented:
Then follow the link posted by angelIII
0
 
Prasenjit_DuttaAuthor Commented:
sorry for replying late. Thanks to you for your prompt help on the issue.
0
 
Anthony PerkinsCommented:
I am not sure what I did yo help.  Feel free to post a message in CS to have the points re-allocated.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.