|
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 |
|
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. ...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...
(13 Sep '13, 04:11)
Volker Barth
Replies hidden
1
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.
(13 Sep '13, 06:12)
John Smirnios
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...
(13 Sep '13, 06:40)
Volker Barth
1
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.
(13 Sep '13, 09:42)
dejstone
|
