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.
BACPAC
BACPAC (Backup package) contains schema and data for SQL Server database. We can generate these files and deploy it in multiple environments. Additionally, we can use to migrate data from one environment to another for example on-premises to Azure SQL Database or online.
If we need the only schema we can go with DACPAC. If we need schema with Data then we can choose the BACPAC method.
Advantages of Data-tier Application
- It has no version compatibility issue. It helps to migrate data from different sources to targets having different SQL versions. It outweigh the script based practices and .bak file because it can handle to restore or migrate from older version to newer version of SQL.
- In upgrade, it warns if there might be any data loss and provides upgrade plan. Based on Plan DBA or Developer can proceed further.
- It compresses the data hence, the file size becomes smaller.
Steps to Create or Export BACPAC file
Step 1
Open SSMS and connect to your SQL Instances. Expands your databases under the SQL instances and Right click on Data which you want to export for data. Then you will get option as shown in below image.
Here, in this stage you can see three options
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 will choose this option in demo example in the article.
Upgrade Data-tier Application: we can upgrade existing database using this option.
As we are talking about BACPAC in this article, Click on Export Data-tier Application. You will get below window, click on Next to proceed further.
Step 2
Click on Browse and select location where you want to keep the file and give File Name. Then click on Next as depicted below.
Step 3
Summary window will come as shown below where you can see details of your Source and target location. Click on Finish to proceed further.
Step 4
It starts exporting the database schema, data, store procedure and view etc. This may take few second to minutes. Wait this process to complete.
When the export process is completed you can close the window and see your file. Now, your export is done, you can import this file to Azure SQL or another Server or any machine’s SQL Server according to your need.
Conclusion
This article has described the options to restore and create databases as well as data-tier application import/export options with an example of BACPAC. I hope, it will help you to manage and create your database in new environment including schema and data either in Azure or local database server using SSMS. In the next article, we will learn how to restore or import the BACPAC file.