Use PowerShell to copy a database to a new logical 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 logical 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 logical server

# Connect-AzAccount -Environment AzureChinaCloud
# The SubscriptionId in which to create these objects
$subscriptionId = "<Subscription-ID>"
# Set the resource group name and location for your source server
$sourceResourceGroupName = "mySourceResourceGroup-$(Get-Random)"
$sourceResourceGroupLocation = "chinaeast2"
# Set the resource group name and location for your target server
$targetResourceGroupName = "myTargetResourceGroup-$(Get-Random)"
$targetResourceGroupLocation = "chinanorth2"
# Set an admin login and password for your server
$adminSqlLogin = "<admin>"
$password = "<password>"
# 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

# Build the SQL administrator credential reused for both servers
$adminCredential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminSqlLogin, (ConvertTo-SecureString -String $password -AsPlainText -Force)

# Create a server with a system-wide unique server name
$sourceServerParams = @{
    ResourceGroupName           = $sourceResourceGroupName
    ServerName                  = $sourceServerName
    Location                    = $sourceResourceGroupLocation
    SqlAdministratorCredentials = $adminCredential
}
$sourceResourceGroup = New-AzSqlServer @sourceServerParams
$targetServerParams = @{
    ResourceGroupName           = $targetResourceGroupName
    ServerName                  = $targetServerName
    Location                    = $targetResourceGroupLocation
    SqlAdministratorCredentials = $adminCredential
}
$targetResourceGroup = New-AzSqlServer @targetServerParams

# Create a server firewall rule that allows access from the specified IP range
$sourceFirewallParams = @{
    ResourceGroupName = $sourceResourceGroupName
    ServerName        = $sourceServerName
    FirewallRuleName  = "AllowedIPs"
    StartIpAddress    = $sourceStartIp
    EndIpAddress      = $sourceEndIp
}
$sourceServerFirewallRule = New-AzSqlServerFirewallRule @sourceFirewallParams
$targetFirewallParams = @{
    ResourceGroupName = $targetResourceGroupName
    ServerName        = $targetServerName
    FirewallRuleName  = "AllowedIPs"
    StartIpAddress    = $targetStartIp
    EndIpAddress      = $targetEndIp
}
$targetServerFirewallRule = New-AzSqlServerFirewallRule @targetFirewallParams

# Create a blank database in the source-server with an S0 performance level
$sourceDatabaseParams = @{
    ResourceGroupName             = $sourceResourceGroupName
    ServerName                    = $sourceServerName
    DatabaseName                  = $sourceDatabaseName
    RequestedServiceObjectiveName = "S0"
}
$sourceDatabase = New-AzSqlDatabase @sourceDatabaseParams

# Copy source database to the target server
$databaseCopyParams = @{
    ResourceGroupName     = $sourceResourceGroupName
    ServerName            = $sourceServerName
    DatabaseName          = $sourceDatabaseName
    CopyResourceGroupName = $targetResourceGroupName
    CopyServerName        = $targetServerName
    CopyDatabaseName      = $targetDatabaseName
}
$databaseCopy = New-AzSqlDatabaseCopy @databaseCopyParams

# 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 new logical 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.

Related content

  • Last updated on