Physically remove record

Question

Post reply

Physically remove record

Viewing 14 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

Steve it sounds like you could prevent it to me.

If you can detect data that does not belong AFTER the fact, you could add filters in the application or in the database to prevent them from getting there in the first place.

simple validation to prevent the end users from entering "bad" data, for example...it depends on the actual applicaiton of course, but to say it's not possible, well, it sounds like some deeper investigating would be in order.

If you can offer some concrete examples, instead of generalities, we might be able to offer some helpful advice.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

lionfan91 SSCrazy Eights Points: 8794 More actions · Answer 2

Lowell (2/1/2010)

Steve it sounds like you could prevent it to me.

If you can detect data that does not belong AFTER the fact, you could add filters in the application or in the database to prevent them from getting there in the first place.

simple validation to prevent the end users from entering "bad" data, for example...it depends on the actual applicaiton of course, but to say it's not possible, well, it sounds like some deeper investigating would be in order.

If you can offer some concrete examples, instead of generalities, we might be able to offer some helpful advice.

Example... imagine a system where you can talk about the Boston Red Sox, but no other MLB team. You cannot mention any other team by name, by reference, by inference, etc. If you do, that's bad and considered a "spill" of data which is what I need to be able to clean up. As you can see, it would be virtually impossible to come up with a "dirty word list" that covers everything.

I think the links provided by bitbucket-25253 look promising. I'll definitely be delving deeper into that information. Many thanks!!!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Rebuild the clustered indexes, shrink the MDF, rebuild the clustered indexes again. Won't help the LDF, though.

As a side bar, unless these so-called "space cleaners" do overwrites with all 1's and 0's a certain number of times, it won't meet certain military specs for "permanently deleting data".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

I'm a little late to the party, but worrying about the phantom copy of the record in the DB when your using CLASSIC ASP to access the data is like treating a skinned knee after stepping on a landmine. Really - the DB is the least of your worries.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

lionfan91 SSCrazy Eights Points: 8794 More actions · Answer 5

Matt Miller (#4) (2/1/2010)

I'm a little late to the party, but worrying about the phantom copy of the record in the DB when your using CLASSIC ASP to access the data is like treating a skinned knee after stepping on a landmine. Really - the DB is the least of your worries.

Actually, our Classic ASP application has been tested, certified and accredited by the Defense Intelligence Agency. This code has been around since 2003 and is pretty tight. We're planning an ASP.NET update this year though.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

Jeff Moden (2/1/2010)

Rebuild the clustered indexes, shrink the MDF, rebuild the clustered indexes again. Won't help the LDF, though.

Even that might leave the record on the hard drive in a sector that was previously used by the data file but isn't any longer.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

GilaMonster (2/2/2010)

Jeff Moden (2/1/2010)

Rebuild the clustered indexes, shrink the MDF, rebuild the clustered indexes again. Won't help the LDF, though.

Even that might leave the record on the hard drive in a sector that was previously used by the data file but isn't any longer.

Yeah... I forgot about that. Hmmmm... build a billion row Tally table with BIGINT??? 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

GilaMonster (2/1/2010)

milzs (2/1/2010)

So does anyone have any recommendations? The requirement would read like "Given one or more records in SQL Server 2008, these records need to be completely removed from the database and underlying file structure. After deletion, no remnants of the record(s) shall remain in the log files or the physical data files."

Export the data that you want to keep, and script all the DB objects. Detach the database. Use a secure deletion program to delete the data and log files and overwrite the sectors on the disk that the data and log files used to occupy. Create a new database. Recreate the objects and import all the data.

That is the only way that you can satisfy that requirement.

And - don't forget to destroy every previous backup media that might have the offending data. That could be every backup you have.

That's a bit more than simply breaking the log chain

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 9

So what exactly are you trying to protect against? Is securing the application, SQL Server, drives, backups, network traffic (and don't forget the information could also reside in server memory) not sufficient?

There has to be a limit to how far you go to destroy this information. Dumping it into a black hole is probably cost-prohibitive at this time (and I don't think that would even work theoretically!).

lionfan91 SSCrazy Eights Points: 8794 More actions · Answer 10

matt stockham (2/2/2010)

So what exactly are you trying to protect against? Is securing the application, SQL Server, drives, backups, network traffic (and don't forget the information could also reside in server memory) not sufficient?

There has to be a limit to how far you go to destroy this information. Dumping it into a black hole is probably cost-prohibitive at this time (and I don't think that would even work theoretically!).

Without getting too much into details, basically this is a DoD application that deals with security of facilities where classified information is handled. Each facility can work with data in one or more of thousands of "special access" programs. Individuals have to be briefed to each of these programs and they can't work with, or really even know about programs they aren't briefed to. This app processes one of these programs, but app users might be briefed to that program plus others. If they happen to enter something that falls under one of these other programs, that is a "spill" and the system is corrupted. The system becomes re-classified at that other program level unless we can ensure (with due diligence) the spilled data has been removed. If we can't we'll have to roll the database back to the last backup prior to when the spill occurred.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 11

milzs (2/3/2010)

If we can't we'll have to roll the database back to the last backup prior to when the spill occurred.

That may very well be the only way you can ensure that the data has GONE.

Detach the database and use a secure deletion program on the files (one that overwrites files to whatever security level is needed) and then restore from backup. With good tran log backups, you shouldn't lose much data, providing you detect the 'spill' quickly. Database deletes will leave the deleted data around (log and data file) for unknown periods of time.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

lionfan91 SSCrazy Eights Points: 8794 More actions · Answer 12

Everyone -- thanks a million for all the constructive inputs and feedback on my issue. Based on what everyone's ideas, here's what I came up with. Let me know what you think before I propose this to the powers-that-be.

1. We need to identify the date/time that the offending data record(s) were added to the database.

2. Run SQL scripts to delete the offending record(s) from the database. This will “delete” the record(s) but they will still be in the underlying MDF database file, marked as empty. This delete transaction is also logged to the SQL Server transaction log, so the LDF log file is “tainted” too, but we’ll see later on that we don’t really care.

3. Create a new database called “APP_REBUILD”

4. Use RedGate SQL Compare to synchronize the schema between the current APP database and APP_REBUILD. This will result in APP_REBUILD being a clone of the current APP database without any data in it.

5. Use RedGate SQL Data Compare to synchronize the contents of the current APP database and APP_REBUILD. This will copy every record in the current database to APP_REBUILD. Since the offending record(s) are no longer in the current database as far as SQL Server is aware, they will not be part of this copy.

6. Re-run SQL Compare and SQL Data Compare to ensure that the databases are clones of each other.

7. Make a full SQL Server backup of APP_REBUILD.

8. Delete the original APP database, ensuring the MDF and LDF file(s) are also physically removed from the server.

9. Delete any SQL Server database and transaction log backup files of the original APP database from on or after the date/time from Step 1.

10. Physically destroy any tape backups of the original APP database from on or after the date/time from Step 1. More than likely the tape backup will contain “tainted” MDF, LDF, SQL Server database backup files, and/or SQL Server transaction log backup files.

11. Rename APP_REBUILD to APP. The web application should now be working with this new database.

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 13

It looks pretty good. There are a few other areas that you may want to investigate:

1) The bad data could still be in server memory - reboot the server?

2) There could be some spill to tempdb, which might require you to follow a similar process with the tempdb files

3) Not sure how Data Compare works, but you will want to ensure that it's copying individual records rather than pages.

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 14

matt stockham (2/3/2010)

It looks pretty good. There are a few other areas that you may want to investigate:

1) The bad data could still be in server memory - reboot the server?

2) There could be some spill to tempdb, which might require you to follow a similar process with the tempdb files

3) Not sure how Data Compare works, but you will want to ensure that it's copying individual records rather than pages.

1) The bad data could still be in server memory - reboot the server? -> does this properly clear the page file? The information could also have been written there as well.