How to import or restore BACPAC file from SSMS?

BACPAC (Backup package) contains schema and data for the SQL Server database. We can export the database in BACPAC and import it into a new environment as a new database. It can be used to migrate data from one environment to another or one SQL Version to another. It has become the best choice for Database Administrators and developers for migrating or restoring databases for various purposes.

Steps to import/restore BACPAC file using SSMS

1. Log in to SSMS with the respective login details.

2. Go to the Object Explorer section and select the SQL instance where you want to restore the database.

Object Explorer

3. Right-click on the databases folder, go to the Import Data-tier Application option, and click on it.

Select Database

4. You will get a new window named Import Data-tier Application. Click on the Next button, as shown in the below picture.

Import Data-tier Application

5. In the Import Setting window, you need to select the option from where you want to restore the database. If you have stored the backup in the local drive, then you need to select Import from the local disk.

If you have stored the backup in the remote system, such as in Windows Azure, you need to select Import from Windows Azure. Here, we have stored the database backup in the local drive, so we are going to select the Import from the local disk.

Click on the browse option and select the location where you have stored the backup of the database in the bacpac format. Then click on the Next button to proceed further.

Backup Database in BACPAC Format

6. In the Database Settings window, we need to specify a setting for the new database. We cannot use an existing SQL Database for importing a BACPAC package.

It also requires input for the data and logs file of this new database. By default, it shows you the default data and logs file location of the connected SQL instance. We can change it per requirement.

As mentioned earlier, we cannot use an existing database for BACPAC file import. We will get the following error message if the database you want to restore already exists.

Duplicate Database

Edit the new database name. Here we have entered test_henry_bacpac as an example. Click on the Next button to proceed further.

Edit New Database Name

7. In the Summary window, you will get details of the source and target location. We always recommend you review it before proceeding. If something is missing or incorrect, you can easily navigate back and make the required changes. Click on the Finish button to start the import process.

Start the Import Process

8. By clicking on it, it will start importing the database schema, data, store procedure, view, etc. It may take a few minutes to complete.

9. Once completed, you will get a message on the screen Operation complete. You can see a success message under the result section. Click on the close button to close the window.

Import Completed

10. You can verify the database is imported by refreshing the object explorer. You can see that test_henry_bacpac is imported. That's all.

Verify the database

Was this answer helpful?

« Back