PowerShell to run SQL Scripts

I’ve used these two scripts for a while now. It’s definitely a timesaver when resetting your DEV Environment over and over … and over.

The first one is useful when you’ve got direct access to the database (with a version of PowerShell that’ll run Invoke-Sqlcmd). The second one combines all the files into one, making it easier to copy/paste/email/dropbox around the place.

Run All SQL Scripts

$path = "$PSScriptRoot"
if ($PSScriptRoot -eq "") {
	# # LOCAL ---------------------
	$path = "C:\repos\Project_Sln\Deployment\1.0"
	# ---------------------------- #>
	<# # TEST ---------------------
	$path = "C:\Installs\Project\SQL"
	# ---------------------------- #>
}

# # LOCAL ---------------------
$server = "(local)"
$db = "LOCAL_DB"
# ---------------------------- #>
<# # TEST  ----------------------
#NOTE: USE ELEVATED RIGHTS ACCOUNT
$server = "TEST_SERVER"
$db = "TEST_DB"
# ---------------------------- #>

<# # PROD  ---------------------- 
#NOTE: USE ELEVATED RIGHTS ACCOUNT
$server = "PROD_SERVER"
$db = "PROD_DB"
# ---------------------------- #>

$files = Get-ChildItem -Path $path -Filter "*.sql" | select FullName, Name | Sort-Object Name

try {
    foreach($f in $files) {
        Write-Output "Processing file - $($f.Name)" 
        Invoke-Sqlcmd -ServerInstance $server -Database $db -ErrorAction 'Stop' -Verbose  -InputFile $f.FullName | Format-List
    }
}
catch{
   Write-Error $_.Exception
}

Combine All SQL Scripts

$SqlPath = "C:\repos\Project_Sln\Deployment\1.0\";

$SqlOutput = "C:\Publish\Project\v1.0\Combined.sql";

if (Test-Path $SqlOutput) {
    Remove-Item $SqlOutput;
}


$files = Get-ChildItem -Path $SqlPath -Filter "*.sql" | select FullName, Name | Sort-Object Name;

$separatorLine = "=" * 150;


foreach($f in $files) {
    Write-Output "Processing file - $($f.Name)";
    Add-Content -Path $SqlOutput -Value "-- $separatorLine";
    Add-Content -Path $SqlOutput -Value "-- $($f.Name)";
    Add-Content -Path $SqlOutput -Value "-- $separatorLine";

    Get-Content $f.FullName | Add-Content $SqlOutput;

    Add-Content -Path $SqlOutput -Value "-- $($f.Name) -- END";
    Add-Content -Path $SqlOutput -Value "GO";
    Add-Content -Path $SqlOutput -Value "-- ";
}

There you have it. I don’t think I need to explain the PS. Just be aware of the inputs and outputs and database names and all will be good in the world.