Download Azure SQL data to a local device to enable "Data Collection" for enterprises 」,

Download Azure SQL data to a local device to enable "Data Collection" for enterprises 」,

Too long to read

This article describes how to migrate content in the Azure SQL database to another location (such as the local environment) through copying and exporting operations. This helps you retain an additional copy locally or elsewhere while running the database in Azure to meet requirements of systems or businesses.

 

Operation Details Edition

Many users have raised the following question to xiaobian: Azure SQL database service is already in use. Although this service has a sound data backup and disaster recovery mechanism, however, due to the requirements of the company's system or other aspects, You need to regularly download the full content of the database to the local storage, even if it is manual operation.

This is certainly acceptable, but I would like to emphasize the various data protection mechanisms provided by the Azure SQL service again:

• Azure SQL provides 99.99% of service level agreements at the database level (basic, standard, and advanced.

• Pro provides the active region replication feature to create up to 4 readable secondary copies in any Azure region and control when and where to perform failover.

• Self-help restoration allows you to better control "recovery under adverse conditions". You can control the entire process of data restoration from available data backup copies.

• In advanced edition, SQL databases can save backup copies for up to 35 days for restoration at any time.

Now let's clarify these questions and see how to save the data in Azure SQL locally.

 

Method 1: Copy Azure SQL database

For Azure SQL databases, we can create transaction consistency copies of existing Azure SQL databases on the same or different servers in multiple ways. A database copy is a snapshot of the source database as of the time when the copy request is sent. We can select different performance levels (versions) in the same or different servers, their service layer and performance level, and the same service layer ). After the copy is completed, the copy will become an independent database that can fully perform the function. In this case, you can upgrade or downgrade it to any version.

We can copy the Azure SQL database to a local directory (or another location) through the Azure portal, PowerShell, or Transact-SQL as needed ).

Take operations under the Azure portal as an example. You only need to log on to the Azure Management Portal, open the corresponding configuration interface of the database to be copied, and click Copy on the toolbar 」, select the target server to be copied.

Of course, you can also use PowerShell to copy the New-AzureRmSqlDatabaseCopy cmdlet:

For more information about this method and more specific operations, see copy Azure SQL database.

Method 2: Export Azure SQL database as a BACPAC File

In addition to copying, you can also perform export operations to obtain the BACPAC file corresponding to the Azure SQL database. This method is more suitable for data archiving or transfer to other platforms.

The BACPAC file is a ZIP file with the extension of BACPAC. It contains metadata and data from the SQL Server database. We can store BACPAC files in Azure Blob storage or local storage, and then import them back to Azure SQL database or SQL Server for local installation.

Note the following before using this method:

• To ensure that the exported transaction processing method is consistent, you must ensure that no Write activity is performed during the export period, or you are exporting from the consistent copy of the Transaction Processing Method of the Azure SQL database.

• If it is exported to Blob storage, the maximum size of the BACPAC file is 200 GB. To archive a larger BACPAC file, export it to local storage.

• You cannot use this method to export a BACPAC file to Azure advanced storage.

• If you export data from the Azure SQL database for more than 20 hours, the system may cancel this operation.

You can use the Azure portal, SQL Package utility, SQL Server Management Studio, and PowerShell to export the Azure SQL database as a BACPAC file.

Take operations under the Azure portal as an example. You only need to log on to the Azure Management Portal, open the corresponding configuration interface of the database to be exported, and click Export on the toolbar 」, select the storage location to export to and other options.

You can also use PowerShell to submit a Database Export request to the Azure SQL database service by using the New-AzureRmSqlDatabaseEmport cmdlet.

For more information about this method and more specific operations, see Export Azure SQL database to the BACPAC file.

 

Visit http://market.azure.cn now