February 8, 2008 at 10:23 am
After deleting rows from a table (using DELETE or TRUNCATE TABLE) they no longer appear when I run a query on the table. However, if I backup the database and do a text search on the backup file I can still see some of the deleted data. I thought this might be due to the deletion being recorded in the transaction log, so I detached the database and did the text search on the mdf file. The deleted data is still there too. I've tried a number of checkpoint/shrink/backup options but still get the deleted data there when I do the text search.
How do I create a backup file, or detach the database, so that the files have only the data that really still exists in the database?
Thanks for any help you can offer.
February 8, 2008 at 10:35 am
I am not sure if i am right, so anyone pls correct me if am wrong..
Try using begin tran and then execute truncate statement and then commit the transaction using commit tran. Then take backup and see if its there..
[font="Verdana"]- Deepak[/font]
February 10, 2008 at 5:36 am
Whe SQL deletes a row, it doesn't overwrite the entry on the data page, it just marks the space as available (pretty much same thing as on a file system when you delete a file)
The spot on the page will get overwritten sometime in the future when new rows get inserted, old rows get updated or when the relevant indexes are rebuilt.
The values appear on the page, but SQL does know that they're not valid rows. There's no need to be concerned about them appearing there
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
February 10, 2008 at 6:24 am
If SQL zeroed out the data pages when you deleted data then deletes would take a while 🙂 It's the same reason why you can "undelete" files on your file system if they haven't been overwritten in the meantime. A "secure delete" program does the write-over deliberately at the time of flagging the file as deleted.
For SQL, you could shrink your data files down as far as they can go. Then you could grow them back out to where they need to be. Alternatively you could use BCP to export your data to files and back them up instead. SSIS could be used for the same purpose - create another database that's empty, fill it, back it up.
February 10, 2008 at 8:58 am
Is there a reason why you're concerned about this?
February 12, 2008 at 2:10 am
I'm not that bothered myself. However, I need a copy of a client's database but he's reluctant to give me a copy, unless its purged of all sensitive information. And he's going as far as searching through the backup file using a text editor. Despite giving him scripts to delete information from sensitive tables, and all of the truncation options, etc. he's still finding data in the backup file, and refusing to send it in.
February 12, 2008 at 2:27 am
*sigh* Users... 😉
Suggest to him that he scripts the data structure out and then uses BCP to export non-sensitive data. He can then send you the scripts and the exported data files. Is far more time consuming, but at least there won't be the possibility that you see something he doesn't want you to see.
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
February 12, 2008 at 12:30 pm
...and don't be surprised if some useful keys are wiped out in the process or cleansing the confidential data.....
Creating a brand new database and copying the objects should work as well.
----------------------------------------------------------------------------------
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?
February 13, 2008 at 3:49 am
There is an innovative process called 'Non Disclosure Agreement' that can get round this problem. (OK, it was innovative when it was invented many decades ago...)
If the cilent's data is ultra-sensitive, they should already be encrypting it. If they do not believe it is important enough to encrypt, they should be happy to let you have a copy once you have a NDA in place.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply