Recover rows from table, unintentionally deleted

[stefanlennerbrant]

I unintentionally deleted data from a table with
delete from TABLE

This was not detected until much later (more than a month ago), and we now have no backup tape that includes the data, just a bunch of empty tables...

However, there has not been much writing in the database (not in any table) since then, so perhaps there is a way to recover at least some of the data?

Does anybody have any suggestion on how to recover deleted data from the MDF file. I suppose the log file has been truncated several times since (because of the backups)

No tables have been dropped. The data was just deleted with something like
delete from EMPLOYEES
delete from ASSSIGNMENTS
insert into EMPLOYES(name) values('John Smith')

Thus, all tables were cleared and then one value was inserted into one of the tables

Thanks,
/Stefan Lennerbrant

 

[SQLBill]

You are out of luck. The .mdf file IS THE DATABASE file. So if the data is gone, it's gone from the .mdf file.

-SQLBill

 

[stefanlennerbrant]

Well, I suppose the data is not physically deleted, is it? The pages are probably just marked as "free" and it should be possible to recover the data in them, at least in theory.
/Stefan Lennerbrant

 

[JayKusch]

Deletions in a database work a bit different than deleting files. There is no way to "magically" recover the deleted records. They are not just sitting in renamed files that a Norton Untilities could find and recover.

Your only recourse is to use a third party utility like Lumigent Log Explorer and recover the deleted records via the transaction log.

I do not beleive there is any other means to recorver these records.

Thanks

J. Kusch

 

[stefanlennerbrant]

I just detached the database in question and got hold of the MDF file. When attaching it to a local database, the tables are of course (almost) empty. Sigh!
However, looking directly into the binary file itself, I can see the data.

Fortunately, we've mostly been writing (insert) sequentially and have not updated/deleted much, when using the database. Thus, I suppose that most data is more or less stored in sequence.

Just manually going through the file and copy/pasting the (mostly textual) data is one option, and using Lumigents tools is of course another.

However, I'd expect that someone has been in the same situation before, and that there may exist some "extremely quick and dirty" filereader application that just traverses the MDF file structure and tries to do the best of the situation?

If it turns out that we really can't find a good enough backup tape that we may use, I'll probably end up writing such a small application.
If there already is something like that (or at least some kind of application or some info to start building upon), it would of course help out a lot!

/Stefan Lennerbrant

 

[stefanlennerbrant]

Just an additional note: I notice that the transaction log in question file is actually quite large.
May it be so that the transaction log data from the delete operation is actually still available in the transaction log, even though we've performed several backups since then.

I though the backups truncated the transaction log? However, it probably just doesn't autoshrink it?

I very well understand that data in MDF and LDF files will be overwritten, and in time lead to data missing physically.
On the other hand, I do think, though without knowing, that MDF files actually *are* (in principal) just like DOS file systems, with deleted data sitting in "renamed files" (or rather free-marked pages), such that a tool like "SQLServer-Norton" -), if there existed one, could recover the data.


One option would be to recover from the free-space-marked pages in the MDF file. I'd guess that's the best option.
The other one would be to recover from the translog left by the delete operation in the LDF file. However, I suppose that that information is similarly free-spaced-marked because of the log truncations performed during the backups since then?
I guess that it's "easier" to understand the MDF file structure than the LDF file structure?

/Stefan Lennerbrant

 

[stefanlennerbrant]

By the way, I solved all this by finding a backup that was not too old, after all.
It did not contain all data, of course, but I was able to regain the last couple of transactions simply by parsing the binary MDF file directly.

The MDF datafile format is not all that complicated, so it's quite possible to recover deleted data. Really!

/Stefan Lennerbrant

 

[SQLBill]

Now that we know you still had the transaction log (oh well too late this time), I would have suggested doing a backup of the log.

Then RESTORE DATABASE dbname
WITH NORECOVERY
Followed by RESTORE LOG dbname
WITH STOPAT date and time prior to the deletion
WITH RECOVERY.


Check those commands out in Books OnLine for future reference.

-SQLBill