Export (0) Print
Expand All

How to: Import and Export a Database (Azure SQL Database)

Updated: May 22, 2014

Use Microsoft Azure SQL Database import and export operations to copy databases between Microsoft Azure SQL Database servers, or to migrate databases between on-premises instances of SQL Server and Azure SQL Database. The Azure SQL Database Import/Export Service creates a logical backup (BACPAC) file containing the schema definition and table data of a database in Microsoft Azure SQL Database. You can then recreate the database on another SQL Database server or in an on-premises instance of SQL Server.

  • Import – To import an on-premises SQL Server database to Microsoft Azure SQL Database, export your on-premises database to a BACPAC file using SQL Server Management Studio (SSMS) and upload the BACPAC file to your Blob storage container. Then you can import the BACPAC file to create a new SQL database in Azure.

  • Export – To export a database from Microsoft Azure SQL Database to an on-premises instance of SQL Server, export the database from Azure to a BACPAC file, and then use SSMS to import the BACPAC file to your on-premises instance of SQL Server.

Note that an export operation performs an individual bulk copy of the data from each table in the database, so it does not guarantee the transactional consistency of the data. To make a transactionally consistent copy of a database, use the Azure SQL Database copy database feature, and then perform the export from the copy. For more information, see Copying Databases in Azure SQL Database.

Additional Resources

For information about hosting a SQL Server database in an Azure VM, see Deploy a SQL Server Database to a Windows Azure Virtual Machine.

To read about other options for database migration, see Choosing Tools to Migrate a Database to Azure SQL Database and Migrating Data-Centric Applications to Windows Azure.

For more information about business continuity, see Azure SQL Database Business Continuity.

Before You Begin

Before you begin working with the Import/Export Service, complete the following steps:

Import a Database into Azure SQL Database

  1. Using one of the tools listed in the Before You Begin section, ensure that your Blob storage account has a container, and that the BACPAC file to be imported is in the container. To read about exporting a SQL Server database to a BACPAC file using SSMS, see Export a Data-tier Application.

  2. Log on to the Azure Platform Management Portal.

  3. Click New > Data Services > SQL Database > Import. This will open the Import Database dialog.

  4. Navigate to the .bacpac file to import: Click Storage account > Container > BACPAC and then click Open.

  5. Specify a name for the new SQL database. The database name must be unique on the server, so you cannot use the name of an existing database, and the name must comply with SQL Server rules for identifiers. For more information, see Identifiers.

  6. Specify Subscription, Edition, Max Size, and host Server details. To continue, click the Arrow at the bottom of the dialog.

  7. Specify login details for the host server.

  8. To start the import operation, click the Check mark at the bottom of the dialog. The portal will display status information in the ribbon at the bottom of the page.

  9. To view your new database, click SQL Databases in the navigation pane and refresh the page.

Export a Database

  1. Using one of the tools listed in the Before You Begin section, ensure that your Blob has a container.

  2. Log on to the Azure Platform Management Portal.

  3. In the navigation pane, click SQL Databases. In the list view of SQL databases, click the name of the database you would like to export.

  4. On the ribbon at the bottom of the page, click Export to open the Export Database dialog.

  5. Verify that the database name, Blob Storage Account, destination Container, and host Server information is correct for the SQL database you want to export. Provide the server login details. To continue, click the Check mark at the bottom of the dialog. Note that the server account must be a server-level principal login - created by the provisioning process - or a member of the dbmanager database role.

  6. You should see a message saying your export database request succeeded. After the export operation is complete, you can import your BACPAC file into a SQL database server, create a new SQL Server user database in SSMS, or initialize the set of objects in a new data-tier application using SQL Server Data Tools. You should verify that the export file can be utilized successfully.

Configure Automated Exports

Use the Azure SQL Database Automated Export feature to schedule export operations for a SQL database, and to specify the storage account, frequency of export operations, and to set the retention period to store export files.

To configure automated export operations for a SQL database, use the following steps:

  1. Log on to the Azure Platform Management Portal.

  2. In the navigation pane, click All Items> SQL Databases. In the list view of SQL databases, click the name of the database you would like to schedule for automated exports.

  3. Click the Configure tab, and then on Export Status, click Automatic.

  4. On the Automated Export work space, specify settings for the following parameters:

    • Storage Account

    • Frequency

      • Specify the export interval in days.

      • Specify the start date and time. The time value on the configuration work space is UTC time, so note the offset between UTC time and the time zone where your database is located.

    • Retention

    • Credentials for the server that hosts your SQL database. Note that the account must be a server-level principal login - created by the provisioning process - or a member of the dbmanager database role.

  5. When you have finished configuring the export settings, click Save.

  6. You can see the time stamp for the last export on under Automated Export in the Quick Glance section of the SQL Database Dashboard.

To change the settings for an automated export, select the SQL database, click the Configure tab, make your changes, and then click Save.

Create a New SQL Database from an Existing Export File

Use the Azure SQL Database Create from Export feature to create a new SQL database from an existing export file.

To create a new SQL database from an existing export file, use the following steps:

  1. Log on to the Azure Platform Management Portal.

  2. In the navigation pane, click All Items> SQL Databases. In the list view of SQL databases, click the name of a database, and then click Configure.

  3. Specify a Storage Account, click New Database, and then specify settings for the following parameters:

    • Bacpac file name - This is the source file for your new SQL database.

    • A name for the new SQL database.

    • Server – This is the host server for your new SQL database.

    • To start the operation, click the Check mark at the bottom of the page.

Import and Export a Database Using API

You can also programmatically import and export databases by using an API. For more information, see the Import Export example on Codeplex.

See Also

Community Additions

ADD
Show:
© 2014 Microsoft