DAC Powershell Samples

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

1Get 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

11Install Data Post DAC deployment

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.

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.