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'"
