Import BACPAC file to SQL Server

BACPAC files are the ones widely using now a days and getting popular day to day for the ease of distribution of your database with others, where this file can be easily imported either in Microsoft SQL Server or Microsoft Azure SQL Database.

The BACPAC file is internally a ZIP file with the extension of BACPAC which encapsulates the database schema as well as data stored in database.


Here is the sample BACPAC file with its extension.

Sample BACPAC file

When you rename the BACPAC file to ZIP format and open, it looks like this.

BACPAC in ZIP file format
From the above picture, model.xml file contains the database schema information and Data folder has table data.

Now, we will look into how this BACPAC file can be imported into Microsoft SQL Server Database. Later I will post one more article on how to import the BACPAC file on to Microsoft Azure SQL Database.

Firstly, go to the SQL Server Management Studio, right click on Database folder and click on “Import Data-tier Application” option.

BACPAC file Import Option in SQL Server

 SQL Server Management Studio (SSMS) will launch below dialog with introduction saying what you must do in the next screen in order to import the database from BACPAC file. Click on Next.

Import Data-tier Application - Introduction

In the next screen you need to specify the location of the BACPAC file to import and create a new database.

There are two ways you can provide the BACPAC as shown below. One is from local file system and another is from the Microsoft Azure Storage.

In this article we are going to provide the BACPAC file which is on local machine.

Import Data-tier Application - Import Settings

In the next screen you will be asked to confirm the name of the new database and paths of the database data file and log file where they will be added.

By default, new database name is given as name of the BACPAC file which is given in previous screen. And if you would like to change the location of the data file and log file, then you are free to change it from this screen.

Here I’m going to keep the name of BACPAC file as the name of database. And, click next.

Import Data-tier Application - Database Settings

In the next screen you will see the summary of the import details to verify the same as shown below. If you feel it needs to be changed then you can go back and re-update the information by clicking the ‘Previous’ button, else click Finish.

Import Data-tier Application - Summary

The final screen will show the results of the import BACPAC file activity as shown below. In case for any reason if your BACPAC file is not imported, the Result column in below screen will show the Error with link, and on click on it you will see the reason why it failed, so that you can rectify and reimport the same.

Import Data-tier Application - Results

Click on ‘Close’ button which will close the import wizard dialog. And, go back to object explorer and refresh Databases folder to see the imported database called 'SampleDB' as shown below.

Database created from BACPAC file
That’s it! These are the only steps involved in importing BACPAC file to SQL Server. Hope this article helps you and if you any queries, please feel free to post them from below comments section.

No comments:

Powered by Blogger.