SQL Azure introduced Import/Export option with SQL Azure lab. By using DAC framework, the SQL Azure database can be exported and imported (backup and restore) using command line from on-premise system itself.
This post explains how to Import and Export (Backup and Restore) Azure database using DAC command line tool.
Prepare the system
This tool built on top of SQL Server “Denali” (currently in CTP 3.0). So, to use Import/Export service, the system must have the following software needs to be installed.
The first three installations must be done on the same order and after that the other three can be in any order. I have tested installing in the same order, so please try installing in the same order.
If your system already installed any other CTP version of SQL Server “Denali”, this installation will have some issue. So better to uninstall the older version and try with this new version. But installing SQL Server “Denali” CTP versions will not impact to any other SQL Server version such as SQL Server 2008/2008 R2, 2005.
Once the system installed successfully these installations, download the DacImportExportCli.zip from codeplex site - the application (EXE and config file) or source code – version 1.1 (DAC Import Export Client-side Tools V 1.1).
Note: The EXE from the codeplex compiled for x64 bit CPU, so if you are working with x86 (32 bit) machine you required to compile the source code by changing platform target to Any CPU (or x86) in the property of the project.
The source code does not have two files in the solution (Export.cs, Deploy.cs and CHANGES.txt); this can be downloaded from browsing the code from Source Code tab. The same code uploaded here.
Export the DacImportExportCli-V-1-1 file under a folder (for Ex: C:\Database\ DacImportExportCli), it will contain two files DacImportExportCli.exe, DacImportExportCli.exe.config. (Otherwise open the source code and compile it.)
Verify the SQL Azure server can able to login from the system where the command line runs. It required firewall configuration settings in the Management Portal.
Command-line option
The command line will accept the following parameter.
–S –> Server Name (Ex: -S abcedjjdfhh. database.windows.net)
–D –> Database Name (Ex: -D Northwind2)
–E –> Informs Windows Authentication
–U –> User Name of the Database to login (Ex: -U azuredbuser)
–P –> Password of the user mentioned (Ex: -P password) (Either –E or –U –P possible)
–X –> Inform Export is required
–I –> Inform Import is required (Either –E or –I can be possible)
–F –> File name to store the backup database (Ex –F C:\Database\Northwind_18Sep2011.bacpac)
–EDITION –> edition of the database whether it can be BUSINESS or WEB (Ex: - EDITION WEB)
–SIZE –> Size of the database in GB (Ex –SIZE 1) – The size must be equal to the size can get from SQL Azure (1, 5, 10, 20, 30, 40, 50 GB)
Export (Backup) the database
In this example, I am planning to backup a database Northwind2 from SQL Azure server. Below is the screenshot of SQL Azure server before taking backup.
Below is the command line for exporting (backup) the database. The backup local will be in my local computer under D:\Database. The database backup file name will be Northwind2_18Sep2011.bacpac.
DacImportExportCli.exe -S **********.database.windows.net -D Northwind2 -F D:\Database\Northwind2_18Sep2011.bacpac -X -U **username** -P **Passw@rd**
Below screenshot shows the output of the command line.
Import (Restore) the database
Below command shows how to import (restore) the database to a new database under SQL Azure server using command line tool.
In this example I am creating a new database Northwind2New under the same server exported.
DacImportExportCli.exe -S **********.database.windows.net -D Northwind2New -F D:\Database\Northwind2_18Sep2011.bacpac -I -U **username** -P **Passw@rd**
Below screenshot shows the output
Below is the screenshot of SQL Azure server after import operation completed.
Drop the database
If the Azure database imported using DAC command line, the bacpac will be registered in the Azure server. So, to delete the database better to use the DAC command line instead of using DROP DATABASE. If the database is drop by DROP DATABASE, the bacpac registration wont be removed from the server. So importing the database with the same registration name will not be possible next time.
Below is the command to drop the database
DacImportExportCli.exe -S **********.database.windows.net -D Northwind2New -DROP -U **username** -P **Passw@rd**
The screenshot of the command line output follows:
The command will drop the database Northwind2New from the SQL Azure server.
This post explains how to Import and Export (Backup and Restore) Azure database using DAC command line tool.
Prepare the system
This tool built on top of SQL Server “Denali” (currently in CTP 3.0). So, to use Import/Export service, the system must have the following software needs to be installed.
- Install .NET 4.0 run time
- Install SQLSysClrTypes.msi
- Install SharedManagementObjects.msi
- Install DACFramework.msi
- Install SqlDom.msi
- Install TSqlLanguageService.msi
The first three installations must be done on the same order and after that the other three can be in any order. I have tested installing in the same order, so please try installing in the same order.
If your system already installed any other CTP version of SQL Server “Denali”, this installation will have some issue. So better to uninstall the older version and try with this new version. But installing SQL Server “Denali” CTP versions will not impact to any other SQL Server version such as SQL Server 2008/2008 R2, 2005.
Once the system installed successfully these installations, download the DacImportExportCli.zip from codeplex site - the application (EXE and config file) or source code – version 1.1 (DAC Import Export Client-side Tools V 1.1).
Note: The EXE from the codeplex compiled for x64 bit CPU, so if you are working with x86 (32 bit) machine you required to compile the source code by changing platform target to Any CPU (or x86) in the property of the project.
The source code does not have two files in the solution (Export.cs, Deploy.cs and CHANGES.txt); this can be downloaded from browsing the code from Source Code tab. The same code uploaded here.
Export the DacImportExportCli-V-1-1 file under a folder (for Ex: C:\Database\ DacImportExportCli), it will contain two files DacImportExportCli.exe, DacImportExportCli.exe.config. (Otherwise open the source code and compile it.)
Verify the SQL Azure server can able to login from the system where the command line runs. It required firewall configuration settings in the Management Portal.
Command-line option
The command line will accept the following parameter.
–S –> Server Name (Ex: -S abcedjjdfhh. database.windows.net)
–D –> Database Name (Ex: -D Northwind2)
–E –> Informs Windows Authentication
–U –> User Name of the Database to login (Ex: -U azuredbuser)
–P –> Password of the user mentioned (Ex: -P password) (Either –E or –U –P possible)
–X –> Inform Export is required
–I –> Inform Import is required (Either –E or –I can be possible)
–F –> File name to store the backup database (Ex –F C:\Database\Northwind_18Sep2011.bacpac)
–EDITION –> edition of the database whether it can be BUSINESS or WEB (Ex: - EDITION WEB)
–SIZE –> Size of the database in GB (Ex –SIZE 1) – The size must be equal to the size can get from SQL Azure (1, 5, 10, 20, 30, 40, 50 GB)
Export (Backup) the database
In this example, I am planning to backup a database Northwind2 from SQL Azure server. Below is the screenshot of SQL Azure server before taking backup.
Below is the command line for exporting (backup) the database. The backup local will be in my local computer under D:\Database. The database backup file name will be Northwind2_18Sep2011.bacpac.
DacImportExportCli.exe -S **********.database.windows.net -D Northwind2 -F D:\Database\Northwind2_18Sep2011.bacpac -X -U **username** -P **Passw@rd**
Below screenshot shows the output of the command line.
Import (Restore) the database
Below command shows how to import (restore) the database to a new database under SQL Azure server using command line tool.
In this example I am creating a new database Northwind2New under the same server exported.
DacImportExportCli.exe -S **********.database.windows.net -D Northwind2New -F D:\Database\Northwind2_18Sep2011.bacpac -I -U **username** -P **Passw@rd**
Below screenshot shows the output
Below is the screenshot of SQL Azure server after import operation completed.
Drop the database
If the Azure database imported using DAC command line, the bacpac will be registered in the Azure server. So, to delete the database better to use the DAC command line instead of using DROP DATABASE. If the database is drop by DROP DATABASE, the bacpac registration wont be removed from the server. So importing the database with the same registration name will not be possible next time.
Below is the command to drop the database
DacImportExportCli.exe -S **********.database.windows.net -D Northwind2New -DROP -U **username** -P **Passw@rd**
The screenshot of the command line output follows:
The command will drop the database Northwind2New from the SQL Azure server.
For automating SQL Azure backup using Worker Role, please look at another post Automating SQL Azure backup using Worker Role.
very nice tool, but not for large DB!
There is a new entry: DacIESvcCli.exe
The DAC is exposed as a service!
RC0 is released, http://www.microsoft.com/download/en/details.aspx?id=28147.