使用 PowerShell 从备份还原 Azure SQL 数据库
以下 PowerShell 脚本示例通过异地冗余备份还原 Azure SQL 数据库,将已删除的 Azure SQL 数据库还原为其最新备份,并将 Azure SQL 数据库还原到特定的时间点。
本示例需要 Azure PowerShell 模块 5.1.1 或更高版本。 运行 Get-Module -ListAvailable AzureRM
即可查找版本。 如果需要进行安装或升级,请参阅安装 Azure PowerShell 模块。
运行 Login-AzureRmAccount -EnvironmentName AzureChinaCloud
,创建与 Azure 的连接。
示例脚本
# Login-AzureRmAccount -EnvironmentName AzureChinaCloud
# Set the resource group name and location for your server
$resourcegroupname = "myResourceGroup-$(Get-Random)"
$location = "China North"
# Set an admin login and password for your server
$adminlogin = "ServerAdmin"
$password = "ChangeYourAdminPassword1"
# Set server name - the logical server name has to be unique in the system
$servername = "server-$(Get-Random)"
# The sample database name
$databasename = "mySampleDatabase"
# The restored database names
$georestoredatabasename = "MySampleDatabase_GeoRestore"
$pointintimerestoredatabasename = "MySampleDatabase_10MinutesAgo"
$deleteddatabaserestorename = "MySampleDatabase_DeletedRestore"
# The ip address range that you want to allow to access your server
$startip = "0.0.0.0"
$endip = "0.0.0.0"
# Create a resource group
$resourcegroup = New-AzureRmResourceGroup -Name $resourcegroupname -Location $location
# Create a server with a system wide unique server name
$server = New-AzureRmSqlServer -ResourceGroupName $resourcegroupname `
-ServerName $servername `
-Location $location `
-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
$firewallrule = New-AzureRmSqlServerFirewallRule -ResourceGroupName $resourcegroupname `
-ServerName $servername `
-FirewallRuleName "AllowedIPs" -StartIpAddress $startip -EndIpAddress $endip
# Create a blank database with an S0 performance level
$database = New-AzureRmSqlDatabase -ResourceGroupName $resourcegroupname `
-ServerName $servername `
-DatabaseName $databasename `
-RequestedServiceObjectiveName "S0"
# Restore database from latest geo-redundant backup into existing server
# Note: Check to see that backups are created and ready to restore from geo-redundant backup
# Important: If no backup exists, you will get an error indicating that no backups exist for the server specified
Get-AzureRmSqlDatabaseGeoBackup -ResourceGroupName $resourcegroupname -ServerName $servername
Get-AzureRmSqlDatabaseGeoBackup -ResourceGroupName $resourcegroupname -ServerName $servername -DatabaseName $databasename
# Do not continue until a backup exists
Restore-AzureRmSqlDatabase `
-FromGeoBackup `
-ResourceGroupName $resourcegroupname `
-ServerName $servername `
-TargetDatabaseName $georestoredatabasename `
-ResourceId $database.ResourceID `
-Edition "Standard" `
-ServiceObjectiveName "S0"
# Restore database to its state 10 minutes ago
# Note: Point-in-time restore requires database to be at least 5 minutes old
Restore-AzureRmSqlDatabase `
-FromPointInTimeBackup `
-PointInTime (Get-Date).AddMinutes(-10) `
-ResourceGroupName $resourcegroupname `
-ServerName $servername `
-TargetDatabaseName $pointintimerestoredatabasename `
-ResourceId $database.ResourceID `
-Edition "Standard" `
-ServiceObjectiveName "S0"
# Delete original database
Remove-AzureRmSqlDatabase -ResourceGroupName $resourcegroupname -ServerName $servername -DatabaseName $databasename
# Restore deleted database
# Note: Check to see that the Get-AzureRmSqlDeletedDatabaseBackup cmdlet returns a deletion date (may take a few minutes).
# Important: If no backup exists, no value will be returned.
$deleteddatabase = Get-AzureRmSqlDeletedDatabaseBackup -ResourceGroupName $resourcegroupname -ServerName $servername -DatabaseName $databasename
$deleteddatabase
# Do not continue until the cmdlet returns information about the deleted database.
Restore-AzureRmSqlDatabase -FromDeletedDatabaseBackup `
-ResourceGroupName $resourcegroupname `
-ServerName $servername `
-TargetDatabaseName $deleteddatabaserestorename `
-ResourceId $deleteddatabase.ResourceID `
-DeletionDate $deleteddatabase.DeletionDate `
-Edition "Standard" `
-ServiceObjectiveName "S0"
# Clean up deployment
# Remove-AzureRmResourceGroup -ResourceGroupName $resourcegroupname
清理部署
运行脚本示例后,可以使用以下命令删除资源组以及与其关联的所有资源。
Remove-AzureRmResourceGroup -ResourceGroupName $resourcegroupname
脚本说明
此脚本使用以下命令。 表中的每条命令均链接到特定于命令的文档。
命令 | 注释 |
---|---|
New-AzureRmResourceGroup | 创建用于存储所有资源的资源组。 |
New-AzureRmSqlDatabase | 在逻辑服务器中创建数据库作为单一数据库或入池数据库。 |
Get-AzureRmSqlDatabaseGeoBackup | 获取数据库的异地冗余备份。 |
Restore-AzureRmSqlDatabase | 还原 SQL 数据库。 |
Remove-AzureRmSqlDatabase | 删除 Azure SQL 数据库。 |
Get-AzureRmSqlDeletedDatabaseBackup | 获取可以还原的已删除数据库。 |
Remove-AzureRmResourceGroup | 删除资源组,包括所有嵌套的资源。 |
后续步骤
有关 Azure PowerShell 的详细信息,请参阅 Azure PowerShell 文档。
可以在 Azure SQL 数据库 PowerShell 脚本中找到更多 SQL 数据库 PowerShell 脚本示例。