By Andy Cox
Hi everyone! Long time no talk.
So today I was trying to accomplish the following:
On an instance of SQL Server, there could exist a database with a naming convention similar to “%naplog%”. If a database with that naming convention exists, check to see if some tables, indexes and stored procedures exist; if they don’t, create them.
Simple enough, right?
Since there could be any number of SQL instances that might meet this qualification, I decided to automate the process. I debated for a few minutes on which method to use, and fought with SQLCMD for quite a while. After I decided that SQLCMD wouldn’t give me what I needed, I decided to try PowerShell.
Now, I have to admit: I’ve avoided PowerShell for a while now. Yes, guilty as charged. My thought has been that PowerShell seems cool, but it’s a better fit for the Active Directory and Windows Admins, not a SQL guy.
I now stand before you, a changed man.
After some Bing research ( as well as some helpful suggestions from my co-workers ), I strung together the following:
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | Out-Null
$server = “andycox1\denalictp3″
$sqlserver = new-object “Microsoft.SqlServer.Management.Smo.Server” $server
foreach ($db in $sqlserver.Databases)
if ( $db.name -like “*naplog*” )
invoke-sqlcmd -inputfile create_db_objects.sql -database $db.name -ServerInstance $sqlserver.name -IgnoreProviderContext
To get things started, I loaded the SMO assembly ( “Microsoft.SQLServer.SMO” ); this allows me to create an instance of a SQL instance object ( $sqlserver ). I used a local variable name “$server” to specify the SQL instance I wanted to connect to.
Then, I looped through all of the databases on this instance ( “foreach( $db in $sqlserver.databases )” ) and looked for any databases that had a naming convention similar to a specific string ( “if ( $db.name -like “*naplog*” )” ). Once I find an instance of a database that meets my requirements, I call the invoke-sqlcmd cmdlet, passing parameters of the T-SQL script I want to execute along with the SQL instance and database I want to connect to.
I have to admit – I had some fun with this, and plan on exploring the mystery of PowerShell a bit more.
Check out these links for more references on PowerShell, and happy scripting!
Microsoft PowerShell – http://technet.microsoft.com/en-us/scriptcenter/dd742419.aspx
Microsoft PowerShell Team - http://blogs.msdn.com/b/powershell/
Gary Siepser’s PowerShell and Exchange Blog - http://blogs.technet.com/b/gary/
Link to the original article: http://blogs.technet.com/b/andyc_sql/archive/2011/11/30/dipping-my-little-toe-into-the-powershell-waters.aspx