Troubleshooting potential backup/restore issues on Azure SQL Managed Instance

[JovanPop]

Managed Instance takes automatic backups (full backups every week, differential every 12 hours, and log backups every 5-10 min) that you can use to restore a database to some point of time in past within the retention period, restore accidentally deleted database. For more information, see Automated backups. Managed Instance also enables you to restore a database from a backup file placed on Azure Blob Storage, backup a database to Azure Blob Storage. Managed Instance currently does not support backup retention longer than 35 days, but you can use backups to blob storage as an alternative.

If you are experiencing issues with any backup or restore operation, the following troubleshooting steps might help you to identify the issue.

Restore database (point in time restore)

• Find a database in the Azure portal and check the earliest restore time value. Check that the point-in time that you used is after this time.
• If you cannot connect to the database that has completed restore, you might need to wait some additional time. The restored database must be registered in Azure, and in Business Critical tier, it should complete replication/seeding to all secondary replicas.

Restore database (from Azure blob storage)

• If you are noticing that some error is returned by RESTORE, check are you using supported syntax in this statement
• Make sure that you are restoring a database from a public blob storage protected with SAS credentials. Private IPs for blob storage and service endpoints are currently not supported.
• If you are getting an error 5 (Access Denied), make sure that you have not denied the access to your Azure Blob Storage account using the firewall:
• Verify that you have created a CREDENTIAL with the name equal to the URL of the blob storage where you want to backup your database.
• Try to run RESTORE FILELISTONLY statement and check if Managed Instance returns a list of the files in the backup.
• Script the CREDENTIAL to SQL Server, and restore a database from Azure Blob Storage account on the SQL Server.
• Check if your SAS credential placed in the SECRET option of CREATE CREDENTIAL statement valid. The most common errors in SAS token parameters are:
  • ? is not removed from the beginning of the SAS token because the Azure portal generates SAS token with the leading ?. Remove this character if you see it.
  • se (expiry date) property is set to some value in the past (note that this is UTC time).
  • st (start date) property is not in the past (note that this is UTC time).
  • sp (permission) property should allow reading the file on the storage account.
  • sip (ip range) remove this parameter if it is present in SAS token.
• If you are getting error 33111 Cannot find server certificate with thumbprint ... and you are trying to restore a backup of a database from another Managed Instance, check if the original database was encrypted with TDE in the past. Follow this procedure to backup a TDE protected database.
• If you want to track the progress of the ongoing RESTORE statement use T-SQL to query Dynamic Management views
• If you cannot connect to the database that has completed restore, you might need to wait some additional time. The restored database must be registered in Azure, and in Business Critical tier, it should complete replication/seeding to all secondary replicas.
• If you you need to cancel the restore request, you would need to delete the database that you are restoring using Azure PowerShell or Azure CLI.

Backup database (to Azure Blob Storage)

• If you are noticing that some error is returned by BACKUP check you are using supported syntax in this statement (for example mandatory COPY_ONLY option).
• Make sure that you are creating a backup of a database to a public blob storage protected with SAS credential. Private IPs for blob storage and service endpoints are currently not supported.
• If you are getting error 5 (Access Denied), make sure that you have not denied the access to your Azure Blob Storage account using the firewall:

• If you cannot backup a database, check if it is protected with Transparent Data Encryption (TDE) by looking at the following view:

  Select * from sys.dm_database_encryption_keys

  You need to disable TDE protection in order to take a manual backup.
• Verify that you have created a CREDENTIAL with the name equal to the URL of the blob storage where you want to backup your database.
• Script the CREDENTIAL and backup a database from SQL Server to Azure Blob Storage account.
• Check if your SAS credential placed in SECRET option of CREATE CREDENTIAL statement is valid. The most common errors in SAS token parameters are:
  • ? is left in the beginning of the SAS token that should be removed in the token is generated using the Azure portal.
  • se (expiry date) property is set to some value in the past (note that this is UTC time).
  • st (start date) property is not in the past.
  • sp (permission) property should allow creating and writing the file on the storage account.
  • sip (ip range) remove this parameter if it is present in SAS token.
• Check if your database is greater than 195GB and verify that you are using multiple URL stripes so every stripe would be less than 195 GB. For more information about creating backups see this article.
• If you are getting error 3063 Write to backup block blob device ... failed. Device has reached its limit of allowed blocks. or 3202 Write on '...' failed: 1117(The request could not be performed because of an I/O device error.) add the following options in the BACKUP statement: MAXTRANSFERSIZE=4194304, BLOCKSIZE=65536, and COMPRESS or use URL striping in the backup command.
• If you want to track the progress of the ongoing BACKUP statement use T-SQL to query Dynamic Management views

Automatic backups

• If you want to monitor backup requests, create a XEvent session that traces sqlserver.backup_restore_progress_trace event.
  • If you want to track the progress of the ongoing automatic backup use T-SQL to query Dynamic Management views
• If you cannot connect to the database that has completed restore, you might need to wait some additional time. The restored database must be registered in Azure, and in Business Critical tier, it should complete replication/seeding to all secondary replicas.

Restoring dropped database(Preview)

• Make sure that the parameters that you provided to the script are correct (especially a name of the database and the time when it is dropped). Note that dropped datetime is in UTC time zone.
• If you are using a PowerShell script to take the dropped time, not that this time might not be accurate and can be up to 5min after the actual drop time. Adjust the drop time if the restore fails.
• Make sure that you can restore the database on Managed Instance, and that you don't have some limit related to storage size or number of files in General purpose tier.

Find more troubleshooting steps here.