How to Restore Azure SQL LTR Backup After Azure SQL Instance Deleted
By: Atul Gaikwad | Updated: 2020-07-03 | Comments | Related: More > Azure
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.
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.
- 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.
- 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>
- 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.
- 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>
- 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
- 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-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.
- 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.
- Please check out the Azure tips.
Last Updated: 2020-07-03
About the author
View all my tips