A BACPAC, short for Backup Package, is a comprehensive file format that encapsulates both the schema and data of a SQL Server database. By exporting a database as a BACPAC file, you can easily import it into a new environment, creating a new database with the same structure and content. This functionality proves valuable for tasks such as migrating data between different environments or upgrading to a different SQL Server version.
BACPAC files have emerged as the preferred choice for Database Administrators and developers regarding database migration or restoration. They offer a streamlined and efficient method for transferring databases, ensuring that the schema and data remain intact. Whether moving data between environments or restoring databases for various purposes, BACPAC files provide a reliable solution for managing database changes effectively.
Steps to create or Export BACPAC file from SSMS
1. Login into SSMS with the respective login details.
2. Go to the Object Explorer section and select the SQL instance.
3. Expand the databases folder and select the database you want to export. Here, we have selected the test_henry database as an example
4. Right-click on it and select the tasks option. Afterward, go to the Export Data-Tier Application option and click on it.
Here, we have briefly described 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 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 and data in a file called a 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.
5. A new window with the name Export Data-tier Application will be opened once you click on it. Click on the Next button to proceed further.
6. 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.
7. 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.
8. By clicking on it, it will start exporting 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.
10. You can verify the backup file by navigating to the directory and verify a BACPAC extension file exist. That's all.
