Background
Developers use one database to develop the solution and then need to create another environment such as Testing and Production. Once everything is ready in a development environment, we need to deploy it for a testing or staging environment. To deploy the application from one environment to another, we need to create a database or deploy a database too. We may need the same setup configuration saved in the database as well as sample test data and in some cases need the same data to troubleshoot issues.
There can be another scenario where we need to debug and check the issues with production data. We cannot directly connect and use the production or Live database for troubleshooting and fixing the issues directly in the client environment. To overcome this situation, we need the same database with the same data. If we restore the same database with data troubleshooting and deployment in testing and staging will be reduced significantly. Additionally, there can be requirements for data migration such as from the Local / On-premises database to Azure.
There are some ways to restore or create a new database with existing data in SQL Server. In this article, we will learn some technics to restore the database using available options. This article will describe how to create a database in another environment including refreshing data with the help of the BACPAC file of the existing database.
Introduction
Many developers do not know that there are options for DACPAC and BACPAC in SQL Server Management Studio. This article describes what are extract and export options available in SQL Server and How to Export BACPAC files using SQL Server Management Studio. So, this writeup explains about Data-tier application with an example of the Export option for BACPAC.
Database Create and Restore Options in SQL Server including Data
- Backup file restore
- Generate script with data
- Data-tier Application
Backup file Restore
In SQL Server, we can restore the database including data by creating a .bak file of the desired database and restore it in a new environment. However, when we create BACPAC and .bak files of the same database, the size of the BACPAC file becomes significantly smaller than the .bak file. As well as, time for creation and restore/import of file also takes comparatively less. Additionally, sometimes due to the heavy size of the large database, there can be file transfer problems for the .bak file and also SQL Server version compatibility issues.
Generate Schema with Data
There is another method to restore or create a database in which we can generate a script with data or without data and can run it to create a new database. Moreover, while generating script in SQL Server, we need to select the Advanced option to select schema and Data to create the new database with the latest data of the source database.
Additionally, if we create a new database with it, it takes much time to execute the script and insert all the data. Sometimes the data insert may throw an error. Apart from this, the size of the file becomes big.
Data-tier Application
There are two ways in the Data-tier application to restore the database with different file extensions: DACPAC and BACPAC.
DACPAC
DACPAC (Data-Tier Application Package) is a logical database entity that defines database objects for example tables, views, users, and logins. It helps to create a single package file containing database objects for the developer and database administrators.






Mahesh ChandPosted Mar 26, 2021, 2:35 PM
Thank you, Satya Karki for posting useful posts.
Kiran MohantyPosted Mar 26, 2021, 5:28 AM
Nice Article !