How to Backup SQL Azure Database to Windows Azure Blob Storage directly from your own machine

  • Article

Using Import/Export Service for SQL Azure CTP you can directly import or export between a SQL Azure database and a customer Windows Azure BLOB storage account.

As you may know the Windows Azure Storage cost ~$0.10 per GB so if you decided to use Windows Azure Storage to back up your SQL Azure DB that could be the best option. If you have SQL Azure and Windows Azure Storage in same Data Center, which would be best setup for using Azure Storage as SQL Azure backup scenario.

 

The service use a very much familiar BACPAC file format on both side during import and export.

 

The import/export service provides some public REST endpoints for the submission of requests. So to start with, you can directly backup your SQL Azure DB to Azure Storage using tool name “DAC SQL Azure Import Export Service Client V 1.2” located as below:

https://sqldacexamples.codeplex.com/releases/view/72388

 

While trying this tool, I found the following blog very useful and the videos were to the point to get my work done:

 

Here is the command line details for this tool:

 Microsoft (R) DAC Import Export Sample version 1.0.1.0
 Copyright (C) Microsoft Corporation. All rights reserved.
 
 Command Line Parameters:
 -H[elp] | -? Show this help text.
 -X[export] Perform an export action.
 -I[mport] Perform an import action.
 -D[atabase] <database> Database name to perform the action on.
 -F[ile] <filename> Name of the backup file.
 -S[erver] <servername> SQL Server Name and instance.
 -E Use Windows authentication
 (not valid for SQL Azure)
 -U[ser] User name for SQL authentication.
 -P[assword] Password for SQL authentication.
 -DROP Drop a database and remove the DAC registration.(*2)
 -EDITION <business|web> SQL Azure edition to use during database creation.(*4)
 -SIZE <1> SQL Azure database size in GB.(*4)
 -N Encrypt Connection using SSL.
 -T Force TrustServerCertificate(*6)
 -EXTRACT Extract database schema only.
 -DEPLOY Deploy schema only to database.
 
 
 Usage:
 Export a database to a bacpac using Windows Auth:
 DacImportExportCli -S myserver -E -B -D northwind -F northwind.bacpac -X
 
 Import a bacpac to a database using Windows Auth:
 DacImportExportCli -S myserver -E -D nw_restored -F northwind.bacpac -I
 
 Import a bacpac to SQL Azure with options:
 DacImportExportCli -S myazure -U azureuser -P azurepwd -D nw_restored -F northwind.bacpac -I -EDITION web -SIZE 5 -N
 
 Drop both database and DAC registration:
 DacImportExportCli -S myserver -E -D nw_restored –DROP

 

 

More info:

- On import the database must not exist. A new database is always created. SQL Azure uses system edition defaults if not set.

- DROP is very aggressive. It will attempt to remove a database that is not registered as a DAC. It will also remove DAC registration missing a database. Use -D to specify the database.

- Databases can use this tool only if they contain SQL 2008 R2 DAC supported objects and types.

- Choose the SQL Azure options desired, this may impact billing. (Only valid against SQL Azure)