Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MorrisK
Yak Posting Veteran
83 Posts |
 Posted - 2013-10-08 : 18:50:25
|
| I need to send our database to a third party so they can use our data to test some problems with their program.I updated some sensitive data in some tables so it would not be meaningful.I backed up the database and log files then shrunk the log file to reduce the size. Then I made another compressed database backup to send to them.Now that the log file has been backed up, shrunk, and backed up again with the database is there any way the log file in the backup could be used to restore the sensitive data that I don't want them to see? |
|
|
MorrisK
Yak Posting Veteran
83 Posts |
Posted - 2013-10-09 : 21:41:17
|
| This article [url]http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx [/url] states When a VLF is truncated, it is not overwritten or zeroed in any way—it is just marked as truncated and can then be reused.This makes it sound like there is a possibility that these log records still exist in the log file included in the last full backup and could be read. If so, what is the best way to send the database so the updates cannot be read or undone? |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-10-10 : 07:14:20
|
| The log will contain the before and after image of the data that was updated. A more secure way to do what you describe is send a full or differential backup only. Although it's probably easier to output just the tables they need using bcp or SSIS into a text file, which they load themselves.Another option is to script INSERT statements with the relevant data and send those. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-10 : 08:00:27
|
| I am a little confused by your process. What I would do is the following:1. Make all the changes that you want to the database to remove the sensitive data. Make sure all the transactions are committed and that there are no open transactions.2. Take a full backup of the database.3. Send only this full backup. Do not send a backup of the transaction log.If you do it that way, when the third party restores the database, it should not have any trace of the original sensitive data. However, I am not database forensic expert, so if someone is able to demonstrate otherwise, I stand corrected. |
|
|
|
|
|
|
What's left in the transaction log in the backup