Muokkaa

Jaa

Import a BACPAC file to create a new database

  • Artikkeli

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

Import a .bacpac file - to create a copy of the original database, with the data, on a new instance of the Database Engine, or to Azure SQL Database. An export operation can be combined with an import operation to migrate a database between instances or to create a copy of a database deployed in Azure SQL Database. Options for easily importing a .bacpac include:

  1. the Import Data-tier Application Wizard in SQL Server Management Studio
  2. the Deploy Database to Microsoft Azure SQL Database Wizard in SQL Server Management Studio to deploy a database between an instance of the Database Engine and a Azure SQL Database server, or between two Azure SQL Database servers
  3. the SqlPackage command-line utility

Before You Begin

The import process builds a new database in two stages.

  1. The import creates a new database using the database model definition stored in the .bacpac export file, the same way a .dacpac deploy creates a new database from the definition in a .dacpac file.

  2. The import bulk copies in the data from the .bacpac export file.

Database Options and Settings

By default, the database created during the import has all of the default settings from the CREATE DATABASE statement, except that the database collation and compatibility level are set to the values defined in the .bacpac export file. A .bacpac export file uses the values from the original database.

Some database options, such as TRUSTWORTHY, DB_CHAINING, and HONOR_BROKER_PRIORITY, can't be adjusted as part of the import process. Physical properties, such as the number of filegroups, or the numbers and sizes of files can't be altered as part of the import process. After the import completes, you can use the ALTER DATABASE statement, SQL Server Management Studio, or SQL Server PowerShell to tailor the database. For more information, see Databases.

Security

To improve security, SQL Server Authentication logins are stored in a .bacpac export file without a password. When the file is imported, the login is created as a disabled login with a generated password. To enable the logins, sign in with ALTER ANY LOGIN permission and use ALTER LOGIN to enable the login and assign a new password. This extra step isn't needed for Windows Authentication logins because their passwords aren't managed by SQL Server.

Permissions

A .bacpac can only be imported by members of the sysadmin or serveradmin fixed server roles, or by logins that are in the dbcreator fixed server role and have ALTER ANY LOGIN permissions. The built-in SQL Server system administrator account named sa can also import a .bacpac. Importing a .bacpac with logins to SQL Database requires membership in the loginmanager or serveradmin roles. Importing a .bacpac without logins to SQL Database requires membership in the dbmanager or serveradmin roles.

The Deploy Database to Microsoft Azure SQL Database Wizard in SQL Server Management Studio requires more permissions to export the source database. The login requires at least ALTER ANY LOGIN and database scope VIEW DEFINITION permissions, as well as SELECT permissions on sys.sql_expression_dependencies. Members of the securityadmin fixed server role who are also members of the database_owner fixed database role are permitted to export a .bacpac. Members of the sysadmin fixed server role or the built-in SQL Server system administrator account named sa can also export a .bacpac.

Use the Import Data-tier Application Wizard

To launch the wizard, use the following steps:

  1. Connect to the instance of SQL Server, whether on-premises or in SQL Database.

  2. In Object Explorer, right-click on Databases, and then select the Import Data-tier Application menu item to launch the wizard.

  3. Complete the wizard dialogs:

Introduction Page

This page describes the steps for the Data-tier Application Import Wizard.

Options

  • Do not show this page again. - Select the check box to stop the Introduction page from being displayed in the future.

  • Next - Proceeds to the Import Settings page.

  • Cancel - Cancels the operation and closes the wizard.

Import Settings Page

Use this page to specify the location of the .bacpac file to import.

  • Import from local disk - Select Browse... to navigate the local computer, or specify the path in the space provided. The path name must include a file name and the .bacpac extension.

  • Import from Azure - Imports a .bacpac file from a Microsoft Azure container. You must connect to a Microsoft Azure container to validate this option. The Import from Azure option also requires that you specify a local directory for the temporary file. The temporary file will be created at the specified location and will remain there after the operation completes.

    When browsing Azure, you're able to switch between containers within a single account. You must specify a single .bacpac file to continue the import operation. You can sort columns by Name, Size, or Date Modified.

    To continue, specify the .bacpac file to import, and then select Open.

Database Settings Page

Use this page to specify details for the database that will be created.

For a local instance of SQL Server:

  • New database name - Provide a name for the imported database.

  • Data file path - Provide a local directory for data files. Select Browse... to navigate the local computer, or specify the path in the space provided.

  • Log file path - Provide a local directory for log files. Select Browse... to navigate the local computer, or specify the path in the space provided.

To continue, select Next.

For an Azure SQL Database:

Validation Page

Use this page to review any issues that block the operation. To continue, resolve blocking issues and then select Re-run Validation to ensure that validation is successful.

To continue, select Next.

Summary Page

Use this page to review the specified source and target settings for the operation. To complete the import operation using the specified settings, select Finish. To cancel the import operation and exit the wizard, select Cancel.

Progress Page

This page displays a progress bar that indicates the status of the operation. To view detailed status, select the View details option.

To continue, select Next.

Results Page

This page reports the success or failure of the import and creates database operations, showing the success or failure of each action. Any action that encountered an error has a link in the Result column. Select the link to view a report of the error for that action.

Select Close to close the wizard.

Related content