Deploying Code
Deploying SQL code with PowerShell can be very easy but why would you want to do that instead of just open a script in SQL Management Studio and run it? The number 1 reason that I can think of is when you need to deploy to multiple destinations. Let’s get started with something simple, we’ll deploy a table that I use in the post on collecting database sizes.
Invoke-Sqlcmd -ServerInstance Win7Netbook -Database CentralInfo -InputFile C:\temp\dbo.DatabaseFileSizes.SQL
That’s a pretty simple example and one that doesn’t offer too much any benefit over just opening up SSMS and running the script. Let’s take it a little further with the loop construct and this time we’ll deploy Adam Machanic’s ( blog | twitter ) sp_WhoIsActive to a bunch of machines
$DeployFile = C:\temp\who_is_active_v11_00.sql <# Loops through Registered SQL Severs and applys WhoIsActive #> foreach ($RegisteredSQLs in dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\Development\ | where {$_.Mode -ne "d"} ) { "Deploying to "+ $RegisteredSQLs.ServerName; Invoke-Sqlcmd -InputFile $DeployFile -ServerInstance $RegisteredSQLs.ServerName -database master}
So now we can see how we can deploy a SQL Script to multiple machines with just 4 lines of PowerShell. The script obviously doesn’t have to be deploying code. It could inserting/updating data or even verifying permissions.