Export SQL Azure Data Using SSMS
This is not all, you can even use SQL Server Management Studio (SSMS) to connect to the Windows Azure SQL Database server and then export the database using SSMS as well. It even provides more granular controls like, you can select the local file location for the BACPAC file as well as specify which objects you want to export. To do that, right click on the database in Object Explorer of SSMS and go to Tasks and then click on Export Data-tier Application as shown below:
On the Export Data-tier application wizard, specify the location for the BACPAC file which could be either a local file location or Windows Azure blob storage account as shown below:
On the Advance tab of the Export Data-tier Application wizard, you can select which database objects you want to export. In my case, as I have just one table in the database this is how it looks:
Click on the Finish button to start the export operation and once it is completed, your screen should look similar to this:
Getting a Transactional Consistent Backup
There is an important point to note here, the export operation performs a bulk copy operation on each individual table (or on selected tables if you are using SSMS) in the database and hence it does not guarantee the transactional consistency of the data. So, how do we get a transactional consistent BACPACK file?
The first option is to set the database to READ-ONLY mode, export the data to a BACPAC file and then set it back to READ-WRITE mode. But what if you cannot do that as you want your database to be fully available during export operation?
The second option is to create a database copy of the Windows Azure SQL database (which is actually a consistent copy of your original\source database at the time when it completes) and then perform an export operation from the copy of the database. Note that the SQL Database copy process is asynchronous and runs in the background without resulting in any downtime for the source database.
There are pros and cons of each of these options - in the first option, as we are not creating a copy of the existing database it does not involve additional cost but at the same time it does not guarantee the transactional consistency of the data. If we have to put the database in read-only mode to guarantee the transactional consistency of the data we are again reducing the usage time for the database for write operations. The second choice may be better as it does not impact the write operations of the database, but while you are creating another copy of the database there would be a cost involved for this second copy of the database.
SQLPackage.exe (Data-Tier Application client tool) is a command line utility that can be used to export or import a BACPAC file. For more information, see SqlPackage.exe.
Windows Azure SQL Database - Import
If you have a BACPAC file, you can then restore\recreate the database on the same Windows Azure SQL Database server or another Windows Azure SQL Database server or an on-premises instance of the SQL Server Database Engine. Windows Azure SQL Database has an import service to import from the BACPAC file. On the Windows Azure portal, under SQL Databases section click on the Import link available at the bottom as shown below:
Clicking on the Import icon will bring up the following screen, here you need to select the BACPAC file, specify the name of the database to be created and server information as shown below. If you check "Configure Advance Database Settings" it will allow you to specify the edition of the database you want to create (Web or Business) and size of database:
When you start the import operation, you can monitor the progress as shown below:
Once the import operation is successfully completed, you can see your new database as shown below:
Like export, SSMS allows importing the database from a BACPAC file. To launch it, right click on the Database node in the Object Explorer of SSMS then click on Import Data-tier Application menu item as shown below:
On the first screen of the Import Data-tier Application wizard, just click on Next button to move to the next screen of the wizard:
On the Import Settings screen of the wizard specify the location of the BACPAC file (BACPAC file could be from local file system or Windows Azure blob storage) and then click on the Next button to move ahead:
On the Database Settings screen of the Import Data-tier Application, specify the server instance, edition and maximum size of the database to be created as shown below:
The Import Data-tier Application wizard should look like this after completion of the import operation from the BACPAC file.
Windows Azure SQL Database - Automating Exports
Windows Azure SQL Database provides a mechanism for automating the process for exporting a database to BACPAC files on a set interval and frequency. In order to set up the automatic export process, go to the Configure page of the database on the Windows Azure portal and select Automatic as shown below. Next you can specify the storage account where the BACPAC files will be stored after export, authentication mechanism to connect for exporting, frequency of export and retention settings for the BACPAC files:
No comments:
Post a Comment