Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Restore Azure SQL Database to Local SQL Server


By:   |   Updated: 2019-04-15   |   Comments   |   Related: More > Azure

Storage and High Availability Options for SQL Server in the Cloud

Free MSSQLTips Webinar: Storage and High Availability Options for SQL Server in the Cloud

This webinar will cover best practices for optimizing cloud storage and cost, how to leverage the cloud for disaster recovery, availability options and requirements for SQL Server and key factors to consider in your selection.


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:

  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 testazuresqldatabaseserver.database.windows.net.
CREATE DATABASE mydatabasesource
AS COPY OF prodazuresqldatabaseserver.mydatabase
  1. Remove database name references from Proc1 and Proc2
  2. 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.
  3. Export Data-tier Application to .bacpac
  4. Reenable Auditing on testazuresqldatabaseserver.database.windows.net.
  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]
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:



Last Updated: 2019-04-15


get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools