Use PowerShell to restore a database to an earlier point in time

Стаття
08/31/2022

This PowerShell script example restores a database in SQL Database to a specific point in time.

If you don't have an Azure subscription, create an Azure free account 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.

Use Azure Cloud Shell

Azure hosts Azure Cloud Shell, an interactive shell environment that you can use through your browser. You can use either Bash or PowerShell with Cloud Shell to work with Azure services. You can use the Cloud Shell preinstalled commands to run the code in this article, without having to install anything on your local environment.

To start Azure Cloud Shell:

Option	Example/Link
Select Try It in the upper-right corner of a code block. Selecting Try It doesn't automatically copy the code to Cloud Shell.
Go to https://shell.azure.com, or select the Launch Cloud Shell button to open Cloud Shell in your browser.
Select the Cloud Shell button on the menu bar at the upper right in the Azure portal.

To run the code in this article in Azure Cloud Shell:

Start Cloud Shell.
Select the Copy button on a code block to copy the code.
Paste the code into the Cloud Shell session by selecting Ctrl+Shift+V on Windows and Linux, or by selecting Cmd+Shift+V on macOS.
Select Enter to run the code.

If you choose to install and use PowerShell locally, this tutorial requires Az PowerShell 1.4.0 or later. If you need to upgrade, see Install Azure PowerShell module. If you are running PowerShell locally, you also need to run Connect-AzAccount to create a connection with Azure.

Sample script

# Connect-AzAccount
$SubscriptionId = ''
# Set the resource group name and location for your server
$resourceGroupName = "myResourceGroup-$(Get-Random)"
$location = "westus2"
# 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
$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(-7) `
      -ResourceGroupName $resourceGroupName `
      -ServerName $serverName `
      -TargetDatabaseName $pointInTimeRestoreDatabaseName `
      -ResourceId $database.ResourceID `
      -Edition "Standard" `
      -ServiceObjectiveName "S0"

# Note: For performing geo-restore for a managed instance database, use -FromGeoBackup parameter with restore. 
# Sample script: Restore-AzSqlDatabase -FromGeoBackup -ResourceGroupName "TargetResourceGroup" -ServerName "TargetServer" -TargetDatabaseName "RestoredDatabase" -ResourceId $GeoBackup.ResourceID -Edition "Standard" -RequestedServiceObjectiveName "S2"


# Clean up deployment 
# Remove-AzResourceGroup -ResourceGroupName $resourcegroupname

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-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 in a server.
Get-AzSqlDatabaseGeoBackup	Gets a geo-redundant backup of a standalone or pooled database.
Restore-AzSqlDatabase	Restores a database.
Remove-AzSqlDatabase	Removes a database.
Get-AzSqlDeletedDatabaseBackup	Gets a deleted database that you can restore.
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.

Поділитися через