desensitize data after a restore

Question

desensitize data after a restore

PearlJammer1

SSCrazy Eights

Points: 8495
More actions
January 14, 2014 at 8:38 am

#287218

Hi everybody
We have a requirement to de-sensitize some of the data in our tables after we do a restore to our test\dev environment. We need to replace the data with some kind of meaningful test data that our software tester can run tests against.
Can anybody point me to any scripts\methodologies that may handle this type of task as it is a first for me.

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

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

Steve Jones - SSC Editor SSC Guru Points: 738632 More actions · Answer 1

Basically I think you need custom scripts that run as part of your restore process to clean up the data before people use it. Essentially follow this:

- restore db

- set restricted user

- run obfuscation script

- if works, set all users

A few links:

https://www.simple-talk.com/sql/database-administration/obfuscating-your-sql-server-data/

http://www.brentozar.com/archive/2011/09/how-do-you-mask-data/

http://www.sqlservercentral.com/Forums/Topic461773-61-1.aspx

http://documentation.red-gate.com/display/SDG3/Using+SQL+Data+Refresh

Disclosure: I work for Red Gate Software.

Perry Whittle SSC Guru Points: 234064 More actions · Answer 2

PearlJammer1 (1/14/2014)
Hi everybody
We have a requirement to de-sensitize some of the data in our tables after we do a restore to our test\dev environment. We need to replace the data with some kind of meaningful test data that our software tester can run tests against.
Can anybody point me to any scripts\methodologies that may handle this type of task as it is a first for me.

If it's a 3rd party db have you tried contacting the vendor?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 3

Here is one from a colleague that I haven't used before, so please test first:

http://www.robusthaven.com/products/production-management/SQL-Data-Obfuscator

One more for jumbledb http://www.orbiumsoftware.com

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

PearlJammer1 SSCrazy Eights Points: 8495 More actions · Answer 4

Ok thanks for the replies everyone - looks like a complicated process to implement. I'm going to have to give this some serious thought.

:-):-)

Perry Whittle SSC Guru Points: 234064 More actions · Answer 5

PearlJammer1 (1/15/2014)
looks like a complicated process to implement. I'm going to have to give this some serious thought.
:-):-)

What?

Identitfy the Live tables that require obfuscating (there's likely only a few). Build yourself a set of generic tables with meaningless names, addresses, etc and use these to obfuscate the real data 😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Lowell SSC Guru Points: 323546 More actions · Answer 6

Perry Whittle (1/15/2014)
PearlJammer1 (1/15/2014)
looks like a complicated process to implement. I'm going to have to give this some serious thought.
:-):-)
What?
Identitfy the Live tables that require obfuscating (there's likely only a few). Build yourself a set of generic tables with meaningless names, addresses, etc and use these to obfuscate the real data 😉

exactly what i do; i have a bunch of tables like Ansi color names, animal names, actor names that i use for updating existing data with meaningful but clearly obfuscated data elements.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!