How to Restore Azure SQL LTR Backup After Azure SQL Instance Deleted

By: Atul Gaikwad | Updated: 2020-07-03 | Comments (1) | Related: > Azure Backup and Restore

Problem

We were in the process of migrating our SQL server environment to an Azure SQL (PaaS) solution. We have built a few Azure SQL Instances for our production environment and migrated our databases to those instances. We have a multi tenant application and we had performance issues hosting our application on Azure SQL due to some constraints. We decided to migrate those Azure SQL (PaaS) instances to Azure SQL server VM (IaaS) server. Once the migration is completed we can discard the Azure SQL (PaaS) instances, but for an audit requirement we need to be able to restore the LTR (long term retention) backups during the period of time when we hosted the databases on the Azure SQL (PaaS) instances.

Unfortunately, due to the cost we cannot keep the instances and databases running on Azure SQL (PaaS). Can we restore LTR backup even after the host Azure SQL Instance (PaaS) has been deleted? Let’s see how we can do that.

Solution

While working with Microsoft to understand how the LTR backup works in terms of retention and their availability to restore the databases in case we need them for auditing purposes, we realized once the Azure SQL Instance has been deleted you cannott recover the instance after the 14 day period as it will be marked for decommission and can’t be recovered.

This is a valid limitation as it might involve costs to store the instance metadata and data to recover your Azure SQL Instance.

Steps to restore the AZSQL DB from LTR backups after SQL Instance is deleted

The next question is even if an Azure instance will not be recovered, can we still have LTR backups available associated with the Azure SQL Instance which has retention of another 6 months or a few years, as per the LTR backup policy.

The answer to that question is yes. Let us demonstrate, how can we do that step by step.

Below is an Azure SQL instance where I have my dummy database hosted which I created for demonstration purposes.

Azure SQL Instance where DB has been hosted

I have used default retention policy of 7 days for PiTR (Point In Time Recovery) backups as shown below.

Verify the available LTR Backups for the database are available as per the retention policy.

Verify if the Backups are available as per the retention policy

This PowerShell command will verify the availability of LTR Backups for a specific SQL Instance. It will connect to an Azure SQL Instance and verify that the backup shown as available are accessible.

Get-AzSqlDatabaseLongTermRetentionBackup -Location<Instance Location> -Servername <AZ SQL Instance Name> -Database <Az SQL DB Name>

Verify if the LTR Backup is available from Powershell

Now let’s go ahead and delete the Azure SQL instance, it will delete the inline database hosted on the SQL instance.
Connect to the Azure portal and delete the Azure SQL instance. When prompted provide the Azure SQL instance name for confirmation.

Once the instance is deleted you will receive notification that Azure SQL instance deletion is completed. Verify the instance has been deleted.

you will be notified if the SQL Instance has been deleted

Please verify if the LTR backups are still available and accessible from PowerShell even after the Azure SQL instance has been deleted. As you can see in the image below, the LTR backup is still available and accessible via PowerShell even after the Azure SQL instance is deleted.

Get-AzSqlDatabaseLongTermRetentionBackup -Location <Instance Location> -Servername  <AZ SQL Instance Name> -Databasename <Az SQL DB Name>

Verify if the LTR Backup is available from Powershell after Instance deleted

Now let’s try to restore the database from the available LTR Backups without creating a SQL Instances to verify if it creates an Azure SQL Instance where the database was backed up initially.
As shown below, set the parameters to restore the database before you execute the restore database command.

$subId = "<Your Subscription ID>"
$serverName = "AzureSQLServerVM"
$resourceGroup = "AzureSQLRG"
$dbName = "AzureSQLDB"
$location ="EAST US"

# get the list of all LTR backups in a specific Azure region
# backups are grouped by the logical database id, within each group they are ordered by the timestamp, the earliest backup first
$ltrBackups = Get-AzSqlDatabaseLongTermRetentionBackup -Location $Location

# get the list of LTR backups from the Azure region under the named server
$ltrBackups = Get-AzSqlDatabaseLongTermRetentionBackup -Location $Location -ServerName $serverName

# get the LTR backups for a specific database from the Azure region under the named server
$ltrBackups = Get-AzSqlDatabaseLongTermRetentionBackup -Location $Location -ServerName $serverName -DatabaseName $dbName

# list LTR backups only from live databases (you have option to choose All/Live/Deleted)
$ltrBackups = Get-AzSqlDatabaseLongTermRetentionBackup -Location $Location -DatabaseState Live

# only list the latest LTR backup for each database
$ltrBackups = Get-AzSqlDatabaseLongTermRetentionBackup -Location $Location -ServerName $serverName -OnlyLatestPerDatabase

$ltrBackup = $ltrBackups[0]

Set Parameter values required for AZ SQL DB Restore

Once Parameter values has been set without any issue, you can proceed with the database restore. Unfortunately, the Restore failed because the Destination server is not available, which was deleted. This is expected.

Restore-AzSqlDatabase -FromLongTermRetentionBackup -ResourceId $ltrBackup.ResourceId -ServerName $serverName -ResourceGroupName $resourceGroup -TargetDatabaseName "Test" -ServiceObjectiveName P1

Restore failed as Destination Instance has been deleted

Restore-AzSQLDatabase : The Resource 'Microsoft.sql/servers/azuresqlservervm' under resource group 'AzureSQLRG' was not found.
At Line:1 char:1
+Restore-AzsqlDatabase -FromLongtermRetentionBackup -ResourcesId $ltrBa ...
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   + CategoryInfo          : CloseError: (:)  [Restore-AzSqlDatabase], CloudException
   + FullQualifiedErrorId  : Microsoft.Azure.Commands.Sql.Backup.Cmdlet.RestoreAzureRmSqlDatabase

Now let’s create a test SQL Server instance for restoring the database. As you can see, I have created a test SQL Server instance, which we are going to use for the database restore.

create a test AZ SQL Instance for dummy restore

Now the Azure SQL Instance is created. Let’s go ahead with the restore. First, set parameters as required for the restore as shown below:

Next, we can proceed with the database restore. Provide the destination server name where we need to restore the database i.e. AZSQLTestVm. You can see AZSQLDB restore completed successfully.

Restore-AzSqlDatabase -FromLongTermRetentionBackup -ResourceId $ltrBackup.ResourceId -ServerName $serverName -ResourceGroupName $resourceGroup -TargetDatabaseName "AzureSQLDBCopy"

Now login to Azure Portal and verify the database is restored as expected and is available.

Verify DB is restored successfully from Azure Portal

Next Steps

Please check out the Azure tips.

About the author

Atul Gaikwad has over 14+ years of experience with SQL Server. He currently works for one of the leading MNCs in Pune as a Delivery Manager.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2020-07-03

Friday, April 21, 2023 - 3:40:35 AM - Ryan	Back To Top (91125)
Thanks for the tip, I was wondering if the same applies for Managed Instance, do you know if the backups remain after host delete and is the original scope of the LTR retained LTR backup remains for 90 days or what ever set? In some ways I prefer the recovery vault to the PaaS offerings as backups were somewhat detached.