Run any sql script (from a web server)

jtucker@inflexxion.com

Prerequisite: Install of SQLPS.

$ElapsedStopWatch = [System.Diagnostics.Stopwatch]::StartNew()
# $RedGatePackageDirectoryPath is the On Deck Circle where the package is initial extracted.
# http://powershell.com/cs/forums/t/13752.aspx
if ([string]::IsNullOrEmpty($RedGatePackageDirectoryPath))
{
	$RedGatePackageDirectoryPath = "D:\On Deck Circle\MigrationScript"	
}

"The package binaries are in " + $RedGatePackageDirectoryPath | Write-Host
"Running migration script for environment " + $RedGateEnvironmentName | Write-Host


if ($IsRollback.ToLower() -eq "true")
{
	"Rolling the Data synch for " + $RedGateEnvironmentName | Write-Host
	
	if ([string]::IsNullOrEmpty($ApplicationRollbackPath))
	{
		$ApplicationRollbackPath = "D:\ApplicationRollback\MigrationScript"
	}
	
	"The backup directory is " + $ApplicationRollbackPath | Write-Host
	set-location $ApplicationRollbackPath
	get-location
	$out = $RedGatePackageDirectoryPath	+ "\" + "UnDoMigration.log"
	$inputFile = $RedGatePackageDirectoryPath + "\" + $UnDoMigrationScript
	invoke-sqlcmd -ServerInstance $TargetSqlServer -Database $TargetDatabaseName -InputFile $inputFile | format-table | out-file -filePath $out
}
else
{
	"Deploying Data Synch to "  + $RedGateEnvironmentName | Write-Host
	# these steps are crucial
	set-location $RedGatePackageDirectoryPath
	get-location
	
	$out = $RedGatePackageDirectoryPath	+ "\" + "DoMigration.log"
	$inputFile = $RedGatePackageDirectoryPath + "\" + $DoMigrationScript
	invoke-sqlcmd -ServerInstance $TargetSqlServer -Database $TargetDatabaseName -InputFile $inputFile | format-table | out-file -filePath $out
	Copy-Item $RedGatePackageDirectoryPath $ApplicationRollbackPath -force -recurse
}
# Stop the timer
$elapsedTime = $ElapsedStopWatch.Elapsed.ToString()
$elapsedTime | Write-Host