How to recover a deleted row from SQL Server 2005 table?
Add a comment |
5 Answers
If you have database backups that have deleted data:
- Restore backup in separate database and recover deleted data from there
If there are no backups but your database is in full recovery mode:
- Try reading transaction log using some third party transaction log reader or using DBCC LOG command.
You’ll need help from third party tools because transaction log is not well documented. This is because it’s purpose is not to be used for this kind of recovery. However, if you can read it there are a lot of useful details there that can be used to recover accidentally deleted data.
Comments
Rollback the transaction (if you started one).
Restore a backup (if you have one).
[edit] If you have transaction logs, you should be able to restore the backup of the database to the point roughly just before the row was deleted (assuming you know when that was).
Comments
There are two ways we can recovery specific table:
- The first one: restore fullback up with no recovery after restore with
no_truncateoption fort-logbackup - The second way: using triggers we can recovery deleted tables with audit table.
Comments
Don't forget to set full recovery model for a database if you need the "restore to a point in time" option!
Comments
The ApexSQL Log tool can be the solution for deleted rows. In case the DELETE operation exists (the database was not using the Simple recovery model) in database transaction logs (online, backups), the tool can create an undo T-SQL script for the operation.
Disclaimer: I work as a Product Support Engineer at ApexSQL
