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, seeAutomated backups. Managed Instance also enables you to restore a database from a backup file placed on Azure Blob , backup a database to Azure Blob . Managed Instance currently don't support backup retention longer than 35 days, but you can use backups to blob 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 theearliest 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 byRESTOREcheck are you usingsupported syntax in this statement
  • Verify that you have createdCREDENTIALwith the name equal to the URL of the where you want to backup your database.
  • Try to runRESTORE FILELISTONLYstatement and check would Managed Instance return a list of the files in the backup.
  • ScriptCREDENTIALto , and restore a database from Azure account the .
  • Check is your SAS credential placed inSECREToption ofCREATE CREDENTIALstatement 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.
  • If you are getting the error33111Cannot 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 ongoingRESTOREstatement useT-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 a database that you are restoring usingAzure PowerShellorAzure CLI.

Backup database (to Azure Blob Storage)

  • If you are noticing that some error is returned byBACKUPcheck are you usingsupported syntax in this statement(for example mandatoryCOPY_ONLYoption).
  • If you cannot backup a database check is it protected with Transparent Data (TDE) by looking at the following view:
    Select * from sys.dm_database_encryption_keys

    . You need todisable TDE protection in order to take a manual backup.

  • Verify that you have createdCREDENTIALwith the name equal to the URL of the where you want to backup your database.
  • ScriptCREDENTIALand backup a database from the to Azure Blob Storage account.
  • Check is your SAS credential placed inSECREToption ofCREATE CREDENTIALstatement 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.
  • 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 error3063Write to backup block blob device ... failed. Device has reached its limit of allowed blocks.or3202Write on '...' failed: 1117(The request could not be performed because of an I/O device error.)add the following options in theBACKUPstatement:MAXTRANSFERSIZE=4194304,BLOCKSIZE=65536, andCOMPRESS.
  • If you want to track the progress of the ongoingBACKUPstatement useT-SQL to query Dynamic Management views

Automatic backups

  • If you want to monitor backup requests create XEvent session that tracessqlserver.backup_restore_progress_traceevent.
  • 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.

 

This article was originally published by Clustering For Mere Mortals. You can find the original article here.