Share via

DAC Powershell Samples

  • Article

All the samples in this document are based on the DAC module that you can download from the attached zip folder (DACModule.zip).To import the modules,

Step 1) check your Module Path

PS > dir Env:PSModulePath

Step 2) chose any of the module paths and copy the attached folder (after unzipping) to that location. You might have to create any missing folders on the module path. Once copied, you can import the modules into your powershell by running the command:

PS > Import-Module DACModule

DACModule is the name of the module and is also the name of the psm1 file. This operation needs your PowerShell to enable scripting through an execution policy. To learn more about the execution policy, please run

PS > Get-Help Set-ExecutionPolicy

Functions

 

To view the functions in the module, you can run the below

PS > Get-Command -Module DACModule

The result of this command provides us with the available functions in the module. All functions have ‘help’ argument that show more information about the function.

1. AddExtraFile-Dac: This enables the user to add an extra file to a given DAC type. The user can also provide a tag to be put on the extra file.

2. AddpostDeployFile-Dac: This enables the user to add a post deployment file on a given DAC. The tag is always fixed as “POST-DEPLOY”.

3. Build-Dac: Given a set of Transact-SQL files, this function lets the user build a DAC with the given DAC type name.

4. Deploy-Dac: This function deploys a give DAC type on the input connection. It checks if there already exists a DAC with the input instance name. If so, the function calls upgrade on the given DAC type or else, it installs the DAC type.

5. Extract-Dac: This function extracts a database to a DAC Type with the input name. Additionally it also saves the DAC type to a give file path as a DAC package.

6. Get-DacType: This function loads up the DAC type from the DAC package (dacpac) file.

7. Get-ServerConnection: This is a helper function to create the server connection on a given server instance. Please note that this uses the default Windows authentication. The users can directly create a server connection using a connection string or from a SqlConnection object.

8. Register-Dac: This function registers an existing database on the given connection as a DAC.

9. Save-DacType: This function saves a DAC type to a given path in dacpac format.

10. Uninstall-Dac: This function uninstalls a DacInstance on a given connection.

11. Unpack-Dac: This function unpacks an input DAC package (dacpac) file into a folder by extracting the related metadata xml files and any extra files as well.

Setup

 

The samples below can be executed on any computer where the following list of MSI files have been installed. If SQL Server Management Studio for SQL Server 2008 R2 is installed, then these MSI files are already available on the machine.

SharedManagementObjects.msi

DACFramework.msi

SQLSysClrTypes.msi

TSqlLanguageService.msi

Samples

1 Get Server Connection

Problem

Initiate a connection to an instance of SQL Server.

Input

a. $instance_name is the name of the server. This is used only for default windows authentication

‘Or’

b. $connectionString is standard SQL Connection string to connect to the target instance

Solution

PS > $serverConnection = Get-ServerConnection –serverName “instance_name”

Or

PS > $serverConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection $connectionString

2 Build a DAC Type from TSQL Scripts

Problem

Given a set of Transact-SQL scripts, build a DAC type.

Input

c. $buildFile1 and $buildFile2 are the full paths referring to the Transact-SQL files.

d. DAC Type name should be “DacCompile”

Solution

PS > $dacType = Build-Dac –dacTypeName "DacCompile" –files @($buildFile1, $buildFile2)

3 Save a DAC Type

Problem

Given a DAC type, save it to a file.

Input

a. $dacType is the DAC type input

b. $path is the path of the dacpac

Solution

PS > Save-DacType –dacType $dacType –dacpackPath $path

4 Extract a Database to a DAC Type

Problem

Given a database on a given connection, extract it to a DAC type.

Input

a. $srvConnection is the ServerConnection object

b. $path is the path of the dacpac file to be saved after extraction

c. The name of the database to be extracted is “pubs”. The DacType name should be “pubs” and the DacType version should be “1.0.0.0”

Solution

PS > $dacType = Extract-DAC –serverConnection $srvConnection –databaseName "pubs" –dacTypeName "pubs" –dacTypeVersion "1.0.0.0" –dacPackPath $path

 

5 Register a Database to a DAC

Problem

Given a database on a given connection, register it as a DAC instance.

Input

a. $srvConnection is the ServerConnection object

b. The name of the database to be registered is “pubs”. The DacType name should be “pubs” and the DacType version should be “1.0.0.0”

Solution

PS > $dacType = Register-DAC –serverConnection $srvConnection –databaseName "pubs" –dacTypeName "pubs" –dacTypeVersion "1.0.0.0"

6 Install a DAC

Problem

On a given connection, install a DacType.

Input

a. $srvConnection is the ServerConnection object (run $serverConnection.Connect if not connected)

b. $dacType is the DacType input to be installed

c. The name of the DAC to be installed is “pubs_Dac”

 

Solution

PS > $dacInstance = Deploy-Dac –serverConnection $srvConnection –dacType $dacType –dacInstanceName “pubs_Dac”

 

7 Upgrade a DAC

 

Problem

On a given connection, upgrade an existing DacInstance with a given DacType.

Input

a. $srvConnection is the ServerConnection object (run $serverConnection.Connect if not connected)

b. $dacType is the DacType input to upgrade an already installed DAC

c. The name of the DAC to be installed is “pubs_Dac”

Solution

PS > $dacInstance = Deploy-Dac –serverConnection $srvConnection –dacType $dacType –dacInstanceName "pubs_Dac"

 

8 Uninstall a DAC

 

Problem

On a given connection, uninstall an existing DacInstance with a given DacType.

Input

a. $srvConnection is the ServerConnection object (run $serverConnection.Connect if not connected)

b. The name of the DAC to be un-installed is “pubs_Dac”

c. $uninstallMode is the input for the uninstall mode -

#MakeUnmanaged does not affect the database - removes the DAC metadata from the instance without touching the underlying database

#DetachDatabase detaches the database - removes the DAC metadata from the instance and detaches the database associated with the DAC

#DropDatabase - removes the DAC metadata from the instance and drops the database associated with the DAC

PS > $uninstallMode = [Microsoft.SqlServer.Management.Dac.DacUninstallMode]::DropDatabase;

Solution

PS > Uninstall-Dac –serverConnection $srvConnection –dacInstanceName "pubs_Dac" –uninstallMode $uninstallMode

 

9 Unpack a DAC

 

Problem

Given a DAC Type, unpack it to view its content

Input

a. $dacType is the DacType that needs to be unpacked

b. $path is the full path of the folder in which the DAC type is unpacked

Solution

PS > $dacInstance = Unpack-Dac –dacType $dacType –unpackFolderPath $path

 

 

10 Add Post-Deploy file to a DAC Type

Problem

Given a DAC Type, add a post deployment file to it

Input

a. $dacType is the DacType

b. $path is the full path of the post deploy file that needs to be added to the DAC type

Solution

PS > $dacType = AddPostDeployFile-Dac –dacType $dacType –filePath $path

11 Install Data Post DAC deployment

 

Problem

On a given connection, install a DacType and run the post deployment script, which contains (for example) data insertion commands.

Input

a. $srvConnection is the ServerConnection object

b. $dacType is the DacType input to be installed

c. The name of the DAC to be installed is “pubs_Dac”

d. $postDeployFile is the path of the post-deployment script containing the data insertion commands. For instance this file could contain Transact-SQL commands such as “INSERT INTO t1 VALUES (1, 2)”, assuming that the schema in $dacType contains a table called t1 with two columns taking integer values.

 

Solution

PS > $dacType = AddPostDeployFile-Dac $dacType $postDeployFile

PS > $dacInstance = Deploy-Dac $srvConnection $dacType “pubs_Dac”

 

12 Install Unsupported Objects Using Post Deployment Script

Problem

On a given connection, install a DacType and run the post deployment script containing objects that are not natively supported in DAC (such as synonyms, for example).

Solution

1. Create a file with Transact-SQL definitions for the unsupported objects in the DAC. For instance, one could create synonyms in the post deployment file using the statement: “CREATE SYNONYM syn_t1 FOR t1”, assuming that the original DAC type contains a table called t1.

 

2. Once you have the post-deployment file, refer to the above sample here.

               

13Unregister All DACs on a Given Instance

 

Problem

On a given connection, unregister all the DACs on the instance of SQL Server.

Input

a. $srvConnection is the ServerConnection object

Solution

PS > $dacStore = New-Object Microsoft.SqlServer.Management.Dac.DacStore($srvConnection)

PS > $srvConnection.Connect()

PS > $uninstallMode = [Microsoft.SqlServer.Management.Dac.DacUninstallMode]::MakeUnmanaged

PS > $dacStore.DacInstances | %{Uninstall-Dac $srvConnection $_.Name $uninstallMode}

This example shows how one could pipe a collection input and efficiently process any DAC action on each of the inputs.

 

-Shireesh Thota

DACModule.zip

Comments

  • Anonymous
    April 09, 2012
    The comment has been removed

  • Anonymous
    March 12, 2013
    The comment has been removed

  • Anonymous
    March 13, 2013
    The comment has been removed

  • Anonymous
    January 28, 2014
    Do any of these examples need to change for DAC Framework 3.0?

  • Anonymous
    November 19, 2014
    Hello, I'm added your module and got the permissions setup right but it doesn't seem to be working. I followed your example for extraction a DAC file but the file isn't generated and there are no errors given.