What’s it like to be a Database Administrator for managed databases in Azure? Sometimes it’s a painful guessing game when a routine, core operation– restoring a database – fails with a most unhelpful error.

In this case, if the restore is run via PowerShell, following Microsoft guidance, the error message is:

Restore-AzSqlInstanceDatabase: Long running operation failed with status ‘Failed’. 
Additional Info: An unexpected error occured while processing the request.

The misspelling of “occurred” doesn’t bring confidence, but we try and try again.

Surely There’s a More Clear Error Message If You Run the Restore in the Azure Portal, Right?

Nope, if you attempt do to the same operation in the Portal it will fail, and the message there is:

Error code: ResourceOperationFailure
Message: The resource operation completed with terminal provisioning state 'Failed'.

Ooof. So what went wrong?

I Couldn’t Find the Answers in the SQL Server Error Log

One of the benefits of Azure SQL Managed Instance is that it behaves a lot like a ’normal’ SQL Server Instance. You can access the SQL Server Error Log.

But even for core operations like a database restore, it doesn’t always contain the info you’d expect. In this case I couldn’t find any info regarding the restore in the log at all.

I Couldn’t Find the Answers on the Internet, but I Found Another Customer Who Also Lost Hours to This Problem

I haven’t found anything in Microsoft documentation so far explaining what can cause The Vaguest Error Ever.

I did find a blog post from Mika Sutinen in 2021, SQL Server Managed Instance and the most unhelpful error message during a database restore. Mika hit the same error message as I did – and in Mika’s case it was worse because they were restoring a large database, so the “long running” part of the error implied it might be related to the database’s size.

In Mika’s case, the database was partially contained and they needed to run some sp_configure commands to get the restore to work. (See the link above for full details.)

Talk about a needle in a haystack.

But the database I was restoring wasn’t partially contained. I was looking for a different needle.

In My Case, It was a Transparent Data Encryption Problem

When using Transparent Data Encryption (TDE):

Backup files for databases that have TDE enabled are also encrypted with the DEK. As a result, when you restore these backups, the certificate that protects the DEK must be available. Microsoft Docs

If you hit issues restoring a TDE database in “normal” SQL Server and this isn’t configured correctly, you’ll receive error messages that give you a good clue – something along the lines of:

Cannot find server certificate with thumbprint....

I didn’t have access to any such message when restoring my database. And, in fact, I didn’t figure it out at all– my colleague Mike figured it out and ended my suffering after a couple of hours. (Thanks, Mike!)

Better Error Messages do Show Up in Some Places

Azure SQL Managed Instance also has a database cloning functionality that uses an AG-like setup under the hood, called Database Copy.

This process seems to use some sort of database restore to seed the AG. If initiating the copy fails, you get a more clear error message.

This isn’t great as a workaround, though, because there are a lot of limitations on database copy, the biggest of which is:

The source or destination managed instance shouldn’t be configured with a failover group (geo-disaster recovery) setup.

If you have to choose between maintaining Disaster Recovery and troubleshooting a restore failure, most folks are going to choose keeping DR healthy every time.

Can Someone Please Fix This?

I created an “idea” on the Azure feedback site, Database Restores in the portal and Restore-AzSqlInstanceDatabase should give a helpful error message.