restore a database to an Azure SQL Database

Question

I've created my first Azure SQL Database (not managed instance or virtual machine) , but I don't know SQL.  I can connect to my Azure SQL Database via SSMS v18.2 (15.0.18142.0).  I want to restore a database backup from AdventureWorks2016.bak.  When I right-click on my databases in SSMS though, its drop-down doesn't list "restore database".

Consequently, I couldn't follow the rest of the (linked Microsoft Doc's) instructions and switched to follow another Microsoft Doc to restore a database to a managed instance.  I assume that I can simply copy the SQL script in step #3.  But why does executing that script result in the error: "'CREATE CREDENTIAL' is not supported in this version of SQL Server"?  Which Microsoft Doc should I follow?  Thanks!

Answer 1

I've created an Azure SQL Database by following a Microsoft Doc, but I don't know SQL.  I can connect to my managed instance via SSMS v18.2 (15.0.18142.0).  I want to restore a database backup from AdventureWorks2016.bak.  When I right-click on my managed instance's databases though, SSMS's drop-down doesn't list "restore database".

Consequently, I couldn't follow the rest of the (linked Microsoft Doc's) instructions and switched to follow another Microsoft Doc to restore a database to a managed instance.  I assume that I can simply copy the SQL script in step #3.  But why does executing that script result in the error: "'CREATE CREDENTIAL' is not supported in this version of SQL Server"?  Which Microsoft Doc should I follow?  Thanks!

Good day,

In short, first document is for SQL Server on-premises, and second link is for Azure Managed Instance. You probably use Azure SQL Database which mean both does not fit your case.

In the content you speak about "created an Azure SQL Database" but "connect to my managed instance". You cannot create X and connect to Y. Seems like there is a confusion which must be clarify first.

----- My 2 cent ----

>> I don't know SQL

It make no sense to me that you got a simple job like restoring database without any knowledge of SQL. The forum is not a place to teach the basics but to help in specific questions that raise on the way.

>> When I right-click

Do not right-click or left-click anywhere! 

Wizards GUI tools are the worst option which someone who lack the knowledge should use! They should only serve as accessory tools to short the working time for example. Someone without the proper knowledge which use Wizards GUI tools will never gain the knowledge probably and will always have issue to understand what actually the tool do and therefore he will always have technical issue on the way.

Before you Restore a Database Backup Using SSMS you should Restore a Database Backup Using Transact-SQL, which is what SSMS do for you under the scene. If you use direct query then you will get the direct respond from the server instead of the respond of the ckuent application.

>> When I right-click on my managed instance's databases though, SSMS's drop-down doesn't list "restore database".

If you only provided a screenshot then we could see what you see. I ahve no idea where you clicked or looked.

As you can see the image bellow SSMS shows the option when we use managed instance, but this will probably not help you.

>> I want to restore a database backup from AdventureWorks2016.bak. 

This link is for SQL Server on-premises and NOT for Azure SQL Managed Instance!

I HIGHLY recommend to first learn and get some experience with On-premises server BEFORE you move to use Azure Managed Instance!

>> I couldn't follow the rest of the (linked Microsoft Doc's) instructions and switched to follow another Microsoft Doc to restore a database to a managed instance.

This link is for Azure Managed Instance. The tutorial show how to Restore the [Wide World Importers] database to a Managed Instance, using a URL and backup which were prepared in advanced for you for the task. I don't think that this tutorial give any knowledge! It is simply a step by step instructions to the layman.

It is working well 

>> But why does executing that script result in the error: "'CREATE CREDENTIAL' is not supported in this version of SQL Server"?

If you are using the instruction as it is and you read them well then it will not raise this issue or any other issue. As I mentioned this is a step by step instructions to the layman - who read well.

My guess (and this is only a guess) is that you are not using SQL Server managed instance but Azure SQL Database, which not totally different service and it does not support CREATE CREDENTIAL as you can see in the official documentation:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-credential-transact-sql?view=sql-server-2017

Check the "APPLIES TO" list

>> Which Microsoft Doc should I follow?

First we need to clarify which service you actually use, but in any case I do not recommend any of these two URLs! Next you must understand how to BACKUP before you try to use RESTORE! The way to backup the database will determine the way you restore it.

 

WHAT NEXT?

Check which type of database you use by executing the following query:

SELECT
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('EngineEdition') AS EngineEdition,
SERVERPROPERTY('edition') AS Edition,
SERVERPROPERTY('ProductMajorVersion') As MajorVersion,
@@version AS version;
GO

EngineEdition value determine the Database Engine edition of the instance which is what we need.

1: Personal or Desktop Engine
2: Standard
3: Enterprise
4: Express
5: Azure SQL Database
6: SQL Data Warehouse
8: managed instance

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]    [Linkedin]

Answer 2

In the content you speak about "created an Azure SQL Database" but "connect to my managed instance". You cannot create X and connect to Y.

Ronen, I appreciate your detailed reply.  My goal is to learn Microsoft Exam Ref 70-779, which doesn't specify SQL or T-SQL as its pre-requisite.  On page 5, its note requires readers to connect to AdventureWorks2016 (specific version).  Since I don't have my own server, I created my first Azure SQL Database (in the Azure portal) and I can connect to it using SSMS.

Its Query Editor results are:
ProductVersion: 12.0.2000.8
ProductLevel: RTM
Edition: SQL Azure
EngineEdition: 5
MajorVersion: 2
Version: Microsoft SQL Azure (RTM) - 12.0.2000.8

When I right-click on databases in SSMS, I don't see "restore database" (maybe you're using a different version than v18.2 15.0.18142.0).  Should I create another Azure SQL Database with its data source as AdventureWorks2016.bak?  If no, then how can my Azure SQL Server restore AdventureWorks2016.bak?  Thanks!

Answer 3

Hi,

You wont see restore DB in Azure.  You can restore from blob storage following this link for a .bak

Thanks,

Matt

Answer 4

You can restore from blob storage following this link for a .bak

@Matt Bradley Mcr, my original post's second paragraph linked the same Microsoft Doc as you did.  However, I learned that it's not applicable to an Azure SQL Database that's not a managed instance.

Answer 5

Ah so this is not a managed instance as the title says?  Azure SQL (not managed instance) cant restore a .bak, you need a bacpac file to do this.  So on the source server, you need right click the DB and take a data tier export.  Bacpacs can either be restored from blob storage (if you use the import option via the Azure portal on the DB screen). Or from your local machine if you're connected via SSMS.

Be wary though that bacpacs can be very temperamental.  If you get an error exporting or importing then you probably just needs to use the latest version of Management Studio to take the bacpac.

Let me know if I've misunderstood

Thanks,

Matt

Answer 6

This is the Azure portal way 

And this is from local drive via SSMS

Errors can be misleading though and it is usually the SSMS version on older DB engines

Thanks,

Matt

Answer 7

Azure SQL (not managed instance) cant restore a .bak

Matt, unfortunately the data source for AdventureWorks isn't .bacpac.  How can I install the sample version that I need, which is AdventureWorks2016.bak?  Thanks!

Answer 8

Hi,

You'll have to restore it to another SQL instance (VM maybe) and then take the data tier export (bacpac) via SSMS.  Sadly you cant do a .bak to Azure SQL or convert them.

Thanks,
Matt

Answer 9

Hi

First the link that you provided is for Azure Sql Managed instance:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance-get-started-restore

The same document has a link on how to create the same and connect using SSMS.

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance-get-started

The screen shot you have given is for Azure SQL Single database and it does not support what you are trying to do. So, it is advised that you create the managed instance for Azure Sql as explained in the above document and then restore the back up you have on it.

Or You can create a Sql Server VM and restore the .bak file on this and export the restored DB to Azure SQL Single DB.

Here is link to create the VM:
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/quickstart-sql-vm-create-portal

Here is the link for different methods to export the restored DB on VM to move to Azure SQL Single DB
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-single-database-migrate

Hope this helps. 
Thanks
Navtej S

Answer 10

Its Query Editor results are:
ProductVersion: 12.0.2000.8
ProductLevel: RTM
Edition: SQL Azure
EngineEdition: 5
MajorVersion: 2
Version: Microsoft SQL Azure (RTM) - 12.0.2000.8

Hi,

I am back :-)

I see that you already got the rest of the support.
I am glad to see that solved the issue :-)

It is important that the query I asked you to execute is the main information which we need.
I clarify which version of service you are using.

According to your answer you EngineEdition: 5 you use Azure SQL Database. Please remember this for any future question and don't confuse with the other types like Azure Managed Instance and On-Premises SQL Server.

I have no idea why other people still confused and focus on screenshot for example when we have pure answer provided by your result of the query :-) This is all we need to execute in order to confirm which version you have : EngineEdition: 5

>> Navtej S: The screen shot you have given is for Azure SQL Single database and it does not support what you are trying to do. So, it is advised that you create the managed instance for Azure Sql as explained in the above document and then restore the back up you have on it.

I am sorry but I totally do not agree which this direction.

Choosing your service should never be according your ability, knowledge, or laziness!
But according to what you actually need to use.
IN MY OPINION IT IS NOT advised that you create the managed instance only for this reason!

• There is no reason to manage instance when you need single database
  
• Managed instance means that you will need to pay for at least 4 vCore which make no sense for your needs probably. It is more than 400$ a month! While Azure SQL Database can cost about 5$ a month.
• In short those are totally different services!

 

>> Navtej S: Or You can create a Sql Server VM and restore the .bak file on this

Again, totally different services.

1. Do you really want to manage the entire Virtual Machine + the SQL Server Instance when you only need a single database? Obviously there are cases that this is the solution, but your request was to restore the database to your current service which is Azure SQL Database, and this is not a reason to choose a different service!

2. You said that you learn for exam. Is this fit your exam requirement?!?
You cannot learn about Azure SQL Database using SQL Server on VM.

In my opinion according your description, Your solution should be to one of the following for example:

1. You can choose with Matt Bradley Mcr solution which is: restore it to On-premises SQL Server instance (Azure VM or locally) and then take the data tier export (bacpac).
2. Another option which make much more sense to me is simply create the DDL (all the tables and other entities) using script directly in your Azure SQL Database, and import the data to the tables using the csv files as presented in the same project which you gave the link.
   Check the information under "Install scripts". For example in the following URL you have all the script for the DLL and all the .csv files. In this case you might need to manually make some tiny changes in the script and I do not recommend to execute it as one but step by step.
   https://github.com/Microsoft/sql-server-samples/tree/master/samples/databases/adventure-works/oltp-install-scriptHere you get the scripts to create the database and all the inside entities:
   https://github.com/microsoft/sql-server-samples/blob/master/samples/databases/adventure-works/oltp-install-script/instawdb.sql
   
3. Another option which is probably the best option in your case if you are OK with using the light version AdventureWorksLT of the database, is simply create new Azure SQL Database using the portal or powershell and select from the existing templates to use AdventureWorksLT. THIS IS BUILT-IN TEMPLATE IN THE AZURE😃

You explicitly said that your "goal is to learn Microsoft Exam Ref 70-779, which doesn't specify SQL or T-SQL as its pre-requisite"

I am not familiar with this exam or any other theoretical exam. According to your description I will probably never advice anyone to take this exam by itself, and I will not give someone who did this exam even one single point when I will need to hire someone. It make no sense to me to hire someone who have no idea in basic SQL which any child can learn in few days, even to the lower position.

According to your description I am guessing that you probably need the built in AdventureWorksLT version and not the full version AdventureWorks. Check and confirm this with someone who is familiar with this exam.

I Hope that you still follow this thread and you will read my response with full attention!

Good luck in the exam👍

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]    [Linkedin]

Answer 11

In my opinion according your description, Your solution should be to one of the following for example:

1. You can choose with Matt Bradley Mcr solution which is: restore it to On-premises SQL Server instance (Azure VM or locally) and then take the data tier export (bacpac).
2. Another option which make much more sense to me is simply create the DDL (all the tables and other entities) using script directly in your Azure SQL Database, and import the data to the tables using the csv files as presented in the same project which you gave the link.
   Check the information under "Install scripts". For example in the following URL you have all the script for the DLL and all the .csv files. In this case you might need to manually make some tiny changes in the script and I do not recommend to execute it as one but step by step.
   https://github.com/Microsoft/sql-server-samples/tree/master/samples/databases/adventure-works/oltp-install-scriptHere you get the scripts to create the database and all the inside entities:
   https://github.com/microsoft/sql-server-samples/blob/master/samples/databases/adventure-works/oltp-install-script/instawdb.sql
   

Sorry Ronen, I didn't realize that EngineEdition has different options and mistakenly assumed that Azure SQL Database is an instance managed by Azure.  (I avoided your proposed solution #1 because Microsoft warned SQL novices about the complexities of installing and configuring SQL Server.  Since I don't know what's OLTP, I also avoided your proposed solution #2.)

So I created my Azure SQL Database using AdventureWorksLT, but it isn't the version of the sample data set that my book (from which I'm learning) used for examples.  The easiest solution is searching for AdventureWorks2016.bacpac on GitHub, which can be easily imported into my databases.  Nonetheless, thanks for your suggestions and understanding!

Answer 12

Just to clarify:

>> I didn't realize that EngineEdition has different options

I told you this from the start, and this was the basic to understand your case, which is why I asked you to execute the query to check the value 😃

I HIGHLY recommend to always to read my entire responds even if they are long. It is recommended to understand what are the optional values of EngineEdition and I gave you this information in my first response.

>> Since I don't know what's OLTP, I also avoided your proposed solution #2

This make no sense!!!

If you don't know then you should ask, and not get decision by eliminate anything that you do not know.

I totally do not understand your approach. Do you want to learn or do you just want to get a paper which say that know something without really knowing anything?!? Do you really think that this paper will help you get anywhere?

I do not know you and I really wish you luck. I try to explain to you the side of the person who will interview you to your next job (at least my approach).

OLTP = Online Transactional Processing.

It has nothing to do with the question. It is a way of using the data/database and not a type of a database. You can use the same type of Database for OLTP or for DW (Data Warehouse).

For example, a database which has several millions transactions per second which change the data is usually OLTP, while a database which has only 1 transaction which change the data each day and several millions transactions which read the data is usually Data Warehouse (I simplified the topic a lot... for more information you should use Google) 

* With that being said there are types of databases which best fit for one case usually. for example Azure Data Warehouse is mainly used for (can you guess?) Data Warehouse. In Addition there are special features for OLTP like in-memory tables.

>> So I created my Azure SQL Database using AdventureWorksLT, but it isn't the version of the sample data set that my book (from which I'm learning) used for examples.

This make sense. Which is why I said "Check and confirm this with someone who is familiar with this exam" :-)

>> The easiest solution is searching for AdventureWorks2016.bacpac on GitHub

Not true! The easiest solution is not to do anything and let someone else do it! 

With that bring said, if you cannot install SQL Server at home or any other place -> restore the database to your on-premises server -> and create the bacpac yourself... then letting someone else do it might be your fastest option (not best but fastest).

Good luck with your exam

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]    [Linkedin]

Answer 13

Great info as always, Pituach!

Answer 14

Thanks Matt

Appreciate good word always😃

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]    [Linkedin]