SQL Azure DACPAC – import and export data

If you keep an eye on the Windows Azure blog then you saw the  announcement of service release of SQL Azure. With this update there are also changes made to the SQL Azure part on the Windows Azure portal. Before we had Houston (on my Dutch blog I had months ago an item on that one). Houston was the web tooling to manage the database objects on a SQL Azure instance. And it was pretty neat! Other competitors also came with Web sites from which you can manage a SQL Azure instance.

This new release is a clean-up of the tool and of the portal functions. Nowadays everything got a ribbon, so does the Windows Azure portal and also the SQL Azure portal part. On this ribbon the most commons tasks are summarized.

In this post I want to highlight the part of “Import and Export CTP” on the ribbon. These look rather cool and seems to be a most asked feature. At least if I read it correct, it is about import and export data. Nowadays this is not easy job to accomplish. You could create scripts in SQL Server Management Studio and include the data in the script. But again error prune.

sqlazureribbon

But what about those new ribbon items?

sqlazureribbonexport sqlazureribbonimport

Depending on the focus in the tree on the left hand site of the screen, the export or the import is highlighted.

First the Export. With this option you can export the data and objects from a SQL Azure database to a single file. This file will be stored on Windows Azure Storage. You need to select the Database (Export is highlighted and can be clicked), then enter the credentials and Windows Azure storage location and hit the finish button. At that point you get a pop-up telling you your request will be processed.

sqlazure_export1

If you give it some time, depending on the size of the database of course etc. And if you go to the location on Windows Azure storage, you will see the file is there.

storage

If it is not then you click on the status button in the ribbon. You enter your credentials and look at the logging entries and find out what went wrong.

sqlazure_status1sqlazure_status2

And of course the import is the other way around. The file is on Windows Azure storage and you specify where the data has to imported in. I have specified a new database and the database will be created. But it also works with an existing database, the data and objects are replaced by the content of the file.

sqlazure_import1

Now that is cool! It almost looks like a backup facility. But it gets better.

Now we want an export from an on premise SQL  Server 2008 R2 server and import this into a SQL Azure instance!

Yes, this is also possible. Look at this site http://www.sqlazurelabs.com/ImportExport.aspx and with this DAC Import Export Client-side Tools V 1.1 tool on http://sqldacexamples.codeplex.com/ you can accomplish this task.

Run the tool with the correct options, put the file on the Windows Azure storage and activate an import as described above.

localbacpac1

And you have filled the SQL Azure database with on premise data. Now that is even cooler!

In the coming SDN Cloud special magazine (partly Dutch) we have an article about this too: How to use this for backup.

Leave a Reply