Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4799
  • Last Modified:

recovering delete rows in SQL 2005

Is there a way to recover deleted rows in SQL 2005 with myTransaction Log?
0
mattphung
Asked:
mattphung
  • 3
  • 3
1 Solution
 
mastooCommented:
Recover the db and log backups into another database and then insert the records back into the original database.
0
 
mattphungAuthor Commented:
where do I get the db and log backups?
I created another db called "tempDB". How do I insert the deleted records back into the original database? Where is the deleted records kept?
0
 
mastooCommented:
Sorry, my answer only works if someone is running backups of the database.  You have a dba to ask?  If not you're probably out of luck but right-click the database in Enterprise Manager and do properties then options.  What is the Recovery Model?
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
mattphungAuthor Commented:
My recovery model is FULL
0
 
mastooCommented:
Ah, you might be in luck then.  Right click the "databases" node in the Enterprise Manager treeview.  Select All Tasks and then Restore.  Type in some dummy name for the Restore As, such as MyNewDb.  Go to Show backups of and select the original.  Down below hopefully you'll see a list of backups starting with a full backup and then one or more transaction log backups.  Check the last one in the list - it needs to have a date and time later than when the records were deleted.  (Stop following these steps if at any point something isn't as described)  Ok - to restore the backups into the new database name.  Then let's say you're looking for deleted records in TableOne where SomeDate = '11/29/2007'  Run a select in Query Analyzer in the restored database to look at the records and then you can do an insert into the other database to put them back.  BUT, this gets pretty ugly if there is any hierarchy of records deleted (i.e., there is a parent-child relationship and both parent and child got deleted).
0
 
mattphungAuthor Commented:
Thanks for all your help! It worked.
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now