Powershell Scripts to Replace "setvar" Variable in SQL-CMD Script File Before Running the SQL Scripts

Consider having the following SQL file, to run it an SQL-CMD mode:

C:\PowershellTest\CreateDatabase.sql

:setvar ImagesLocation 'C:\ImagesStore\'
........
Update ImagesStore
SET ImagesLocation = '$(ImagesLocation)'
.......

Now, when you run this SQL-cmd scripts file during the deployments, you would also want to change the value of ImagesLocation variable, as the location may vary for different environments.

This can be achieved through using regular expressions in Powershell scripts. In order to do that, you can create the following function in your Powershell deployment or pre-deployment scripts:

#function to replace cmdlet variable in SQL-CMD scripts (i.e., the ones assigned by :setvar). For strange reasons, command line variable assignments has lower precendence than the Sqlcmd scripts setvar.

function replaceCmdletParameterValueInFile( $file, $key, $value ) {
    $content = Get-Content $file
    if ( $content -match ":setvar\s*$key\s*[\',\""][\w\d\.\:\\\-]*[\'\""_]" ) {
        $content -replace ":setvar\s*$key\s*[\',\""]_
        [\w\d\.\:\\\-]*[\'\""_]", ":setvar $key $value" | Set-Content $file     
	    } else {
		      Add-Content $file "$key = $value"
		}
}

Call this function in the following manner:

$scriptfile = "C:\PowershellTest\UpdateImagesLocation.sql"

replacePatternMatchingValueInFile $scriptfile"SET @ImagesLocation" 
"'\\datashare\appImages'"
	replaceCmdParameterValueInFile "C:\PowershellTest\CreateDatabase.sql" 
	"ImagesLocation" "'\\datashare\ImagesStore'"

As a result, the variable assignment for ImagesLocation would be changed to a different value in the SQL file, and then that file can be used in Invoke-Sqlcmd to run it.