The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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

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
accept rate: 40%

...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

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

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 12 Sep '13, 18:24

question was seen: 780 times

last updated: 13 Sep '13, 09:42