Restore Azure SQL Database to Local SQL Server

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, and it gets the blame as the source of any issue
With an application upgrade upcoming, the database will be moved 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.) so it was tested on SQL Server 2012

Solution

Going from an on-premises SQL Server to Azure SQL Database or Azure SQL Database to on-premises SQL Server is basically the same thing. Unfortunately, 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.

Copy Production Database to Test Server

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 begin.

Right-click on the 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

According to the errors, two stored procedures referencing the database name in the FROM appear to be a cross-database query. Of course, this is not supported in Azure SQL Database. I wasn’t too concerned as this is a test copy of the production database. So, I removed the database name references from both stored procedures. On a side note, I still can’t figure out how the stored procedures were created in the first place. I’m guessing it may have been created on Azure SQL Database V11 at the time and V11 allowed the create to work. Again, 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

Click Close. 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 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

This time, the error tells me it failed trying to create a Database Scoped Credential, generally used for auditing and 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 drops 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. Basically, this 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

Finally, 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
Remove Database Scoped Credential by temporarily stop Auditing on testazuresqldatabaseserver.database.windows.net 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

Again, this is only one particular scenario. Moreover, it demonstrates the need to test, find workarounds, test again, rinse, lather, and repeat until you have your process down.

Next Steps

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

Resources

Community

Subscribe