Import and Export (CTP) is an interesting SQL Azure feature that allows us to Export a SQL Azure database in form of a bacpac to Azure storage. And think of bacpac as the “zipped” version of schema and data in your database. It also allows us to import the bacpac to a SQL Azure database. Also note that Import and Export CTP works also with on premise SQL server database and you can download the necessary bits and information here: http://sqldacexamples.codeplex.com/releases/view/72388
Now in this blog post, we will see a step by step procedure to export a SQL Azure database to Azure storage.
Before we begin we will need:
- Azure storage account key (or limited shared access key]
- An Azure storage container [ you can create a Azure storage blob container via third party tools like Cerebrata’s cloud storage studio or find a tool on codeplex: http://www.codeplex.com/site/search?query=azure%20storage ]
- Credentials of the SQL Azure Database
Ok so let’s get started.
1. Go to Azure Management portal and browse to the “Database” section of the portal.
2. Now select the database that you want to export. For the demo purpose I would be exporting a database named AdventureWorksLTAZ2008R2 that resides on one of my SQL Azure server. Note that once you select a database – the “Export” section would be visible in the Import and Export CTP.
3. click on the Export button and you would see:
4. Now let’s see what do we input in each filed:
Under Database Export settings
a. Login: Enter the SQL Azure Username here
b. Password: Enter the SQL Azure password for the above username.
Under Target Storage accounts
c. New Blob URL: Now this would take the form: http(s)://<account name>.blob.core.windows.net/<container name>/<blob name>.bacpac
As I had mentioned earlier, you would need an existing Azure storage container – you input it in <container name>
And please note that the process would throw an error if the container does not exist.
And the blob would be create by the process.
And please note that the process would throw an error if the blob (of same name) already exists.
d. Access Key and Key Type: Enter the Azure storage access key or shared access key and select the appropriate key type.
Here is a sample
Note my blob URL: https://parasdoshicom.blob.core.windows.net/sqlazuredbs/adventureworksdb31dec.bacpac
here parasdoshicom is the account name, sqlazuredbs is the container name and adventureworksdb31dec.bacpac is the that will be created by Import and Export CTP and it does NOT exist before I start the process of exporting.
5. On successful submission, you will see a message:
6. Close it and let’s Switch the SQL Azure management portal. click on “server” (not database) and then click on status.
7. It will open the Import and Export Request status and it would ask you to enter the SQL Azure server credentials – please enter your username and password and then click on next:
Here note that if the process fails – then in the message column – you would see the reason behind it, So to show you that I had uploaded a database that had DDL triggers which are not supported by Data-tier application and as you can see – I have a row that has a column with few statements in the message tab.
So yes. This is it! And once you have successfully exported a bacpac to Azure storage, this bacpac can later be imported to an on premise SQL server database or it can be imported to SQL Azure Database.