Click here to Skip to main content
14,693,117 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi,

I am using SQLServer 2005 and by mistake I have deleted some of the values in one table now I want to recover the deleted data.

Is there any way? I don't have any backups.

Records has been deleted using <code>DELETE</code> statement.
Posted
Updated 1-Mar-11 2:41am
v2

Here[^] is one method.

Good luck though, it doesn't always work.

You should always take backups of databases to minimize any losses from situations like this.
   
SQL Server offers point in time recovery

http://www.mssqltips.com/tip.asp?tip=1229[^]

However, if you have no backups and no transaction logs, then you're out of luck!
   
Hi
Try this may helps you

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1219179,00.html

Recovery tools
Another option is to use recovery tools that allow you to read the SQL Server transaction log and then undo transactions and recover deleted tables -- even the truncated tables in some cases. Since all transactions are recorded in the transaction log, with these tools you can read through and create T-SQL statements to undo the statement or even undo a truncate table statement.
Some of these recovery tools are:

  • Lumigent Technologies' Log Explorer
  • ApexSQL LLC's ApexSQL Log
  • Red-Gate Software Ltd.'s SQL Log Rescue
   
For SQLServer 2005 I suggest using ApexSQL Recover[^] It’s not free tool but it can do the job for you in your database was in full recovery mode.

In that case this tool will try to read transaction log and recover transaction based on the info that exists in transaction log.
   
Comments
CHill60 12-Jun-13 7:06am
   
This question is 2 years old. Posting links to websites on old questions could be perceived as spamming
Server keeps log for each deleted records.You can query these logs via 'fn_dblog' sql server function with the table name and it will give you all the deleted records from the log.
Select [RowLog Contents 0] FROM   sys.fn_dblog(NULL, NULL) WHERE  AllocUnitName = 'dbo.TableName'        AND Context IN ( 'LCX_MARK_AS_GHOST', 'LCX_HEAP' )        AND Operation in ( 'LOP_DELETE_ROWS' )

But this log is in Hex format. and you need to convert this Hex format to your actual data.
Given below is the article will help you to recover the deleted records in the same way defined above.
http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900