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

asked 12 Sep '13, 18:24

dejstone's gravatar image

dejstone
959405069
accept rate: 0%


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.

permanent link

answered 12 Sep '13, 19:30

John%20Smirnios's gravatar image

John Smirnios
9.0k377114
accept rate: 39%

...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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×11

question asked: 12 Sep '13, 18:24

question was seen: 917 times

last updated: 13 Sep '13, 09:42