Use PowerShell to copy a database to a new server
Applies to: Azure SQL Database
This Azure PowerShell script example creates a copy of an existing database in Azure SQL Database in a new server.
If you don't have an Azure trail subscription, create a trial subscription before you begin.
Note
This article uses the Azure Az PowerShell module, which is the recommended PowerShell module for interacting with Azure. To get started with the Az PowerShell module, see Install Azure PowerShell. To learn how to migrate to the Az PowerShell module, see Migrate Azure PowerShell from AzureRM to Az.
This tutorial requires Az PowerShell 1.4.0 or later. If you need to upgrade, see Install Azure PowerShell module. You also need to run Connect-AzAccount -EnvironmentName AzureChinaCloud
to create a connection with Azure.
Copy a database to a new server
# Connect-AzAccount -Environment AzureChinaCloud
# The SubscriptionId in which to create these objects
$SubscriptionId = ''
# Set the resource group name and location for your source server
$sourceResourceGroupName = "mySourceResourceGroup-$(Get-Random)"
$sourceResourceGroupLocation = "chinaeast"
# Set the resource group name and location for your target server
$targetResourceGroupname = "myTargetResourceGroup-$(Get-Random)"
$targetResourceGroupLocation = "chinanorth"
# Set an admin login and password for your server
$adminSqlLogin = "SqlAdmin"
$password = "ChangeYourAdminPassword1"
# The logical server names have to be unique in the system
$sourceServerName = "source-server-$(Get-Random)"
$targetServerName = "target-server-$(Get-Random)"
# The sample database name
$sourceDatabaseName = "mySampleDatabase"
$targetDatabaseName = "CopyOfMySampleDatabase"
# The ip address range that you want to allow to access your servers
$sourceStartIp = "0.0.0.0"
$sourceEndIp = "0.0.0.0"
$targetStartIp = "0.0.0.0"
$targetEndIp = "0.0.0.0"
# Set subscription
Set-AzContext -SubscriptionId $subscriptionId
# Create two new resource groups
$sourceResourceGroup = New-AzResourceGroup -Name $sourceResourceGroupName -Location $sourceResourceGroupLocation
$targetResourceGroup = New-AzResourceGroup -Name $targetResourceGroupname -Location $targetResourceGroupLocation
# Create a server with a system wide unique server name
$sourceResourceGroup = New-AzSqlServer -ResourceGroupName $sourceResourceGroupName `
-ServerName $sourceServerName `
-Location $sourceResourceGroupLocation `
-SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminSqlLogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))
$targetResourceGroup = New-AzSqlServer -ResourceGroupName $targetResourceGroupname `
-ServerName $targetServerName `
-Location $targetResourceGroupLocation `
-SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminSqlLogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))
# Create a server firewall rule that allows access from the specified IP range
$sourceServerFirewallRule = New-AzSqlServerFirewallRule -ResourceGroupName $sourceResourceGroupName `
-ServerName $sourceServerName `
-FirewallRuleName "AllowedIPs" -StartIpAddress $sourcestartip -EndIpAddress $sourceEndIp
$targetServerFirewallRule = New-AzSqlServerFirewallRule -ResourceGroupName $targetResourceGroupname `
-ServerName $targetServerName `
-FirewallRuleName "AllowedIPs" -StartIpAddress $targetStartIp -EndIpAddress $targetEndIp
# Create a blank database in the source-server with an S0 performance level
$sourceDatabase = New-AzSqlDatabase -ResourceGroupName $sourceResourceGroupName `
-ServerName $sourceServerName `
-DatabaseName $sourceDatabaseName -RequestedServiceObjectiveName "S0"
# Copy source database to the target server
$databaseCopy = New-AzSqlDatabaseCopy -ResourceGroupName $sourceResourceGroupName `
-ServerName $sourceServerName `
-DatabaseName $sourceDatabaseName `
-CopyResourceGroupName $targetResourceGroupname `
-CopyServerName $targetServerName `
-CopyDatabaseName $targetDatabaseName
# Clean up deployment
# Remove-AzResourceGroup -ResourceGroupName $sourceResourceGroupName
# Remove-AzResourceGroup -ResourceGroupName $targetResourceGroupname
Clean up deployment
Use the following command to remove the resource group and all resources associated with it.
Remove-AzResourceGroup -ResourceGroupName $sourceresourcegroupname
Remove-AzResourceGroup -ResourceGroupName $targetresourcegroupname
Script explanation
This script uses the following commands. Each command in the table links to command specific documentation.
Command | Notes |
---|---|
New-AzResourceGroup | Creates a resource group in which all resources are stored. |
New-AzSqlServer | Creates a server that hosts databases and elastic pools. |
New-AzSqlDatabase | Creates a database or elastic pool. |
New-AzSqlDatabaseCopy | Creates a copy of a database that uses the snapshot at the current time. |
Remove-AzResourceGroup | Deletes a resource group including all nested resources. |
Next steps
For more information on Azure PowerShell, see Azure PowerShell documentation.
Additional SQL Database PowerShell script samples can be found in the Azure SQL Database PowerShell scripts.