Troubleshooting potential backup/restore issues on Azure SQL Managed Instance

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 don’t support backup retention longer than 35 days, but you can use backups to blob storage as an alternative.

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

Restore database (point is time restore)

• Find a database in the Azure portal and check the earliest restore time value. Check is the point-in time that you used 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


• Verify that you have created 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 would Managed Instance return a list of the files in the backup.


• Script CREDENTIAL to SQL Server, and restore a database from Azure Blob Storage account the SQL Server.


• Check is your SAS credential placed in SECRET option of CREATE CREDENTIAL statement valid. The most common errors 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).
  
  
  • sv (valid 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.
  
  
  
  


• If you are getting the error 33111 Cannot find server certificate with thumbprint … and you are trying to restore a backup of a database from another Managed Instance, check was the original database encrypted with TDE in the past. Follow this procedure to backup 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.

Backup database (to Azure Blob Storage)

• If you are noticing that some error is returned by BACKUP check are you using supported syntax in this statement(for example mandatory COPY_ONLY option)


• Verify that you have created CREDENTIAL with the name equal to the URL of the blob storage where you want to backup your database.


• Script CREDENTIAL and backup a database from the SQL Server to Azure Blob Storage account.


• Check is your SAS credential placed in SECRET option of CREATE CREDENTIAL statement valid. The most common errors 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).
  
  
  • sv (valid date) property is not in the past. If
  
  
  • sp (permission) property should allow creating and writing the file on the storage account.
  
  
  
  


• Check is your database greater than 195GB and verify that you are you using multiple URL stripes so every stripe would be less than 195 GB.


• If you are getting the 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.


• If you want to track the progress of the ongoing BACKUP 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.

Automatic backups

• If you want to monitor backup requests create 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.