Use PowerShell to restore a database to an earlier point in time
Applies to: Azure SQL Database
This PowerShell script example restores a database in SQL Database to a specific point in time.
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.
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
$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"
# 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.