The Adventure Begins

Powershell script with dbatools – Copy Database with Rename

This post might contain affiliate links. We may earn a commission if you click and make a purchase. Your support is appreciated!

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:

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…

Powershell Icon

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.

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 a chance… it is a really great tool and you should have it in your DBA toolbelt.

This post might contain affiliate links. We may earn a commission if you click and make a purchase. Your support is appreciated!

Ähnliche Beiträge

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden.