Powershell script to update config for change logging

Dan GuzmanDan Guzman Posts: 20
Below is a Powershell script I am sharing with the Redgate community.

This script specifies a change logging database name in the local configuration file so that last Changed By information is not lost after a SQL restart in the shared development model as described in http://documentation.red-gate.com/displ ... +databases. We are rolling out SQL Source Control to dozens of developers and this script avoids the manual and error-prone task of each developer updating the configuration manually. I've also include a sample command-line file to run the script, which our developers run immediately after installing SQL Source Control.

Powershell script (UpdateRedgateEngineConfig.ps1) contents:

    # load existing RedGate config file
    $redGateConfigXml = New-Object System.Xml.XmlDocument;
    $redGateConfigPath = [System.IO.Path]::Combine($env:localappdata, "Red GateSQL Source Control 3RedGate_SQLSourceControl_Engine_EngineOptions.xml");

    # get EngineOptions element
    $engineOptionsElement = $redGateConfigXml.DocumentElement.SelectSingleNode("/EngineOptions");
    if($engineOptionsElement -eq $null)
        throw "Root element ""EngineOptions"" not found in config file.";

    # get TraceCacheDatabase element
    $traceCacheDatabaseElement = $engineOptionsElement.SelectSingleNode("TraceCacheDatabase");
    if($traceCacheDatabaseElement -eq $null)
        # create TraceCacheDatabase if it doesn't already exist
        $traceCacheDatabaseElement = $redGateConfigXml.CreateElement("TraceCacheDatabase");
        $traceCacheDatabaseElement = $engineOptionsElement.AppendChild($traceCacheDatabaseElement);

    # specify name of RedGate audit database
    $traceCacheDatabaseElement.InnerText = "RedGateAudit";


    Write-Host "SUCCESS: RedGate configuration updated.";

catch [Exception]

    Write-Error $("ERROR: " + $_.Exception.Message);


Sample command file (UpdateRedgateEngineConfig.cmd) contents below. Update UNC path as needed for your environment:
@REM Update local RedGate source control config file to specify the database name used for auditing
@Powershell -ExecutionPolicy RemoteSigned -File "\<file-server><share-name>RedGateSQLSourceControl3UpdateRedgateEngineConfig.ps1"
@PAUSE Press [ENTER] to close


Sign In or Register to comment.