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.
Table of Contents
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:
- Creates a target resource group if it does not exist
- Creates a target logical SQL server if it does not exist
- Creates a firewall rule if it does not exist and parameters targetStartIp and targetEndIp have been defined
- 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
