3

I can't find a straight answer to this question so hoping someone here came across this.

As the LTR backups are tied to the subscription there should be a way to restore a backup even if the original SQL Server that hosted the database is deleted.

How can these be viewed and restored via the Portal after the SQL Server is deleted?

Or via other means.

Florin D. Preda
  • 1,358
  • 1
  • 11
  • 25

3 Answers3

2

Currently there isn't a built-in method to restore the entire server. When a server is deleted (soft deleted) then you should call Azure support as soon as possible before a purge process that runs periodically fully removes the logical server. There are no SLAs for server deletions. So the quicker you can get to Azure CSS, the better it is.

If the server is deleted, there is no way to restore from automated built-in backups offered by Azure. Quoting from the page:

If you delete an Azure SQL Database server instance, all its databases are also deleted and cannot be recovered. There is currently no support for restoring a deleted server.

So everything is AS-IS. When a user deletes a logical server, you were asking the server to be deleted which is why you typed in the server name, etc. etc. CSS can work with engineering to figure out what is possible at best but there are no service-level guarantees unless Disaster Recovery (Geo-replication, Synchronization, long-term backups, etc.) was part of the deployment strategy.

I witnessed a case where a developer that works for a company in Costa Rica deleted their production Azure SQL logical server on a Thursday and Azure Support was able to recover on the next Monday. Usually Azure CSS gives a time frame of 7 days to recover an Azure SQL logical server that was accidentally deleted.

To avoid this in the future you can use “resource locks” which can protect against accidental deletion using Azure portal.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
  • Thanks Alberto. Seems the old automated backup to blob storage was more robust then. Do you know if there is any way to achieve similar results, i.e. back up the DB automatically to a place that is not dependent on the server’s existence? – Adam Szabo Jul 24 '19 at 21:10
  • You need to configure redundant replicas to be covered by Azure SLA https://learn.microsoft.com/en-us/azure/sql-database/sql-database-disaster-recovery. Long term backups are covered by Azure SLA also. https://learn.microsoft.com/en-us/azure/sql-database/sql-database-long-term-retention – Alberto Morillo Jul 24 '19 at 23:04
  • Azure guarantees for RPO and RTO if you are using appropriate features. See SLA coverage: https://azure.microsoft.com/en-in/support/legal/sla/sql-database/v1_3/ – Alberto Morillo Jul 24 '19 at 23:05
  • 1
    @Alberto this blog post suggests that the LTR backups are recoverable because they are tied to the subscription: https://blogs.msdn.microsoft.com/azuresqldbsupport/2018/11/13/unable-to-view-ltr-backups-for-azure-sql-database-servers/. Also I'm not looking to restore the entire server. That I can recreate. I just need to understand how to retrieve the data from LTR backups. – Florin D. Preda Jul 25 '19 at 09:17
  • Recreating the logical server with the same name yourself won't bring back old backups that existed prior a logical server deletion. You will start a new chain of backups that are managed by Azure. – Alberto Morillo Jul 25 '19 at 11:01
  • Once you delete a logical server all automated backuos are gone. – Alberto Morillo Jul 25 '19 at 11:07
  • Thanks Alberto. So the following statement from the blob post I linked is incorrect then? "...Basically, the LTR Backups are related to the subscription and not the server, so if the server was deleted those LTR backups would still exist as they are associated with the Subscription..." – Florin D. Preda Jul 25 '19 at 12:40
  • As I mentioned on my reply above to you long-term (LTR) backups are covered by the SLA and you can restore your database from there, but do you see them? https://stackoverflow.com/questions/51071129/long-term-backup-retention-list-is-not-available – Alberto Morillo Jul 25 '19 at 14:09
  • Call support they will recover your server for sure since you have LTR backups. That's the advice I have had in the past about this topic. – Alberto Morillo Jul 25 '19 at 14:21
1

This question specifically asks about Long Term Retention backups which the other answers (so far) do not address. Yes, when a logical server is deleted, all the automatic backups are also deleted, but NOT the long term retention backups. If a database was configured to use LTR and the LTR backup's retention period has not expired, then yes you can restore from them.

After a logical server is deleted, you can't see the LTR backups from the portal so you must use Powershell commands to list them and issue the restore.

  • Get-AzSqlDatabaseLongTermRetentionBackup
  • Restore-AzSqlDatabase

This link gives a good basic tutorial.

https://www.mssqltips.com/sqlservertip/6443/how-to-restore-azure-sql-ltr-backup-after-azure-sql-instance-deleted/

Dan S.
  • 81
  • 1
  • 2
0

If you delete your Azure sql server, then you could not backup it from the LTR backup.

Alberto Morillo has show you the document:

If you delete an Azure SQL Database server instance, all its databases are also deleted and cannot be recovered. There is currently no support for restoring a deleted server.

I also asked Azure support to get more message about your question. The replied me:

" Azure support can help you recover you Azure SQL server in 7 days after the deletion. You need to provide your server name and region for Azure Support."

You can call Azure Support from portal: enter image description here

Hope this helps.

Leon Yue
  • 15,693
  • 1
  • 11
  • 23