Before beginning my SQL development I like to work with the latest version of the database from production.
If your database is doing OLTP workloads then I would advise getting a copy from backups. However in this scenario I’m getting a copy of a data mart which supports a SSAS Tabular Model run on a schedule so I know to run this script without disrupting production jobs.
To do this I am using PowerShell and the amazing community built
What is this script doing?
- Firstly you’ll need to edit the variables with your server names, database name, and a network share location for the backup / restore. You’ll need to choose a network share that both SQL Instance service accounts have access to so databases can be backed up and restored from.
- Remove the database from dev (you’ll get prompted before this happens).
- Copy the database from prd to dev.
- Add in the development database users you need. You’ll need to edit this bit with the account name.
- Remove the production database users. Again you’ll need to edit this.
## Copy a SQL Database from one Instance to another. $SourceServer = "SQLPRD" $DestServer = "SQLDEV" $CopyDatabase = "DataMart" $NetworkShare = "\\Share\Folder\" ## Remove copy of the database at target dev server. Remove-DbaDatabase -SqlInstance $DestServer -Database $CopyDatabase ## Copy the database from Prd to Dev Copy-DbaDatabase -Source $SourceServer -Destination $DestServer -Database $CopyDatabase -BackupRestore -SharedPath $NetworkShare ## Add Dev DB User New-DbaDbUser -SqlInstance $DestServer -Database $CopyDatabase -Login "domain\dev_user" ## Remove Production DB User Remove-DbaDbUser -SqlInstance $DestServer -Database $CopyDatabase -User "domain\prd_user"