How to Restore a SQL Database after the Server has been decomissioned

Question

Hi.

I have a SQL database on a logical SQL Server that can be decommissioned as the application is no longer used, but I dont want to lose the LTR backups as they need to be retained for 7 years. If I delete the logical server, and I going to lose all the LTR backups? If so, how can I retain them. I need to keep them all, but just take a final backup.

Thanks in anticipation.

Answer 1

Hi Michael,

If you delete the logical server, the Long Term Retention (LTR) database backups will remain until their expiration date or until someone deletes them. If you need to restore an (unexpired) LTR database backup you may restore it to a different logical server.

To do this you would first list the LTR backups using CLI/PowerShell/REST API, then restore the one you need to a logical server.

For example, to get a list of LTR backups in East US, you could use PowerShell command similar to below (you can add more options to filter it more):

Get-AzSqlDatabaseLongTermRetentionBackup -Location eastus

To restore one of the LTR backups you could use command similar to below:

Restore-AzSqlDatabase -FromLongTermRetentionBackup -ResourceId <resourceId> -ResourceGroupName myResourceGroup -ServerName targetServer -TargetDatabaseName myRestoredDatabase

Get-AzSqlDatabaseLongTermRetentionBackup

https://learn.microsoft.com/en-us/powershell/module/az.sql/get-azsqldatabaselongtermretentionbackup?view=azps-12.4.0

Restore-AzSqlDatabase

https://learn.microsoft.com/en-us/powershell/module/az.sql/restore-azsqldatabase?view=azps-12.4.0

Please click Accept Answer and upvote if the above was helpful.

Thanks.

-TP

Answer 2

Hi @Bullen, Michael,

Welcome to the Microsoft Q&A Platform! Thank you for asking your question here.

We would like to inform you that, If the logical SQL server is deleted, there is no way to restore to the database from LTR backups as these are stored in the Recovery Server vault.

We would suggest you to retain a logical SQL server without databases. You can delete the databases which is no longer to use. Server must remain for LTR backups to be accessible.

You can follow the below mentioned steps for restore a database from LTR backups using the Azure portal. For more information, click on below mentioned link.

https://learn.microsoft.com/en-us/azure/azure-sql/database/long-term-backup-retention-configure?view=azuresql&tabs=portal#view-backups-and-restore-from-a-backup

 

1. In the Azure portal, navigate to your server and then select Backups. To view the available LTR backups for a specific database, select Manage under the Available LTR backups column. A pane appears with a list of the available LTR backups for the selected database.
2. In the Available LTR backups pane that appears, review the available backups. Select a backup to restore from.
3. To restore from an available LTR backup, select the backup from which you want to restore, and then select Restore.
4. Choose a name for your new database, then select Review + Create to review the details of your Restore. Select Create to restore your database from the chosen backup.
5. On the toolbar, select the notification icon to view the status of the restore job.
6. When the restore job is completed, open the SQL databases page to view the newly restored database.

I hope, This response will address your query and helped you to overcome on your challenges.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.