[spacer] [spacer] [spacer]

Sign up with your email address to be the first to know about new publications

[spacer]

Azure SQL Copy Database to a Different Server

In today’s post I’ll share a PowerShell script so you can easily copy any Azure SQL Database to a different Azure SQL Database server. This will help you test changes in a different database from the production database or keep a copy for other development purposes.  

Pre-requirements  

To begin, before you can run the script, download the Az Module and log into the Azure tenant.   

INSTALL AZ MODULE   

Install-Module -Name Az  

CONNECT TO AZURE   

Connect-AzAccount 

Download the script 

First, download the script from my Github repository. 

What do I need to know about the script? 

The script copies a database from an existing server and: 

  1. Creates a target resource group if it does not exist 
  1. Creates a target logical SQL server if it does not exist 
  1. Creates a firewall rule if it does not exist and parameters targetStartIp and targetEndIp have been defined 
  1. Copies the database from the source server to the target server 

It uses the PowerShell Az modules. You can find similar examples in the MSDN documentation

The main idea is to have a script ready so you can use it in Azure DevOps to copy a database to multiple environments. 

Execute Script 

The script has the following parameters: 

  • SubscriptionId 
  • sourceResourceGroupName 
  • sourceServerName 
  • sourceDatabaseName 
  • targetResourceGroupname 
  • targetResourceGroupLocation 
  • targetServerName 
  • targetDatabaseName 
  • targetAdminSqlLogin 
  • targetAdminSqlPwd 

Optional: 

  • targetFirewallRuleName 
  • targetStartIp 
  • targetEndIp 

EXAMPLE WITHOUT FIREWALL RULE 

 Copy-AzSqlDatabaseDatabaseDifferentServer -SubscriptionId "XXXXXX-XXXXXX-XXXXXX-XXXXXX" ` 

    -sourceResourceGroupName "source resource group" ` 

    -sourceServerName "source Azure SQL Server name" ` 

    -sourceDatabaseName "source database name" ` 

    -targetResourceGroupname "target resource group name" ` 

    -targetResourceGroupLocation "target resource group location" ` 

    -targetServerName "target Azure SQL Server name" ` 

    -targetDatabaseName "target database name" ` 

    -targetAdminSqlLogin "target Azure SQL Server user name" ` 

    -targetAdminSqlPwd "target Azure SQL Server user password" `  

EXAMPLE WITH FIREWALL RULE 

 Copy-AzSqlDatabaseDatabaseDifferentServer -SubscriptionId "XXXXXX-XXXXXX-XXXXXX-XXXXXX" ` 

    -sourceResourceGroupName "source resource group" ` 

    -sourceServerName "source Azure SQL Server name" ` 

    -sourceDatabaseName "source database name" ` 

    -targetResourceGroupname "target resource group name" ` 

    -targetResourceGroupLocation "target resource group location" ` 

    -targetServerName "target Azure SQL Server name" ` 

    -targetDatabaseName "target database name" ` 

    -targetAdminSqlLogin "target Azure SQL Server user name" ` 

    -targetAdminSqlPwd "target Azure SQL Server user password" ` 

    -targetFirewallRuleName "target Azure SQL Server firewall rule name"` 

    -targetStartIp "target Azure SQL Server start IP rule" ` 

    -targetEndIp "target Azure SQL Server end IP rule"  

Summary and Next Steps 

In this blog post, you learned how to copy a database across different Azure SQL Database servers. The purpose is to automate this script and copy the database across multiple environments.  

In upcoming blog posts, I’ll show you how to do this using Azure DevOps. 

Follow me on Twitter for blog updates, virtual presentations, and more!   

As always, please leave any comments or questions below.   

Check out these other blog posts: 

Integrate Analysis Services and Azure DevOps

Azure Data Factory Data Consistency Verification

Block Deletion of Azure Services

comment [ 0 ]
share
No tags 0

No Comments Yet.

Do you want to leave a comment?

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