We help IT Professionals succeed at work.

How to restore lost data in SQL server 2000 ?

Medium Priority
249 Views
Last Modified: 2012-05-06
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 ?
Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
let's start by reading the support note:
http://msdn.microsoft.com/en-us/library/ms190982.aspx
CERTIFIED EXPERT
Top Expert 2012

Commented:
And hopefully your database has Full Recovery model and not Simple or you will be SOL

Author

Commented:
Yeah, my database has full recovery model
CERTIFIED EXPERT
Top Expert 2012
Commented:
Then follow the link posted by angelIII

Author

Commented:
sorry for replying late. Thanks to you for your prompt help on the issue.
CERTIFIED EXPERT
Top Expert 2012

Commented:
I am not sure what I did yo help.  Feel free to post a message in CS to have the points re-allocated.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.