Azure restore a SQL Server database from blob

0
votes

I am trying to restore a SQL Server database in Azure from a database backup file stored in a blob. I have followed this link but got this error

TITLE: Microsoft SQL Server Management Studio

An error occurred while loading data.

ADDITIONAL INFORMATION:

The type of a blob in the container is unrecognized by this version. (Microsoft.SqlServer.StorageClient)

The remote server returned an error: (409) Conflict. (System)

I have also tried this:

CREATE CREDENTIAL mycredential1   
WITH IDENTITY= 'jjt', -- this is the name of the storage account you specified when creating a storage account   
SECRET = 'storage account key'

Then try to use the credential to restore the sql db from the azure blob, but failed on the above step with the following error:

Msg 40514, Level 16, State 1, Line 1
'CREATE CREDENTIAL' is not supported in this version of SQL Server.

What is the correct way?

1
azureazure-sql-database
What is your SSMS version? Please use the latest SSMS – Jayendran
This is probably better suited for dba.stackexchange.com – James Z
@Jayendran i tried 17.9 and 17.9.1 – thotwielder

1 Answers

0
votes

You cannot use CREATE CREDENTIAL on Azure SQL Database, you need to create a CREATE DATABASE SCOPED CREDENTIAL as shown below:

CREATE DATABASE SCOPED CREDENTIAL UploadInvoices  
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'QLYMgmSXMklt%2FI1U6DcVrQixnlU5Sgbtk1qDRakUBGs%3D';

For more information, please read this article.

Additionally, you cannot restore a native SQL Server backup into an Azure SQL Database. The article you are making reference is restoring a bacpac. You can restore a bacpac into an Azure SQL Database.

Please create a bacpac of your SQL Server (on-premises) database using Microsoft SQL Server Management Studio (SSMS), make a right click on the database, choose Tasks, then choose Export Data-tier Application to create the mentioned bacpac. Once the bacpac has been created you can then import it to Azure SQL Database as a new database.

A better method to migrate your database from a SQL Server instance to Azure SQL database is using the latest version of Data Migration Assistant which you can download from here. This tool would perform an assessment of your SQL Server assessment, it will let you know about any adjustments you need to make to make the database compatible to Azure SQL Database. If the database is compatible, the tool will migrate the database to an Azure SQL Database logical server.