Execute MS SQL scripts from Jenkins with Powershell

Prerequisite: Install SQL Native Client and SQL Command Line Tools on Jenkins server

This script will look inside a directory and sort the .sql scripts by name. It will execute each script and right the output to a temp file. Then it displays the output on the Jenkins console.

Options for the sqlcmd.exe are here: MSDN sqlcmd Utility

ECHO "Executing SQL Scripts"
$sqldir = "$tagDir\"
$sqlfiles = Get-ChildItem $sqldir *.sql -rec | Sort-Object Name 
foreach ($fileName in $sqlfiles) {
$file = $sqldir + $fileName
& "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd.exe" -S $sqlserver -U $deployuser -P $deploypassword -d $database -i $file -e -b -o C:\temp\output.txt
Get-Content "C:\temp\output.txt"
}

This code will make a backup of a database

& "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd.exe" -S $sqlserver -U $deployuser -P $deploypassword -Q "BACKUP DATABASE $database TO DISK='$backupPostLocation'"