BACPAC (Backup package) contains schema and data for SQL Server database. We can export the database in BACPAC and import it in 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 Administrator and developers for migration or restore database for various purposes.
Steps to import/restore BACPAC file using SSMS
- Login into SSMS with the respective login details.
- Go to the Object Explorer section and select the SQL instance where you want to restore the database.
- Right-click on the databases folder and go to the Import Data-tier Application option and click on it.
- You will get a new window with the name Import Data-tier Application. Click on the Next button as shown in the below picture.
- 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 then you need to select the option 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 bacpac format. Then click on the Next button to proceed further.
- 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 log file of this new database. By default, it shows you the default data and log 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 which you want to restore already exists.
Edit the new database name. Here we have entered test_henry_bacpac as an example. Click on the Next button to proceed further.
- In the Summary window, you will get details of the source and target location. We always recommend you to 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.
- By clicking on it, it will start importing the database schema, data, store procedure, and view, etc. It may take a few minutes to complete.
- Once it is 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.
- You can verify the database is imported by refresh the object explorer. You can see that test_henry_bacpac is imported. That's all.