December 26, 2017 at 9:58 am
What is faster, doing a backup of a database, then sending the file over the network and restoring it to a dev database or doing a detach, copying the detached MDF file and attach to the dev db?
The purpose is simply to copy a prod database to a dev database (refresh).
Assume 2016 standard edition.
December 26, 2017 at 11:27 am
steveg5000 - Tuesday, December 26, 2017 9:58 AMWhat is faster, doing a backup of a database, then sending the file over the network and restoring it to a dev database or doing a detach, copying the detached MDF file and attach to the dev db?The purpose is simply to copy a prod database to a dev database (refresh).
Assume 2016 standard edition.
They aren't functionally the same because detaching a production database so you can copy the files takes the production database offline.
I use the backup, copy and restore approach when doing this type of thing. If you use SQL Server logins, you'll probably have a SID mismatch between the users and the logins on the destination server because the start of authority is the instance. You can use sp_change_users_login to find and fix them. If the servers are on the same domain, the AD logins should be fine because the start of authority is the domain.
January 4, 2018 at 4:29 pm
Backup with compression, copy over and restore.
January 5, 2018 at 7:44 am
To be controversial, Don:t do either with the caveat of 'if it contains data pertaining to an individual or an identifiable individual', if this is the case I would script all objects and send that to the dev team.
To distribute data otherwise may put you in breach of regulations, this is more important than ever given the forthcoming GDPR regulations.
If data is needed generate test data.
...
January 5, 2018 at 7:52 am
Or, have scripts that you run against the database to obfuscate the data immediately after the restore.
January 5, 2018 at 8:09 am
Lynn Pettis - Friday, January 5, 2018 7:52 AMOr, have scripts that you run against the database to obfuscate the data immediately after the restore.
Yes, definitely this.
January 5, 2018 at 8:29 am
Lynn Pettis - Friday, January 5, 2018 7:52 AMOr, have scripts that you run against the database to obfuscate the data immediately after the restore.
+100 have done this in the past, not sure if this approach would be affected by GDPR though!
...
January 5, 2018 at 8:38 am
HappyGeek - Friday, January 5, 2018 8:29 AMLynn Pettis - Friday, January 5, 2018 7:52 AMOr, have scripts that you run against the database to obfuscate the data immediately after the restore.+100 have done this in the past, not sure if this approach would be affected by GDPR though!
We do this too, esp for prod - to -dev environment refreshes.
January 6, 2018 at 10:21 pm
Lynn Pettis - Friday, January 5, 2018 7:52 AMOr, have scripts that you run against the database to obfuscate the data immediately after the restore.
Note that you need to bring the restored database up in restricted_user mode and make sure that no non-DBA's have sysadmin permissions; that way you can guarantee the data changes are complete before you put it into multi_user mode and others can get at the data.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply