SQL Azure is a great platform for hosting a database for your applications. You get a redundant SQL cluster and licensing is included for as little as $5.99 a month! I’ve built SQL clusters before and trust me, the cost a lot more than that! AS Microsoft continues to build out the platform and capabilities, more and more companies will adopt it for their applications.
For many projects, you often will need a local copy of your database to test, build, and feel all warm and fuzzy knowing you have a backup of your data. With SQL Server 2012 And SQL Azure, Microsoft provides a great solution for migrating databases to your local machine. This blog details the steps to back up a SQL Azure database and restore it locally in SQL Server 2012.
The first step to this process is to “export” (backup) your SQL Azure database to Windows Azure storage.
Within the Windows Azure Management Portal you can quickly “Export” your database to Windows Azure Storage using the step-by-step wizard.
1. In the Windows Azure Management Portal, select the SQL Azure database and click “Export”
This steps launches the wizard to export the database to Windows Azure storage.
Export Database
2. In the “Export Settings”, select the desired Storage Account / Container
In this step, you will set the configuration for the export. You can export to any storage account / container.
Export Settings
Note
The guide will automatically name the bacpac file using the current date/time. You can modify this if needed.
3. Once completed, verify the export is complete
When compete, the database will packaged as a .bacpac file in the storage account.
Export Completed
4. Copy the bacpac file to your local machine
Using an Azure Storage application you can copy the file to your local machine for faster processing.
Copy Export Local
TIP
This step isn’t required, but is a little faster for the actual restore.
Now that you have a .bacpac file created you are ready to restore it to your local SQL Server 2012 instance.
1. In SSMS, right click “Databases” and select “Import Data-Tier Application”
SQL Server 2012 has been updated to recognize the .bacpac file type. These files are called “Data-Tier Applications”.
Import Data Tier Application
2. On the “Import Settings” page, select your ,bacpac file.
Browse to your local folder where you downloaded the .bacpac file.
Import Settings
TIP
You can import the file directly from Windows Azure Storage if you have not copied it locally. Just supply your account information to connect to Windows Azure Storage.
3. Set your local database name / file location
You can set any unique database name and file location when importing the database.
Database Settings
4. After the wizard completes, verify all the steps were successful
The import process will go through a number of steps to verify the package, create the schema, and import the data. Any errors will be noted the import process will terminate.
Import Complete
5. Verify the database was restored correctly and the data is correct
After refreshing the view, the database should be listed and contain all of your SQL Azure data.
Local Database
Now you have a local copy your Azure database to develop against or blow up or whatever it is you like to do. There are other methods of getting SQL Azure local but I find this is one of the easiest. Hope this helps!
TIP
Microsoft updated the SQL Data Tools in Dec 2012. This update is required for SQL Azure database restores. You can check out my blog on it here.