Restore Database Fails - Azure SQL Database In ElasticPool

0
votes

Attempts to restore a .bak file stored in Blob Storage to an Azure SQL Managed Instance fails.

This database exists in an Elastic Pool. When creating the pool and database, we thought we were creating a Azure SQL Managed Instance. There are posts that state that databases in an ElasticPool are not managed instances. Could this be why restore datbase fails?

T-SQL

RESTORE DATABASE [Web] FROM URL = 'somestorage.blob.core.windows.net/backups/…' WITH CREDENTIAL = 'SHARED ACCESS SIGNATURE'

Error

'RESTORE DATABASE' is not supported in this version of SQL Server.

  • I confirmed that the master key as well as the scoped credential were created successfully.
  • I confirmed that the URL is correct.

Can you recommend a solution to resolve this problem?

We initially encountered a problem where creation of the credential failed following the demo on Microsoft.com. CHEEKATLAPRADEEP responded that the correct syntax is CREATE DATABASE SCOPED CREDENTIAL and not CREATE CREDENTIAL as shown in the demo. Hopefully this fix for the restore is also an update to the syntax.

1
sql-serverazureazure-elasticpool

1 Answers

2
votes

Your Database in the Elastic Pool is not a managed instance.

SQL Database support restore from automated backups only.

https://i.stack.imgur.com/w08uT.png

For more details,you can see:

Feature comparison: Azure SQL Database versus SQL Server: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-features

RESTORE Statements for Restoring, Recovering, and Managing Backups: https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-for-restoring-recovering-and-managing-backups-transact-sql?view=sql-server-2017