Restore a database in Azure SQL Managed Instance to a previous point in time

APPLIES TO: Azure SQL Managed Instance

Use point-in-time restore (PITR) to create a database as a copy of another database from some time in the past. This article describes how to do a point-in-time restore of a database in Azure SQL Managed Instance.

Point-in-time restore is useful in recovery scenarios, such as incidents caused by errors, incorrectly loaded data, or deletion of crucial data. You can also use it simply for testing or auditing. Backup files are kept for 7 to 35 days, depending on your database settings.

Point-in-time restore can restore a database:

  • from an existing database.
  • from a deleted database.
  • to the same SQL Managed Instance, or to another SQL Managed Instance.

Limitations

Point-in-time restore to SQL Managed Instance has the following limitations:

  • When you're restoring from one instance of SQL Managed Instance to another, both instances must be in the same subscription and region. Cross-region and cross-subscription restore aren't currently supported.
  • Point-in-time restore of a whole SQL Managed Instance is not possible. This article explains only what's possible: point-in-time restore of a database that's hosted on SQL Managed Instance.

Warning

Be aware of the storage size of your SQL Managed Instance. Depending on size of the data to be restored, you might run out of instance storage. If there isn't enough space for the restored data, use a different approach.

The following table shows point-in-time restore scenarios for SQL Managed Instance:

Restore existing DB to the same instance of SQL Managed Instance Restore existing DB to another SQL Managed Instance Restore dropped DB to same SQL Managed Instance Restore dropped DB to another SQL Managed Instance
Azure portal Yes No Yes No
Azure CLI Yes Yes No No
PowerShell Yes Yes Yes Yes

Restore an existing database

Restore an existing database to the same SQL Managed Instance using the Azure portal, PowerShell, or the Azure CLI. To restore a database to another SQL Managed Instance, use PowerShell or the Azure CLI so you can specify the properties for the target SQL Managed Instance and resource group. If you don't specify these parameters, the database will be restored to the existing SQL Managed Instance by default. The Azure portal doesn't currently support restoring to another SQL Managed Instance.

  1. Sign in to the Azure portal.

  2. Go to your SQL Managed Instance and select the database that you want to restore.

  3. Select Restore on the database page:

    Restore a database by using the Azure portal

  4. On the Restore page, select the point for the date and time that you want to restore the database to.

  5. Select Confirm to restore your database. This action starts the restore process, which creates a new database and populates it with data from the original database at the specified point in time. For more information about the recovery process, see Recovery time.

Restore a deleted database

Restoring a deleted database can be done by using PowerShell or Azure portal. To restore a deleted database to the same instance, use either the Azure portal or PowerShell. To restore a deleted database to another instance, use PowerShell.

Portal

To recover a managed database using the Azure portal, open the SQL Managed Instance overview page, and select Deleted databases. Choose a deleted database that you want to restore, and type the name for the new database that will be created with data restored from the backup.

Screenshot of restore deleted Azure SQL instance database

../../sql-database

PowerShell

To restore a database to the same instance, update the parameter values and then run the following PowerShell command:

$subscriptionId = "<Subscription ID>"
Get-AzSubscription -SubscriptionId $subscriptionId
Select-AzSubscription -SubscriptionId $subscriptionId

$resourceGroupName = "<Resource group name>"
$managedInstanceName = "<SQL Managed Instance name>"
$deletedDatabaseName = "<Source database name>"
$targetDatabaseName = "<target database name>"

$deletedDatabase = Get-AzSqlDeletedInstanceDatabaseBackup -ResourceGroupName $resourceGroupName `
-InstanceName $managedInstanceName -DatabaseName $deletedDatabaseName

Restore-AzSqlinstanceDatabase -Name $deletedDatabase.Name `
   -InstanceName $deletedDatabase.ManagedInstanceName `
   -ResourceGroupName $deletedDatabase.ResourceGroupName `
   -DeletionDate $deletedDatabase.DeletionDate `
   -PointInTime UTCDateTime `
   -TargetInstanceDatabaseName $targetDatabaseName

To restore the database to another SQL Managed Instance, also specify the names of the target resource group and target SQL Managed Instance:

$targetResourceGroupName = "<Resource group of target SQL Managed Instance>"
$targetInstanceName = "<Target SQL Managed Instance name>"

Restore-AzSqlinstanceDatabase -Name $deletedDatabase.Name `
   -InstanceName $deletedDatabase.ManagedInstanceName `
   -ResourceGroupName $deletedDatabase.ResourceGroupName `
   -DeletionDate $deletedDatabase.DeletionDate `
   -PointInTime UTCDateTime `
   -TargetInstanceDatabaseName $targetDatabaseName `
   -TargetResourceGroupName $targetResourceGroupName `
   -TargetInstanceName $targetInstanceName 

Overwrite an existing database

To overwrite an existing database, you must:

  1. Drop the existing database that you want to overwrite.
  2. Rename the point-in-time-restored database to the name of the database that you dropped.

Drop the original database

You can drop the database by using the Azure portal, PowerShell, or the Azure CLI.

You can also drop the database by connecting to the SQL Managed Instance directly, starting SQL Server Management Studio (SSMS), and then running the following Transact-SQL (T-SQL) command:

DROP DATABASE WorldWideImporters;

Use one of the following methods to connect to your database in the SQL Managed Instance:

In the Azure portal, select the database from the SQL Managed Instance, and then select Delete.

Delete a database by using the Azure portal

Alter the new database name to match the original database name

Connect directly to the SQL Managed Instance and start SQL Server Management Studio. Then, run the following Transact-SQL (T-SQL) query. The query will change the name of the restored database to that of the dropped database that you intend to overwrite.

ALTER DATABASE WorldWideImportersPITR MODIFY NAME = WorldWideImporters;

Use one of the following methods to connect to your database in SQL Managed Instance:

Next steps

Learn about automated backups.