Hi,

I make a backup file from my database and than I opened it in notepad.
I find some data that I can't reach with query. I searched for this data in every varchar and text column but I don't find that.
I scripted all database with data but still not find.
This database is very old and this unavailable data is from 2000. Now the database run on sql 2012 server.
I want to remove this data from the database. I tried to shrink the database or allocate the free space to 0% but nothing happened, the data is still there in the backup and the mdf file too.

Any idea?

Hi,

I make a backup file from my database and than I opened it in notepad.
I find some data that I can't reach with query. I searched for this data in every varchar and text column but I don't find that.
I scripted all database with data but still not find.
This database is very old and this unavailable data is from 2000. Now the database run on sql 2012 server.
I want to remove this data from the database. I tried to shrink the database or allocate the free space to 0% but nothing happened, the data is still there in the backup and the mdf file too.

Any idea?

• Merged by Eric__ZhangMicrosoft contingent staff, Moderator 41 minutes ago same thread from one op

Are you saying that you are seeing data in .bak file which is not there in any table? I have seen that in earlier version of SQL. What's the version of SQL Server you are talking about?

Please avaoid double posts: https://social.msdn.microsoft.com/Forums/en-US/e4faf126-cd0b-4af2-81b1-1455aa316188/unavailable-data-in-backup-file?forum=sqlsecurity

Now I use sql2012 but the origin of the database is older than 15 years. I upgraded that every newer sql server.

I've never heard of anyone wanting to open a backup file and look for data. Usually you'd just query :)

I can understand if you have to expunge court records or something like that but that still seems like it's a bit of overkill.

• Do you know the page #'s?
• Can you read the page with DBCC PAGE and see the data?
• Do you know what table the data used to be in?
• Does the table still exist?
• What if you truncate the table?
• What if you drop and recreate the table?

I can guarantee one way to get rid of the data:

1. Migrate all the current data to a new database. :)

I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

I posted in the other thread.

Are you using Trace flag 661 (Disable the ghost record removal process) by any chance?

I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

I recall these types of discussions. I don't think that there is a way to guarantee that data is physically removed. There are ghost-records, de-allocated (but not physically overwritten) pages and extents, log records and whatnot. There certainly is no command to "wipe" a database. You would have to try a mixture of stuff, among other DBCC commands (like CLEANTABLE, bot others as well). And you will never, in the end, know that you got it all.

As Daniel says, create new database and move over all data by means of (BULK) INSERT. Tedious, but supported and it works.

Far less supported, open the MDF file in hax editor, and zero out the sensitive data. You also need to clear the checksum/torn page bit. Possibly OrcasMDF could make this a little simpler. This is definitely not an operation for the man in the street, and possibly even more tedious.

Unless the data is too sensitive, I would not touch MDF and play with it. I would go with moving data out to different database by Select Into and other methods.

I did further research and found that this issue was fixed somewhere in June 2007 timeframe for SQL 2000 and later versions. If you apply the patch, the issue will not happen for future data insertions. Whatever happened before fix has to be cleaned up by methods defined earlier.

You may also want to look at this query:

select OBJECT_NAME(object_id) as obj, * 
from sys.dm_db_index_operational_stats(db_id(), null, null, null) 
WHERE leaf_ghost_count > 0

*Just make sure you're in the right DB.

I hope you found this post helpful! If you did please vote it as helpful on the left. If it answered your question please mark it as the answer below. :)