SALES: 1-800-867-1380

How to use the Azure Import/Export Service for SQL Server Files

Updated: April 4, 2014

This topic provides information on how to use Azure Import/Export Service for SQL Server databases. We recommend that you consider using the Azure Import/Export Service for large databases as an alternative migration option to/from Azure. This service allows you to transfer large amounts of file data in hard drives from on-premises to Azure Blob storage or from Azure Blob Storage to on-premises. For example, you can send one or more hard drives containing data to an Azure data center to import data to Azure Blob Storage. Similarly, you can send empty hard drives to an Azure data center to export data. The export process copies the Blob data from your Azure storage account to your hard drives and then returns to you.

To create and manage import and export jobs, you can use Azure Management Portal or Import/Export REST API Service.

The following sections explain the workflow that you can follow when importing and exporting large SQL Server files from on-premises to Azure, and from Azure to on-premises.

Azure Import Service for SQL Server files

The following table describes how to use the Azure Import Service to import data, log, and backup files from on-premises:

 

Source Server

Destination Server

How to use Azure Import Service

SQL Server (on-premises)

SQL Server (Azure virtual machine)

For data and log files:

  1. Import data and log files from on-premises to Azure Blob Storage using Azure Import Service.

  2. To create the database in a virtual machine in Azure, perform one of the following two ways:

For backup files:

  1. Import backup files from on-premises server to Azure Blob Storage using Azure Import Service.

  2. To restore the database to an instance of SQL Server in Azure, perform one of the following two ways:

    • Restore the database from Azure Storage by using the RESTORE DATABASE FROM URL statement.

    • Get backup files from Azure Storage to your VM in Azure by using Blob Service API or a third-party Azure storage tool. Then, restore the databases from the local backup files to an instance of SQL Server in a virtual machine in Azure.

Azure Export Service for SQL Server files

The following table describes how to use the Azure Export Service to export data, log, and backup files to on-premises:

 

Source Server

Destination Server

How to use the Azure Export Service

SQL Server (Azure virtual machine)

SQL Server (on-premises)

For data and log files:

  1. Detach the database in the source server in Azure. For databases that are created by using the SQL Server Data Files in Azure feature, the data and log files reside in Azure Storage automatically. For other databases, the data and log files reside in a local folder in your Azure virtual machine and you copy them to Azure Storage using Blob Service API. After the detach process is done, use the Azure Export Service to move the data and log files to on-premises in a hard drive.

  2. Copy the data and log files from the hard drive to your on-premises server. Next, use the CREATE DATABASE … FOR ATTACH statement to attach the database to your on-premises SQL Server instance.

For backup files:

  1. Back up the database in the source server in Azure using the SQL Server Backup to URL feature or the Backup statement. The SQL Server Backup to URL places the backup files in Azure Storage automatically. If you use the Backup statement, the backup files reside in a local folder in Azure virtual machine and you copy them to Azure Storage using Blob Service API. After the backup process is done, use the Azure Export Service to move the backup files to on-premises in a hard drive.

  2. Copy the data and log files from the hard drive to your local on-premises server. Next, use the RESTORE DATABASE statement to restore the database in an on-premises server.

Important recommendations

The Azure Import/Export service allows you to upload or download data to/from Azure Storage block blobs and page blobs. When using SQL Server Data Files in Azure or SQL Server Backup to URL features, you need to use Page blobs, which can be up to 1 TB in size. If your backup/data/log file is larger than 1 TB, you should split it in multiple files, such as by performing backup to multiple devices or by using multiple data or log files with the MAXTRANSFERSIZEparameter set. In addition, when performing RESTORE from URL, you need to set the BLOCKSIZE parameter to 4096 explicitly. Otherwise, an error will occur.

See Also

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft