使用 PowerShell 将 SQL 数据库复制到新服务器

此 PowerShell 脚本示例在新服务器中创建现有数据库的副本。

如果没有 Azure 订阅,可在开始前创建一个试用帐户

本教程需要 Azure PowerShell 模块 5.7.0 或更高版本。 运行 Get-Module -ListAvailable AzureRM 即可查找版本。 如果需要进行升级,请参阅 Install Azure PowerShell module(安装 Azure PowerShell 模块)。 此外,还需要运行 Connect-AzureRmAccount -EnvironmentName AzureChinaCloud 以创建与 Azure 的连接。

将数据库复制到新服务器

# Login-AzureRmAccount -EnvironmentName AzureChinaCloud
# Set the resource group name and location for your source server
$sourceresourcegroupname = "mySourceResourceGroup-$(Get-Random)"
$sourcelocation = "China East"
# Set the resource group name and location for your target server
$targetresourcegroupname = "myTargetResourceGroup-$(Get-Random)"
$targetlocation = "China East"
# Set an admin login and password for your server
$adminlogin = "ServerAdmin"
$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"

# Create two new resource groups
$sourceresourcegroup = New-AzureRmResourceGroup -Name $sourceresourcegroupname -Location $sourcelocation
$targetresourcegroup = New-AzureRmResourceGroup -Name $targetresourcegroupname -Location $targetlocation

# Create a server with a system wide unique server name
$sourceresourcegroup = New-AzureRmSqlServer -ResourceGroupName $sourceresourcegroupname `
    -ServerName $sourceservername `
    -Location $sourcelocation `
    -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))
$targetresourcegroup = New-AzureRmSqlServer -ResourceGroupName $targetresourcegroupname `
    -ServerName $targetservername `
    -Location $targetlocation `
    -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))

# Create a server firewall rule that allows access from the specified IP range
$sourceserverfirewallrule = New-AzureRmSqlServerFirewallRule -ResourceGroupName $sourceresourcegroupname `
    -ServerName $sourceservername `
    -FirewallRuleName "AllowedIPs" -StartIpAddress $sourcestartip -EndIpAddress $sourceendip
$targetserverfirewallrule = New-AzureRmSqlServerFirewallRule -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-AzureRmSqlDatabase  -ResourceGroupName $sourceresourcegroupname `
    -ServerName $sourceservername `
    -DatabaseName $sourcedatabasename -RequestedServiceObjectiveName "S0"

# Copy source database to the target server 
$databasecopy = New-AzureRmSqlDatabaseCopy -ResourceGroupName $sourceresourcegroupname `
    -ServerName $sourceservername `
    -DatabaseName $sourcedatabasename `
    -CopyResourceGroupName $targetresourcegroupname `
    -CopyServerName $targetservername `
    -CopyDatabaseName $targetdatabasename 

# Clean up deployment 
# Remove-AzureRmResourceGroup -ResourceGroupName $sourceresourcegroupname
# Remove-AzureRmResourceGroup -ResourceGroupName $targetresourcegroupname

清理部署

运行脚本示例后,可以使用以下命令删除资源组以及与其关联的所有资源。

Remove-AzureRmResourceGroup -ResourceGroupName $sourceresourcegroupname
Remove-AzureRmResourceGroup -ResourceGroupName $targetresourcegroupname

脚本说明

此脚本使用以下命令。 表中的每条命令均链接到特定于命令的文档。

命令 注释
New-AzureRmResourceGroup 创建用于存储所有资源的资源组。
New-AzureRmSqlServer 创建用于托管数据库或弹性池的逻辑服务器。
New-AzureRmSqlDatabase 在逻辑服务器中创建数据库作为单一数据库或入池数据库。
New-AzureRmSqlDatabaseCopy 创建当前使用快照的数据库副本。
Remove-AzureRmResourceGroup 删除资源组,包括所有嵌套的资源。

后续步骤

有关 Azure PowerShell 的详细信息,请参阅 Azure PowerShell 文档

可以在 Azure SQL 数据库 PowerShell 脚本中找到更多 SQL 数据库 PowerShell 脚本示例。