PowerShell Week at SQL University – Post 7

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.

Please Share This:

You may also like:

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.


Subcribe to Blog Via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

What I'm Saying on Twitter

Subscribe via feedburner