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 development environment, we need to deploy it for testing or staging environment. To deploy the application from one environment to another, we need to create database or deploy database too. We may need same setup configuration saved in database as well as sample test data and some cases need 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 production or Live database for troubleshooting and fixing the issues directly in client environment. To overcome this situation, we need same database with same data. If we restore same database with data troubleshooting and deployment in testing and staging will be reduce significantly. Additionally, there can be requirement of data migration such as from Local / On-premises database to Azure.
There are some ways to restore or create new database with existing data in SQL Server. In this article we will learn some technics to restore database using available options. This article will describe how to create database in another environment including refreshing data with the help of BACPAC file of existing database.
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 file using SQL Server Management Studio. So, this writeup explains about Data-tier application with an example of 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 database including data by creating .bak file of desire database and restore it in new environment. However, when we create BACPAC and .bak file of same database, the size of BACPAC file becomes significantly smaller than bak file. As well as time for creation and restore/import of file also takes comparatively less. Additionally, sometime due to heavy size of large database there can be file transfer problem for .bak file.
Generate Schema with Data
There is another method to restore or create database in which we can generate script with data or without data and can run it to create 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 latest data of source database. Additionally, if we create new database with it, it takes much time to execute script and insert all the data. Sometime the data insert may throw error. Apart from this, size of file becomes big.
There are two ways in Data-tier application to restore database with different file extensions: DACPAC and BACPAC.
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 single package file containing database objects for the developer and database administrators.
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 only schema we can go with DACPAC. If we need schema with Data then we can choose 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
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.
Click on Browse and select location where you want to keep the file and give File Name. Then click on Next as depicted below.
Summary window will come as shown below where you can see details of your Source and target location. Click on Finish to proceed further.
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.
Hence, the article has described what are the options to restore and create database as well as explained about 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 next article, we will learn how to restore or import the BACPAC file.