Copy Production SQL Database to Development

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
module dbatools.

What is this script doing?

  1. 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.
  2. Remove the database from dev (you’ll get prompted before this happens).
  3. Copy the database from prd to dev.
  4. Add in the development database users you need. You’ll need to edit this bit with the account name.
  5. Remove the production database users. Again you’ll need to edit this.

The Script

## 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"