How to find the current version of a SQL Server data-tier application?

Question

We are using a SQL Server data-tier application (dacpac or DAC package) and it’s hard for me to find the current version of the database.

Is there a way to get the current version using any of these methods:

• In SQL Server Management Studio
• Using SQL statement
• Programmatically Using .NET Code

Answer 1

In SQL Server Management Studio

From http://msdn.microsoft.com/en-us/library/ee210574.aspx

To view information about the DAC deployed in an instance of the database engine:

• Select View / Object Browser .

• Connect to the instance from the Object Browser panel.

• Select the View / Browser Object menu.

• Select the node server in the Object Browser that maps to the instance, and then go to Office \ Application node level data .

• The list view in the upper pane of the details page lists all the DACs deployed in the Database Engine instance. Select the DAC to display the information in the details panel at the bottom of the page.

The right-click menu for node-level data applications is also used to deploy a new DAC or delete an existing DAC.

Using SQL statement

SELECT instance_name, type_version FROM msdb.dbo.sysdac_instances 

Via SQL statement on Azure

 SELECT instance_name, type_version FROM master.dbo.sysdac_instances 

Programmatically Using .NET Code

Please note that in DacFx 3.0 this is no longer valid. See my other answer to do this.

FROM#

 ServerConnection serverConnection; string databaseName; // Establish a connection to the SQL Server instance. using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString)) { serverConnection = new ServerConnection(sqlConnection); serverConnection.Connect(); // Assumes default database in connection string is the database we are trying to query. databaseName = sqlConnection.Database; } // Get the DAC info. DacStore dacstore = new DacStore(serverConnection); var dacInstance = dacstore.DacInstances[databaseName]; System.Diagnostics.Debug.Print("Database {0} has Dac pack version {1}.", databaseName, dacInstance.Type.Version); 

Vb.net

 Dim serverConnection As ServerConnection Dim databaseName As String ' Establish a connection to the SQL Server instance. Using sqlConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString) serverConnection = New ServerConnection(sqlConnection) serverConnection.Connect() ' Assumes default database in connection string is the database we are trying to query. databaseName = sqlConnection.Database End Using ' Get the DAC info. Dim dacstore As New DacStore(serverConnection) Dim dacInstance = dacstore.DacInstances(databaseName) System.Diagnostics.Debug.Print("Database {0} has Dac pack version {1}.", databaseName, dacInstance.Type.Version) 

Answer 2

In DacFx 3.0, DacStore is no longer available. To get the version from C # code, you need to query the database. Here is an example:

  using System; using System.Data; using System.Data.SqlClient; class Program { static void Main() { try { string version = GetDatabaseVersion(@"Initial Catalog=xxx;Data Source=yyy;Integrated Security=True;Pooling=False", false); Console.WriteLine("Database has DAC pack version {0}.", version); } catch (Exception ex) { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine(ex.Message); Console.WriteLine(); Console.ResetColor(); } Console.WriteLine("Press any key to exit"); Console.ReadKey(true); } /// <summary> /// Gets the database version. /// </summary> /// <param name="connectionString">The connection string of database to query.</param> /// <param name="isAzure">True if we are querying an Azure database.</param> /// <returns>DAC pack version</returns> private static string GetDatabaseVersion(string connectionString, bool isAzure) { var connectionStringBuilder = new SqlConnectionStringBuilder(connectionString); string instanceName = connectionStringBuilder.InitialCatalog; string databaseToQuery = "msdb"; if (isAzure) { // On Azure we must be connected to the master database to query sysdac_instances connectionStringBuilder.InitialCatalog = "Master"; databaseToQuery = "master"; } string query = String.Format("select type_version from {0}.dbo.sysdac_instances WHERE instance_name = '{1}'", databaseToQuery, instanceName); using (var connection = new SqlConnection(connectionStringBuilder.ConnectionString)) { connection.Open(); SqlCommand command = connection.CreateCommand(); command.CommandText = query; command.CommandTimeout = 15; command.CommandType = CommandType.Text; var version = (string)command.ExecuteScalar(); return version; } } 

}