Download data from Azure SQL locally to meet your enterprise's "data collection"

Too long not to see the version

This article describes how to transfer the contents of an Azure SQL database to a different location, such as your on-premises environment, by copying and exporting two operations. This helps users to keep a copy of the database in Azure while they are running it, either locally or elsewhere, to meet the requirements of the system or business.

Operation Detail Edition

Many users have asked the small part of the question: already using Azure SQL database service, although the service has a very good data backup and disaster recovery mechanism, but for the company system or other requirements, it is necessary to regularly download the full contents of the database to local storage, even if manual operation is possible.

This is certainly possible, but first of all, I would like to highlight the various data protection mechanisms provided by Azure SQL Services:

• Azure SQL provides 99.99% service-level agreements at the database level (basic, standard, advanced three service levels).

• Premium provides proactive geo-replication capabilities to create up to 4 readable secondary replicas in any Azure region and control when and where to fail over.

• Self-service restores allow for better control of "recovery under adverse conditions", and customers can control the entire process of data restoration from a backup copy of available data.

• In Premium Edition, a SQL database can keep a backup copy for up to 35 days for you to recover at any time.

Well, once you've identified these issues, see how you can save data from Azure SQL locally.

Method One: Copy the Azure SQL database

For Azure SQL database, we can create transactional consistent copies of existing Azure SQL databases on the same or different servers in several ways. A database copy is a snapshot of the source database as of the time the replication request is issued, and we can choose the same or different servers, their service tiers and performance levels, and the different performance levels (versions) in the same service tier. After the replication is complete, the replica becomes a standalone database capable of fully functioning. At this point, you can upgrade or downgrade it to any version.

We can replicate Azure SQL databases locally (or elsewhere), depending on your needs, through the Azure portal, PowerShell, or Transact-SQL.

As an example of what's going on in the Azure portal, just sign in to the Azure Management portal, open the appropriate configuration interface for the database you want to copy, then click Copy on the toolbar and select the target server you want to copy to, which is OK.

Of course, you can also use the New-azurermsqldatabasecopy cmdlet for replication via PowerShell:

A further introduction to this approach, as well as a more detailed approach to how to do this, can be found in: Replicating Azure SQL database.

Method Two: Export Azure SQL database to a BACPAC file

In addition to copying, you can also perform an export operation to obtain the BACPAC file for your Azure SQL database. This approach is better suited for purposes such as data archiving or transferring to other platforms.

The BACPAC file is a ZIP file with an extension of BACPAC that contains metadata and data from the SQL Server database. We can store BACPAC files in Azure Blob storage or local storage, which can then be imported back into Azure SQL database or SQL Server Local installation.

There are several issues to note before using this method:

• To ensure that the exported transactions are consistent, you must ensure that no write activity occurs during export, or that you are exporting from a transactionally consistent copy of Azure SQL database.

• If you are exporting to BLOB storage, the maximum size of the BACPAC file is 200GB. To archive a larger BACPAC file, export to local storage.

• Using this method to export BACPAC files to Azure Premium storage is not supported.

• If an export operation from an Azure SQL database exceeds 20 hours, the operation may be canceled.

Azure SQL database can be exported to a BACPAC file through the Azure portal, the SQL Package utility, SQL Server Management Studio, and PowerShell.

For example, in the Azure portal, just sign in to the Azure Management portal, open the appropriate configuration interface for the database you want to export, then click Export on the toolbar and select the storage location and other options you want to export to.

You can also use the New-azurermsqldatabaseemport cmdlet to submit an export database request to the Azure SQL database service via PowerShell.

A further introduction to this approach, as well as a more detailed approach to how to do this, can be found in: Exporting Azure SQL database to a Bacpac file.

Visit http://market.azure.cn Now

Download data from Azure SQL locally to meet your enterprise's "data collection"