Updating SQL 2000 Data

155 pts.
Tags:
MDF File
SQL 2000
SQL data
SQL Database
Hi I have a database with test card numbers, I have encrytpted the card numbers and updated the database, when i run a card scanner program on my MDF file it still finds the cards that i have updated. how can i get rid of these permanently. I have a deadline to apply this same principle to a Production database. Would really appreciate some help on this please. Thanks

Software/Hardware used:
SQL 2000

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 9  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    How exactly have you encrypted the data in the table? What is this card scanner program that you refer to?
    66,190 pointsBadges:
    report
  • KH
    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.
    155 pointsBadges:
    report
  • KH
    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)
    155 pointsBadges:
    report
  • Denny Cherry
    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.
    66,190 pointsBadges:
    report
  • KH
    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.
    155 pointsBadges:
    report
  • Denny Cherry
    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.
    66,190 pointsBadges:
    report
  • KH
    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).
    155 pointsBadges:
    report
  • Denny Cherry
    Use the ALTER DATABASE statement to expand the file back out.
    ALTER DATABASE MyDatabase
    MODIFY FILE (name='MyDatabase_Data', SIZE=1024MB)
    GO
    66,190 pointsBadges:
    report
  • KH
    [...] 1. Mrdenny and KH discuss updating SQL 2000 data. [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following