SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 What's left in the transaction log in the backup
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MorrisK
Yak Posting Veteran

82 Posts

Posted - 10/08/2013 :  18:50:25  Show Profile  Reply with Quote
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

82 Posts

Posted - 10/09/2013 :  21:41:17  Show Profile  Reply with Quote
This article http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx 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?

Edited by - MorrisK on 10/09/2013 21:42:31
Go to Top of Page

robvolk
Most Valuable Yak

USA
15655 Posts

Posted - 10/10/2013 :  07:14:20  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3557 Posts

Posted - 10/10/2013 :  08:00:27  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000