How do I execute SQL script files from PowerShell during deployment?

This was a bit tricky figuring out. I was dead sure there was something I could use built in for MS SQL at least but to complicate things further I have to open an SSH connection before I can connect to the database server.

Since the SSH one was easier and a prerequisite I started with that one. What I wanted was to open putty with some arguments like a password but putty just closes itself after opening the connection so I had a read up on putty and it turns out it does not support command line access very good. Plink on the other hand fully supports what I needed. Since I always type this password I had some problems with authenticating. I totally got the password wrong all the time when looking at it and had to revert back to just writing it without looking or thinking. The Start-Process command does exactly what I needed.

For the MS SQL Server I was right there is a module for that but finding it was a pain in the neck. I kept reading about this Invoke-Sqlcmd but couldn’t find how to get it into my script but finally I found it over at MSDN (who would have guessed?). A potential problem with this approach is that the SSH connection speed isn’t instant so we need to pause for a few seconds before trying to connect with the database.

Since I am limited in the number of simultaneous connections it’s also really important to close that connection when I ‘m done so I have to loop through all the processes and “kill ’em all”. There is actually an alias for Stop-Process which is named Kill but to be really sure everyone understands what I am doing I chose to not use any short aliases for my scripts. It’s imperative that I start expressing myself clearer and more thorough.

Anyway, I hope someone finds this post useful.

add-pssnapin sqlserverprovidersnapin100
add-pssnapin sqlservercmdletsnapin100

$putty = "C:\apps\putty\plink.exe" 
Start-Process $putty -ArgumentList "-load saved_session -pw Password"

Start-Sleep -s 7
$base_dir = "D:\Projects\Project"

Invoke-Sqlcmd `
            -ServerInstance "127.0.0.1,1433" `
            -Database "Database" `
            -Username "Username" `
            -Password "Password" `
            -InputFile "$base_dir\src\Database\1.schema.sql"

$ids =Get-Process plink | Select-Object id

foreach ($id in $ids) {
    Stop-Process -Id $id.Id -Force `
                    -WarningAction:SilentlyContinue ` 
                    -Confirm:$false
}