Powershell script with dbatools – Copy Database with Rename

Last week I was called to create an automated database copy job, which should refresh a development environment every night with a full backup from the production environment. Here are the given requirements from the customer:

• Currently approx. 35 GB
• Backup w / copy only
• No backup of the backup
• Creation of database duplication as an automatic job, attachment, authorization
• Daily at 21:21 hour
• From: SourceServer, DB instance Source_DatabaseName
• To: DestinationServer, DB instance Destination_DatabaseName
• Path: DestinantionServer B:\Backup\Source_DatabaseName
• Authorization for user: xyz@customerdomain.net

My first thoughts about that were creating a SQL Server Agent Job with following steps:

1. check the availability of Shared-Destination-Folder
2. delete/clear Destination-Folder-Content
3. Backup all Databases from given list
4. Restore each Backup-File from folder
   1. Rename data- and transaction log file
   2. Rename database
5. Check all orphaned user

As you can see and imagine yourself this might be much more work… thinking about all those single tasks, might take some time to create such jobs-steps. That was not what I want and what I’m having time for… so there must be another quick and easy solution. How about doing this in Powershell…

First I thought about using the function „Copy-SqlDatabase“ but with that function, it was not possible to rename both files and the database itself. So I had to ask the dbatools-team from Chrissy LeMaire ( @psdbatools | @cl ) if there might be a way how to solve my problem with Powershell ideally with dbatools.

@SQL_aus_HH @cl https://t.co/f8dCrhm4GU is more flexible – use the DatabaseName param :).

— dbatools (@psdbatools) 2. März 2017

I tried a little bit with given function and how to use the Backup-DbaDatabase-Function and named Restore-DbaDatabase (there was actually no documentation on the website I had to look into the function itself) but got what I need … about an hour later I reached my finish line and had a working script. Here you are => some Variables to be flexible and a bit of „clean up“, this one works for me anyone else might want some additional steps/line around.

$Source_DatabaseName = 'Source_DatabaseName'
$Destination_DatabaseName = 'Destination_DatabaseName'
$Network_Transfer_Folder = '\\DestinationServer\Backup\DB_TRANSFER'
$Local_Transfer_Folder = 'P:\DB_TRANSFER'

if (-not (Get-Module -Name dbatools)) {
      Import-Module Z:\dba_script_folder\dbatools-master\dbatools.psd1
}

Get-ChildItem -Path $Local_Transfer_Folder -Include *.* -File -Recurse | foreach { $_.Delete() }

Backup-DbaDatabase -SqlInstance SourceServer -Databases $Source_DatabaseName -Type Full -BackupDirectory $Network_Transfer_Folder

Restore-DbaDatabase -SqlServer DestinationServer -Path $Local_Transfer_Folder -DatabaseName $Destination_DatabaseName -DestinationFilePrefix 'transfered_' -WithReplace -UseDestinationDefaultDirectories

Repair-SqlOrphanUser -SqlServer DestinationServer -Databases $Destination_DatabaseName

1. check if dbotools-module is installed if not install them
2. clean up the transfer share
3. Create a Full-Copy-only Backup from Database to Network Share
4. Restore all Backups to instance default paths from that share, rename it and add a ‚transfered_‘ to datafile and logfile
5. Repair orphaned user for that database

Now I’m triggering the script every day with Windows scheduled tasks and my customer is happy (me too) !!!

Nice and easy 4 lines of real code (without preparation 😉 ) can make DBAs life much easier. Give dbatools.io a chance… it is a really great tool and you should have it in your DBA toolbelt.