Copying an on-premise Database to an Azure SQL Database


August 24, 2016 by Kenneth Fisher

Continuing my exploration of Azure the next logical step seemed to be copying an on-premise database up to the cloud. This is also the next in the ideas I got while watching Jes Borland’s (b/t) excellent session on Azure and SQL Server on the Pass Data Architecture Virtual Chapter.

It turns out it’s pretty easy (even if it takes some time). So where to start? Well the first thing we need is a place to put our database. An Azure SQL Database Server. If you don’t already have one creating a new one is fairly easy.

First start at Log in and follow these steps:

  1. Select New
  2. Type SQL Database Server in the search box.
  3. Select SQL Server (logical server) from the search results.
  4. Select Create. (You can spend some time reading the description if you really want to. I don’t mind.)
  5. Fill in the required fields. Server name, admin name & password, subscription, resource group (create a new one if needed), and lastly the location of the server. Make sure it’s close by. The first few times I did this I managed to put all of my work in Europe.
  6. And finally hit Create again.
  7. Wait.
  8. Wait some more.
  9. Get some coffee.
  10. Wait a bit more.
  11. Sigh with relief that your server has been created.


Next we’ll need to know how to connect to the server. Go to the server, hit properties and it’s under Server Name.


Almost done! Now go into SSMS and connect to the server with the database you want to copy. Right click on the database and select Tasks -> Deploy Database to Microsoft Azure SQL Database.


This is where we will need that server name from before. Don’t forget that in order to connect we need a firewall rule in place. Also pay special attention to the Edition (Basic, Standard, Premium, and Data Warehouse), Size (dependent on edition), and Service Objective (also dependent on edition). These are going to dramatically affect your cost. When ready hit Next.


Confirm your settings and hit finish!


Your database will now be exported and then imported. This can take some time even with a relatively small database. You thought you were waiting before? If you have a slow network you might as well go have some dinner, take a nap, whatever.


And finally finished!


There are lots of ways to perform any task and it’s a good idea to know at least a few of each. So in the not to distant future I’m going to try the same thing only using a BACPAC. Then we work on going the other direction.

Note: I found out after I posted this that my friend Randolph West (b/t) posted on the same subject recently. (Not that it matters. Remember how I’ve said it’s not a big deal to post on the same subject as someone else?) I enjoyed reading his version and thought I would share it with ya’ll as well.

Moving a database to Azure SQL Database

4 thoughts on “Copying an on-premise Database to an Azure SQL Database

  1. Neal Ganslaw says:

    There is also a great tool called
    SQL Database Migration Wizard (SQLAzureMW) which automates this process. I have been using it for years and absolutely love it.

  2. […] Well there are two reasons. First because I’m studying how to move databases from SQL Server to Azure SQL Database and back. My first blog on the subject was using the Deploy Database to Microsoft Azure SQL Database option to move a SQL Server database to Az…. […]

  3. […] lets you create DACPACs and BACPACs. A little while back created a BACPAC and then used it to copy the on-premises DB into Azure. This time I’m going to use a DACPAC as a schema only […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: