Restore Azure SQL Database to Local SQL Server

By:   |   Comments (2)   |   Related: > Azure Backup and Restore


You need to migrate a database from Azure SQL Database to an on-premises SQL Server. Yes, we are taking a database currently on Azure SQL Database and moving it to an on-premises SQL Server.

Why? I needed to test this scenario because:

  • We're the vendor's only client with the database on Azure SQL Database, so of course Azure is blamed as the source of any issue
  • There is an upcoming upgrade of the application, so it's been decided we want to move the database on-prem at that time
  • Due to the small, < 1 GB size of the database and it not having much activity it will probably go on our SQL Server 2012 'Food Court' (See Learn to Speak DBA Slang for the definition of Food Court and some other entertaining DBA slang definitions. I learned quite a few new ones.) so it was tested on SQL Server 2012

Going from on-premises SQL Server to Azure SQL Database or Azure SQL Database to on-premises SQL Server is basically the same thing. We know that the traditional way of simply backing up the database from one server and restoring to another is not supported in Azure SQL Database, so we need a way to export the object creation scripts and export / import the data.

The basics steps to achieve the above are:

  1. Extract the data definitions of each object on source side
  2. Bulk copy the data out of each table to files
  3. Recreate the objects on the target side from the extracted definitions
  4. Bulk copy the data back into the newly created tables

Let's walk through the example.

I presumed it would be necessary to make changes to the source database at some point in this process, so the first step was to get a copy of the production database over to a test server. I created a copy of the production database on prodazuresqldatabaseserver on the test server, testazuresqldatabaseserver using my favorite new Azure SQL Database 'CREATE DATABASE ' option, 'AS COPY OF' to create a transactionally consistent copy of production in test.

CREATE DATABASE mydatabasesource
AS COPY OF prodazuresqldatabaseserver.mydatabase

There are a few options to do this, but SQL Server Management Studio, being my familiar friend, seemed to be the best way to facilitate the migration so that's what I chose.

Let's get started.

Right click on source database and select Tasks > Export Data-tier Application...

Export Data-tier Application

On the below screen click Next.

Export Data-tier Application Introduction Screen

Choose a directory, name the file and Save the *.bacpac file locally.

Save the bacpac file locally

Click Next.

Export Data-tier Application Export Settings

Verify settings and click Finish.

Export Data-tier Application Summary Screen

I was hoping to see all green checkmarks and a list of 'Success' in the Results column, but as we can see, I did not.

Export Data-tier Application Results Screen

Clicking on an Error link under any of the results shows this:

Errors from the Export Data-tier Application

The errors tell me there are two stored procedures referencing the database name in the FROM that appears to be a cross database query. This, of course is not supported in Azure SQL Database. I wasn't too concerned as this is a test copy of the production database, so I went in and removed the database name references from both stored procedures.  As a side note, I still can't figure out how the stored procedures were created in the first place. I'm just guessing it may have been created on Azure SQL Database V11 at the time and V11 allowed the create to work. This is just a guess and I may never know the answer but it's not worth worrying about.

Now we can go back to the top and start again at Export Data-tier Application. This is more like it. All steps have succeeded.

Export Data-tier Application Final Results as Success

Press Close, and we're ready to import the *.bacpac file to a database on our target server.

Import Database to On-Premises SQL Server

Right click on Databases and select Import Data-tier Application...

Import Data-tier Application

Click Next.

Import Data-tier Application Introduction Screen

Choose the .bacpac file that was created above and click Next.

Import Data-tier Application Import Settings
Import Data-tier Application Summary Screen

Did not want to see this coming, more errors. Now what?

Import Data-tier Application Results with Failed Operations

The error tells me it failed trying to create a Databased Scoped Credential, that's used for auditing and is not supported pre-SQL Server 2016.

Error SQL72045: Script execution error.  The executed script: CREATE DATABASE SCOPED CREDENTIAL.

I wasn't too concerned with stopping auditing briefly on the test server as it is test and that was the only way I could see to drop the Credential. To stop, go to the Security section of the Azure SQL Server panel and select Auditing in the Azure Portal.

Stop Auditing from the Azure Portal.

Set this to Off and click Save.

Turn off Auditing in Azure.

Disabling Auditing will drop the Credential (it gets added back when Auditing is enabled again).

Go back to the beginning and follow the steps to create the .bacpac. Now we can try the import again.  Mydatabase was created successfully so it must be dropped first. Go back to Import Data-tier Application… and follow the steps again. Here is what we want to see when everything works.

Import Data-tier Application Results with Operation Complete.

To re-enable Auditing change it back to On and click Save.

Re-enable Auditing

And we're done.

Summary of Steps

Now that I know the workarounds, my checklist for the production cutover looks something like this:

  1. Make a copy of production on
CREATE DATABASE mydatabasesource
AS COPY OF prodazuresqldatabaseserver.mydatabase
  1. Remove database name references from Proc1 and Proc2
  2. Temporarily stop Auditing on to remove Database Scoped Credential as they are not supported pre Azure SQL Database / SQL Server 2016.
  3. Export Data-tier Application to .bacpac
  4. Reenable Auditing on
  5. Import Data-Tier Application to create and import database on an On-Prem SQL Server.
  6. Create SQL Authentication app login and sync user.
-- create login
USE [master]
WITH PASSWORD=N'SuperSecret', 
-- drop user
USE MyDatabase
-- add user back and add to roles
ALTER ROLE [db_datareader] ADD MEMBER [app]
ALTER ROLE [db_datawriter] ADD MEMBER [app]
ALTER ROLE [db_ddladmin] ADD MEMBER [app]
Next Steps

This was just to show you one particular scenario and reiterate why you sometimes need to test, find workarounds, test again, rinse, lather, repeat until you have your process down.

Here are some links to migrating from on-prem to Azure SQL Database where you'll find other ways of migrating between platforms:

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Joe Gavin Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

View all my tips

Comments For This Article

Tuesday, March 3, 2020 - 7:12:26 AM - Joe Gavin Back To Top (84896)

Thanks Sherlee.

Friday, February 28, 2020 - 7:44:02 PM - Sherlee Back To Top (84846)

Nice and very helpful article. Thanks Joe!