SQL Server 2005 backup file

Question

SQL Server 2005 backup file

HBK_4700

SSCrazy Eights

Points: 8932
More actions
July 1, 2010 at 2:43 pm

#145756

How to mask the imp data on production database backup file

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Restore it, do the necessary changes, take another backup.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 2

We have 250+ tables, and in every table we have user name and data etc....

So you want to update all tables which has user data? or any other way

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

laddu4700 (7/1/2010)
So you want to update all tables which has user data?

Yup. You'll only have to write the script once, besides you should be able to use the system tables to generate the script for you.

or any other way

Take a hex editor to the backup file. Assuming that you're feeling brave and don't mind probably making the backup unrestorable.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736632 More actions · Answer 4

Steve Jones - SSC Editor

SSC Guru

Points: 736632

July 1, 2010 at 3:09 pm

#1187929

What do you mean by "mask imp data"?

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 5

I don't want to provide user data like name ssn address...

Steve Jones - SSC Editor SSC Guru Points: 736632 More actions · Answer 6

Why would you want to mask this in a backup file? What if you have a disaster? What if you need to restore? You need that data in a backup.

If you are making a backup that you are sending to someone for a reason, as Gail mentioned, you'd need to go through and make the changes. SQL Server has no concept of "important" data. You'll have to script something to obfuscate or change the data. However once you do this, this script can be handy to re-use in the future.

Ray Mond SSCertifiable Points: 7159 More actions · Answer 7

laddu4700 (7/1/2010)
How to mask the imp data on production database backup file

I take it that your concern lies with the fact that you can simply open a SQL Server backup file with a text editor and see the 'important' contents of your database. There are a few options to address this.

If you are using SQL Server 2008 Enterprise, you could use the Transparent Data Encryption (TDE) feature. However, that would apply to the entire database, not just to specific tables.

As Gail and Steve mentioned, you could also custom-encrypt only the sensitive data. Depending on the sort of applications which will access the data, this might require little or a lot of work.

The above 2 options would 'mask' your important data at the database level, so even if someone manage to get hold of your mdf/ndf files, they'll still need to work out how to decrypt the data. The data in your backup files would also be 'masked'.

If your concern is only with the backup file, you could use 3rd party SQL Server backup software, like Red Gate's SQL Backup, which allows you to encrypt backup files with passwords. SQL Server does not (yet) provide an option to encrypt backup files.

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.