October 28, 2014 at 1:13 pm
Hi,
When the database have sensitive data in the prod. How do you restore into non-prod environments?
What are the steps do you follow to when you restoring to Dev environments?
October 28, 2014 at 7:40 pm
The best way is to have a full script that does the restore and cleans up the database to remove or modify the data that shouldn't be accessible, all as a unit. Then, back that up and use this new backup to distribute to other environments. As an added benefit, you could also shrink the database here to make it as small as possible to help speed the restores just a little (it's not a production database, so here, shrink isn't a big deal, plus we're only talking about one).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 29, 2014 at 12:48 pm
Grant Fritchey (10/28/2014)
The best way is to have a full script that does the restore and cleans up the database to remove or modify the data that shouldn't be accessible, all as a unit. Then, back that up and use this new backup to distribute to other environments. As an added benefit, you could also shrink the database here to make it as small as possible to help speed the restores just a little (it's not a production database, so here, shrink isn't a big deal, plus we're only talking about one).
If you can get away with just removing the sensitive data - might be worth considering *moving* the sensitive content out of that DB entirely: turn the DB into a "sensitive data" DB and a "public data" DB. That would make the job easiest of all.
No doubt that's often not possible, so modifying the sensitive data (i.e. overwriting the real data with something bogus) ends up being the only option.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 29, 2014 at 3:57 pm
Matt Miller (#4) (10/29/2014)
Grant Fritchey (10/28/2014)
The best way is to have a full script that does the restore and cleans up the database to remove or modify the data that shouldn't be accessible, all as a unit. Then, back that up and use this new backup to distribute to other environments. As an added benefit, you could also shrink the database here to make it as small as possible to help speed the restores just a little (it's not a production database, so here, shrink isn't a big deal, plus we're only talking about one).If you can get away with just removing the sensitive data - might be worth considering *moving* the sensitive content out of that DB entirely: turn the DB into a "sensitive data" DB and a "public data" DB. That would make the job easiest of all.
No doubt that's often not possible, so modifying the sensitive data (i.e. overwriting the real data with something bogus) ends up being the only option.
Absolutely true, but one heck of a lot harder to do. Have you used this method?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 29, 2014 at 9:58 pm
Grant Fritchey (10/29/2014)
Matt Miller (#4) (10/29/2014)
Grant Fritchey (10/28/2014)
The best way is to have a full script that does the restore and cleans up the database to remove or modify the data that shouldn't be accessible, all as a unit. Then, back that up and use this new backup to distribute to other environments. As an added benefit, you could also shrink the database here to make it as small as possible to help speed the restores just a little (it's not a production database, so here, shrink isn't a big deal, plus we're only talking about one).If you can get away with just removing the sensitive data - might be worth considering *moving* the sensitive content out of that DB entirely: turn the DB into a "sensitive data" DB and a "public data" DB. That would make the job easiest of all.
No doubt that's often not possible, so modifying the sensitive data (i.e. overwriting the real data with something bogus) ends up being the only option.
Absolutely true, but one heck of a lot harder to do. Have you used this method?
I've kind of had to in one specific scenario (HIPAA-related, etc...). It was quite ugly in a few places (had to sacrifice a few FK's and switch over to other enforcement methods), but it still turned out to be a better way to externalize some data which previously had fairly cozy with a lot of stuff we just shouldn't let out. That was one of the jobs that didn't keep me up at might when I transitioned out of that particular job a few years later, so in the end it felt worth the time sink to separate the two..
I ran into similar scenarios a bit later, but then I had a chance to build the initial designs with it in mind.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply