How exactly have you encrypted the data in the table? What is this card scanner program that you refer to?

Thanks for you reply Mr Denny

I have used a thirdparty dll to encrypt the data.

The strang2 thing is it only finds some of the data. When i view the exact records in the table the data is not there.

If i run the program on backup of the DB it finds more records than if i run it on the MDF file. could it be the MDF file is screwed up.

Hi MrDenny

Here is what im doing :

Just to see whats going on i have taken 1 table (user messages table) from the production database and created new DB.

I have gone through this single table in the new DB found any message with a card number in and have truncated the message (removed the card number) and updated the message.
total messages updated 25000 (approx).

I backup the new DB (single table) run it through the card checker program and it still finds about 50 or so cards that i have removed. I check the message the table and it the card number is not there.
Am i doing something silly.

Card program is called Card Recon (Ground labs)

What kind of encryption is it doing on the card number? It sounds like the card checker app is simply finding values which aren’t encrypted very strongly.

Im am using Blowfish as the encryption algorythm.

Its not just the encryption its also the truncation. I have removed the number from a message updated it, when iview the table the record is updated (minus the number).
deattach the db run the mdf through the scanner it still finds a few NOT ALL. so out of 500 updates it still find about 50 or so numbers.

Another very strange thing is i have dropped the only table from the db and run the mdf file through the scanner and it still finds a few entries.

OK, I think I know what’s going on. When you are making these updates pages are probably being deallocated from the table. When SQL Server deallocates the page it doesn’t wipe out the data on the page. That job is left for when the page is re-allocated for the new object. I’m guessing that the values you are seeing are sitting on deallocated pages that haven’t been reused.

Normally I wouldn’t recommend shrinking the database, but that will be a good test. Do a full shrink of the database so that there is no free space left then scan the mdf file again and see what you find. By shrink the file you’ll cause the SQL Server to shift the data pages around so that it can release pages back as free space to the OS so that will either overwrite all the pages that have the old data in them, or cause those pages to be released back to the disk.

Then expand the data file back to the original size which will cause those data pages to be picked back up and overwritten with 0s.

Thank you for all your help, i will try the shrinking method and see what happens.

I know how to shrink, but how do i expand the data file (im using SQL 2000).

Use the ALTER DATABASE statement to expand the file back out.

ALTER DATABASE MyDatabase
MODIFY FILE (name='MyDatabase_Data', SIZE=1024MB)
GO