Powershell script to update config for change logging

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:

try
{

    # 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");
    $redGateConfigXml.Load($redGateConfigPath);

    # 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";

    $redGateConfigXml.Save($redGateConfigPath);

    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
@CLS
@Powershell -ExecutionPolicy RemoteSigned -File "\<file-server><share-name>RedGateSQLSourceControl3UpdateRedgateEngineConfig.ps1"
@PAUSE Press [ENTER] to close