Restore Azure SQL Database to Local SQL Server

By: Joe Gavin | Updated: 2019-04-15 | Comments (2) | Related: > Azure Backup and Restore

Problem

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

Solution

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:

Extract the data definitions of each object on source side
Bulk copy the data out of each table to files
Recreate the objects on the target side from the extracted definitions
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...

On the below screen click Next.

Export Data-tier Application Introduction Screen

Choose a directory, name the file and 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...

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.

Set this to Off and click Save.

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.

And we're done.

Summary of Steps

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

Make a copy of production on testazuresqldatabaseserver.database.windows.net.

CREATE DATABASE mydatabasesource
AS COPY OF prodazuresqldatabaseserver.mydatabase

Remove database name references from Proc1 and Proc2
Temporarily stop Auditing on testazuresqldatabaseserver.database.windows.net to remove Database Scoped Credential as they are not supported pre Azure SQL Database / SQL Server 2016.
Export Data-tier Application to .bacpac
Reenable Auditing on testazuresqldatabaseserver.database.windows.net.
Import Data-Tier Application to create and import database on an On-Prem SQL Server.
Create SQL Authentication app login and sync user.

-- create login
USE [master]
GO
CREATE LOGIN [app] 
WITH PASSWORD=N'SuperSecret', 
    DEFAULT_DATABASE=[MyDatabase], 
    CHECK_EXPIRATION=OFF, 
    CHECK_POLICY=OFF
GO
 
-- drop user
USE MyDatabase
GO
DROP USER app 
GO
-- add user back and add to roles
CREATE USER [app] FOR LOGIN [app]
GO
ALTER USER [app] WITH DEFAULT_SCHEMA[ app]
GO
ALTER ROLE [db_datareader] ADD MEMBER [app]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [app]
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [app]
GO

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:

About the author

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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2019-04-15

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