Powershell script with dbatools – Copy Database with Rename

The Adventure Begins

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…

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 = '<em>Source_DatabaseName</em>'
$Destination_DatabaseName = '<em>Destination_DatabaseName</em>'
$Network_Transfer_Folder = '\\<em>DestinationServer</em>\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 <em>SourceServer </em>-Databases $Source_DatabaseName -Type Full -BackupDirectory $Network_Transfer_Folder
 
Restore-DbaDatabase -SqlServer <em>DestinationServer </em>-Path $Local_Transfer_Folder -DatabaseName $Destination_DatabaseName -DestinationFilePrefix 'transfered_' -WithReplace -UseDestinationDefaultDirectories
 
Repair-SqlOrphanUser -SqlServer <em>DestinationServer </em>-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.

Björn arbeitet in Hamburg als Datenbank-Administrator und Head of Competence für MS SQL und mySQL. Er nimmt regelmäßig an den PASS Regionalgruppen Treffen in Hamburg, den Veranstaltungen der PASS wie SQLSaturday und SQLGrillen teil und er organisiert in Hamburg das Azure Meetup. Er interessiert sich neben den Themen rund um den SQL Server, Powershell und Azure für Science-Fiction, Snowboarden, Backen 😉 und Radfahren.

2 thoughts on “Powershell script with dbatools – Copy Database with Rename

Leave a Reply

Your email address will not be published. Required fields are marked *