使用 PowerShell 将 Azure SQL 单一数据库还原到更早的时间点Use PowerShell to restore an Azure SQL single database to an earlier point in time

此 PowerShell 脚本示例将 Azure SQL 数据库还原到特定的时间点。This PowerShell script example restores an Azure SQL database to a specific point in time.

如果没有 Azure 订阅,可在开始前创建一个试用帐户If you don't have an Azure subscription, create a trial account before you begin.

Note

本文进行了更新,以便使用新的 Azure PowerShell Az 模块。This article has been updated to use the new Azure PowerShell Az module. 你仍然可以使用 AzureRM 模块,至少在 2020 年 12 月之前,它将继续接收 bug 修补程序。You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020. 若要详细了解新的 Az 模块和 AzureRM 兼容性,请参阅新 Azure Powershell Az 模块简介To learn more about the new Az module and AzureRM compatibility, see Introducing the new Azure PowerShell Az module. 有关 Az 模块安装说明,请参阅安装 Azure PowerShellFor Az module installation instructions, see Install Azure PowerShell.

本教程需要 AZ PowerShell 1.4.0 或更高版本。This tutorial requires AZ PowerShell 1.4.0 or later. 如果需要进行升级,请参阅 Install Azure PowerShell module(安装 Azure PowerShell 模块)。If you need to upgrade, see Install Azure PowerShell module. 此外,还需要运行 Connect-AzAccount -EnvironmentName AzureChinaCloud 以创建与 Azure 的连接。You also need to run Connect-AzAccount -EnvironmentName AzureChinaCloud to create a connection with Azure.

示例脚本Sample script

# Connect-AzAccount -Environment AzureChinaCloud
$SubscriptionId = ''
# Set the resource group name and location for your server
$resourceGroupName = "myResourceGroup-$(Get-Random)"
$location = "chinaeast"
# Set an admin login and password for your server
$adminSqlLogin = "SqlAdmin"
$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
$restoreDatabaseName = "MySampleDatabase_GeoRestore"
$pointInTimeRestoreDatabaseName = "MySampleDatabase_10MinutesAgo"
# The ip address range that you want to allow to access your server
$startIp = "0.0.0.0"
$endIp = "0.0.0.0"

# Set subscription 
Set-AzContext -SubscriptionId $subscriptionId 

# Create a resource group
$resourceGroup = New-AzResourceGroup -Name $resourceGroupName -Location $location

# Create a server with a system wide unique server name
$server = New-AzSqlServer -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -Location $location `
    -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
$firewallRule = New-AzSqlServerFirewallRule -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -FirewallRuleName "AllowedIPs" -StartIpAddress $startIp -EndIpAddress $endIp

# Create a blank database with an S0 performance level
$database = New-AzSqlDatabase  -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -DatabaseName $databaseName `
    -RequestedServiceObjectiveName "S0" 

Start-Sleep -second 600

# Restore database to its state 7 minutes ago
# Note: Point-in-time restore requires database to be at least 5 minutes old
Restore-AzSqlDatabase `
      -FromPointInTimeBackup `
      -PointInTime (Get-Date).AddMinutes(-2) `
      -ResourceGroupName $resourceGroupName `
      -ServerName $serverName `
      -TargetDatabaseName $pointInTimeRestoreDatabaseName `
      -ResourceId $database.ResourceID `
      -Edition "Standard" `
      -ServiceObjectiveName "S0"

清理部署Clean up deployment

使用以下命令删除资源组及其相关的所有资源。Use the following command to remove the resource group and all resources associated with it.

Remove-AzResourceGroup -ResourceGroupName $resourcegroupname

脚本说明Script explanation

此脚本使用以下命令。This script uses the following commands. 表中的每条命令均链接到特定于命令的文档。Each command in the table links to command specific documentation.

命令Command 注释Notes
New-AzResourceGroupNew-AzResourceGroup 创建用于存储所有资源的资源组。Creates a resource group in which all resources are stored.
New-AzSqlServerNew-AzSqlServer 创建托管单一数据库或弹性池的 SQL 数据库服务器。Creates a SQL Database server that hosts a single database or elastic pool.
New-AzSqlDatabaseNew-AzSqlDatabase 在 SQL 数据库服务器中创建数据库作为独立数据库或共用数据库。Creates a database in a SQL Database server as a standalone or a pooled database.
Get-AzSqlDatabaseGeoBackupGet-AzSqlDatabaseGeoBackup 获取独立数据库或共用数据库的异地冗余备份。Gets a geo-redundant backup of a standalone or pooled database.
Restore-AzSqlDatabaseRestore-AzSqlDatabase 还原 SQL 独立数据库或共用数据库。Restores a SQL standalone or pooled database.
Remove-AzSqlDatabaseRemove-AzSqlDatabase 删除 Azure SQL 独立数据库或共用数据库。Removes an Azure SQL standalone or pooled database.
Get-AzSqlDeletedDatabaseBackupGet-AzSqlDeletedDatabaseBackup 获取可以还原的已删除的独立数据库或共用数据库。Gets a deleted standalone or pooled database that you can restore.
Remove-AzResourceGroupRemove-AzResourceGroup 删除资源组,包括所有嵌套的资源。Deletes a resource group including all nested resources.

后续步骤Next steps

有关 Azure PowerShell 的详细信息,请参阅 Azure PowerShell 文档For more information on the Azure PowerShell, see Azure PowerShell documentation.

可以在 Azure SQL 数据库 PowerShell 脚本中找到更多 SQL 数据库 PowerShell 脚本示例。Additional SQL Database PowerShell script samples can be found in the Azure SQL Database PowerShell scripts.