cancel
Showing results for 
Search instead for 
Did you mean: 

how to find deleted rows?

Former Member
2,744

A customer called today reporting that a long-term employee's schedules were missing from our staffing-scheduling system. All application-level checks yielded nothing and I am now left hoping that sqla deletes rows much like windows deletes: data is not really deleted, but just marked as deleted. Is there a way to find/list deleted records in an sqla database?

Thanks, Doug

Accepted Solutions (0)

Answers (1)

Answers (1)

johnsmirnios
Employee
Employee

You could use dbtran on the log and find the inserts / updates for the data you are interested in. Alternatively, you could start up a copy of a recent backup of your database to find the data. You could then export (via UNLOAD or OUTPUT, etc) the data you want.

VolkerBarth
Contributor
0 Kudos

...which both require that the deleted rows are still contained in the log (i.e. it has not been truncated recently) or in the backup database...

johnsmirnios
Employee
Employee

Yes. Nothing from the original post suggests that they don't have backups. As for the logs, they should also be part of the backups -- even if they get truncated.

Deleted rows are truly deleted and the space is reused, often right away. When a row is removed from a page, adjacent row data is moved on top of the old row to keep the rows contiguous on the page. If you were extremely lucky, a forensics team might be able to find portions of some rows on some pages but that would be extremely time consuming, expensive and not likely to succeed.

VolkerBarth
Contributor
0 Kudos

Thanks for the clarification. - I had just tried to point out (but you have made it way more clear, obviously!) that the current database file itself doesn't contain the deleted rows in a "user-readable" fashion, at least not for "mere mortals", even with an hex editor...

Former Member

Thanks for the feedback John & Volker. I will ask the customer for the last date the data was seen and then ask their IT team to restore a copy of the database from around that date. I will then mount the database, use isql to generate a list of the employee's schedules, save that list to a text file and import the data into production.