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


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 (5)

  1. Tye Akoiye says:

    Are all the outlined steps the actual order you need to follow in order to deploy DAC via PS? When I attempt to "Build-Dac" and pipe it to Save-DAC", I receive the following error:

    $serverConnection = Get-ServerConnection –serverName “MySQLServerInstance”

    $path = "C:TempMy PowerShellDAC"

    $buildFile1 = "C:TempMy PowerShellDAC"

    $buildFile2 = "C:TempMy PowerShellDAC"

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

    Save-DacType –dacType $dacType –dacpackPath $path

    #$dacType = Extract-DAC –serverConnection $serverConnection –databaseName "dba_admin" –dacTypeName "dba_admin" –dacTypeVersion "1.0.0.0" –dacPackPath $path

    Save-DacType : Cannot process argument transformation on parameter 'dacType'. Cannot convert the "System.Object[]" value of type "System.Object[]" to type "Microsoft.SqlServer.Management.Dac.DacType".

    At line:9 char:22

    + Save-DacType –dacType <<<<  $dacType –dacpackPath $path

       + CategoryInfo          : InvalidData: (:) [Save-DacType], ParameterBindin…mationException

       + FullyQualifiedErrorId : ParameterArgumentTransformationError,Save-DacType

  2. Carl Prothman says:

    Import-Module : File DACModule.psm1 cannot be loaded.

    The file DACModule.psm1 is not digitally signed. The script will not execute on the system.

    For more information, see about_Execution_Policies at

    go.microsoft.com/fwlink.

  3. Carl Prothman says:

    To get past the "is not digitally signed. The script will not execute on the system", see:

    dmitrysotnikov.wordpress.com/…/unblocking-powergui-add-ons-and-powershell-modules

  4. DeborahK says:

    Do any of these examples need to change for DAC Framework 3.0?

  5. Does this still work? says:

    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.