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 create or Export BACPAC file from SSMS
- Login into SSMS with the respective login details.
- Go to the Object Explorer section and select the SQL instance.
- Expand the databases folder and select the database which you want to export. Here, we have selected test_henry database as an example
- Right-click on it and select the tasks option. After then go to the option Export Data-Tier Application and click on it.
Here, we have given a short description of various methods available in the SSMS for export.
Extract Data-tier Application: Using this option we can extract the database with schema only without any data and the file hence created is called as DACPAC package. If we need schema only, we can choose this option.
Export Data-tier Application: Using this option we can extract schema as well as data in the file which is called as BACPAC file. We have chosen this option as an example in this article.
Upgrade Data-tier Application: we can upgrade the existing database using this option.
- Once you click on it, a new window will be opened with the name Export Data-tier Application. Click on the Next button to proceed further.
- In the Export Settings, you need to select where you want to store the backup of the database in your local drive or remotely to Microsoft Azure. Here, we have selected the Save to local disk option because we want to store it on the local drive. Click on the browse option and select the location where you want to store the file and give the file name. Then click on the Next button as shown below.
- In this window, you will get a summary where you can see details of your Source and destination (target) location. Click on the Finish button to proceed further.
- By clicking on it, it will start exporting 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 backup file by navigating to the directory and verify a BACPAC extension file exist. That's all.