question

Darshak avatar image
Darshak asked

Recover deleted data in simple recovery model.

Hi All, Please help me with the below mentioned scenario-> Scenario-> My Collegaue has accidentally deleted 10000 of records from a table named as X(id int,name varchar(20),sal int) My database is in simple recovery model and i don't have any backup. Can i recover data using single query? Now is there any way by which I can recover the already deleted and commited data on same day and also would like to know whether same can be recovered after few days of passing by. Immediate response would be much appreciated. Regards, Innovator.
recoverydelete-backupsimplequery
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
Sorry man, but no. In simple recovery model, the transaction log is truncated when the transaction is committed (more or less so at least, it's a little while later that the truncation actually happens). And without backups, you're out of luck.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

If you are saying it "is" possible, can you add the links to the question where you have found this reference?
4 Likes 4 ·
@Darshak The delete works like this: all rows subjected to delete operation are marked as ghost (on the record header level). That is all. Even though the data is "still there", as far as the data availability via select statements is concerned, ***the data is gone***. In a mean time, the asynchronous process kicks in to indiscriminately search for all records (in all tables) which are marked as ghost records and if the records are found, the references to the records are removed from the slot array (page header level). Even though the data is "still there", ***it is not available***. Additionally, the deleted records may and will be overridden by new records should any inserts take place afterwards.The only (theoretical) way to restore it is by (immediately after delete) dumping the data pages contents as binary and then examining the said binary in order to attempt to retrieve the deleted information (this will require some procedural coding, not T-SQL). Doing so would require one to be intimately familiar with the page and record structures, still without any guarantee. As @Magnus Ahlkvist said, "without backups, you're out of luck".
2 Likes 2 ·
It's possible. Some peoples are using that but i can't have any idea. please try to solve this problem.
0 Likes 0 ·
jason_clark03 avatar image
jason_clark03 answered
Here is how to Recover Deleted records in SQL Server In Simple Recovery Model. [ https://www.youtube.com/watch?v=y74PMMs4Lto][1] with **SysTools SQL Log Analyzer** [1]: https://www.youtube.com/watch?v=y74PMMs4Lto
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.