Importing a data-tier application to SQL Server 2016

You have seen how to export a data-tier application of a database in  SQL Server 2012. In fact, we exported one of the oldest of Microsoft's Sample Databases, the TestPubs. We also saw some limitations in this process of export due to incompatible data, although we have not fathomed as yet what type of data is incompatible.

When the process of export succeeds you create a .bacpac file which can be saved either to a local drive or to Microsoft Azure SQL Database. In our previous export we exported a TestPubs.bacpacfile from TestPubs database to the local drive.

In this post, you will see how you can import it into a higher version of SQL Server, SQL Server 2016.

Start up the named instance of SQL Server 2016. You can see that the server has no user database presently except the one we created recently (AdventureWorks2014).

ImportDT_07

Make a right click on the Databases node in SQL Server 2016 as shown.

ImportDT_00

Click on Import Data-tier Application... submenu to launch the import wizard's Introduction page as shown. Read the instructions on this page.

ImportDT_01

Click Next to display the Import Settings page. Click on the ellipsis button to browse the default page where the .bacpac files are stored as shown.

ImportDT_02

The file enters the processing by the wizard as shown.

ImportDT_03

Click Next to display the Database Settings page. The deployment is targeted to the SQL Server 2016 and .mdf and .ldf files are created. You can change the database name and herein it remains unchanged.

ImportDT_04

Click Next to display the Summary page as shown - Deployment details, file and target as shown.

ImportDT_05

Click Finish. There is little bit of processing activity as shown.

ImportDT_06

The operation has succeeded as shown.

ImportDT_08

The TestPubs database is now found in SQL Server 2016 (you may need to Refresh the Databases node).

ImportDT_09
The data has also entered the database as shown in one of the queries on the TestPubs in SQL Server 2016.

ImportDT_10

Well. It is quite simple and not a line of code is needed!

Exporting a Data-tier application from SQL Server using SSMS has a problem

According to MSDN (https://msdn.microsoft.com/en-us/library/hh213241.aspx)  it is quite simple to Export a data-tier application. Here is an overview of exporting a database as a Data-tier .bacpac file which can be deployed to another server.

"Exporting a deployed data-tier application (DAC) or database creates an export file that includes both the definitions of the objects in the database and all of the data contained in the tables. The export file can then be imported to another instance of the Database Engine, or to Azure SQL Database. The export-import operations can be combined to migrate a DAC between instances, to create an archive, or to create an on-premise copy of a database deployed in SQL Database.
"
The steps are very clear as the Export data-tier application task brings up a wizard and you just have to follow the steps.
The following screen shots show the process for exporting from a SQL Server 2012 instance.

Initiating the Export Data-Tier Wizard. Click Export Data-tier Application...

Wizard shows up.

Click Next.

In the above you can save it to a local disk (Folder/File) or save to Microsoft Azure. Here the default is accepted.

The Next is greyed as there was an earlier file. However the database had changes and a new one was created with a different name.

Data in the tables will also be exported.

Click Finish. The Wizard starts processing.

The result of processing is that the operation did not succeed.

The reason is in the error shown below.

Looks like some implied incompatibility with some Microsoft Azure SQL database. It may be noted that the target file was supposed to be saved to the local disk and not Microsoft Azure.

This happens in SQL Server 2014 as well.

According to a recent MSDN forum post, the failure in SQL Server 2012 and SQL Server 2014 for this particular case of Northwind database is perhaps related its

Read these popular blog posts related to data-tier applications:
http://hodentekmsss.blogspot.com/2010/07/sql-server-2008-r2-and-data-tier.html
http://hodentekmsss.blogspot.com/2013/12/deploying-dac-to-sql-server-2014.html