{"id":26193,"date":"2016-06-01T00:00:00","date_gmt":"2016-06-01T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/the-posh-dba-accessing-sql-server-from-powershell\/"},"modified":"2021-08-24T13:39:31","modified_gmt":"2021-08-24T13:39:31","slug":"the-posh-dba-accessing-sql-server-from-powershell","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/the-posh-dba-accessing-sql-server-from-powershell\/","title":{"rendered":"The PoSh DBA: Accessing SQL Server from PowerShell"},"content":{"rendered":"<div class=\"article-content\">\n<h2>What extra do I need to access SQL Server from PowerShell?<\/h2>\n<p class=\"start\">You don&#8217;t need anything additional in the way of .NET libraries or PowerShell modules to work with SQL Server, but for many scripting tasks you can get a lot of extra value from using them.<\/p>\n<p>The approach that you choose towards accessing SQL Server depends on the sort of task you need to produce a script for.<\/p>\n<ul>\n<li>If you need to just perform queries against SQL Server databases and save the results to a file, then we can just run <strong>SQLCMD<\/strong> or <strong>BCP<\/strong> from PowerShell.<\/li>\n<li>You can choose to load the .NET data provider. This gives you a lot more versatility, but you are working at a lower level, having to create in code what is already built into SQLCMD<\/li>\n<li>You can load SMO or even SQLPS, the SQL Server provider that uses SMO behind the scenes. SMO is used primarily to provide all the functionality for SQL Server Management Studio. Anything you can do in SSMS you can also automate in SMO.<\/li>\n<li>With SQLPS, you can read the output from the cmdlet <strong>Invoke-sqlcmd<\/strong> into PowerShell&#8217;s native data objects, and you can process the results and save data to file in a number of formats such as XML<\/li>\n<\/ul>\n<p>The same is true of outputting data from PowerShell. The built-in features of PowerShell are enough to get you a long way, but what you use depends on your task. If you need more &#8230;<\/p>\n<ul>\n<li>A lot can be done with command-line tools such as BCP<\/li>\n<li>You can use modules or functions to provide extra, such as writing out in Excel spreadsheet format<\/li>\n<\/ul>\n<p>This article is all about how you get started with all these approaches, and about how you can load and use the modules and assemblies to do this safely and effectively.<\/p>\n<h3>The Various methods of working with SQL Server<\/h3>\n<h4>Running an External command-line Program<\/h4>\n<p>You can run external command-line programs such as <strong>OSQL<\/strong> <strong>, BCP<\/strong> or <strong>SQLCMD<\/strong>. You can call those external programs inside PowerShell and then use them to query the SQL Server instance:<\/p>\n<p>This example will query the <strong>test<\/strong> table and redirect the output to a txt file<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">$ServerInstance = 'DeathStar'\r\n$Database = 'Dagobah'\r\n$Query = 'Select * from test'\r\n$SqlcmdOptions = @\"\r\n-S\"$ServerInstance\" -d \"$Database\" -Q \"$Query\"\r\n\"@\r\n\r\nStart-Process -FilePath \"sqlcmd.exe\" -ArgumentList @\"\r\n$SqlcmdOptions\r\n\"@ -Wait -NoNewWindow -RedirectStandardOutput C:\\temp\\SQLCMD.txt -PassThru \r\n<\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2425-2608a335-516e-4693-afc6-e4f5fe418398.png\" alt=\"2425-2608a335-516e-4693-afc6-e4f5fe41839\" \/><\/p>\n<p>Another approach is to use BCP utility which allows us very fast import or export of a table, view or query. In the following example we are exporting the <strong>product<\/strong> table from <strong>AdventureWorks2014<\/strong>:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">$ServerInstance = 'DeathStar'\r\n$Database = 'Dagobah'\r\n$TableExport = 'AdventureWorks2014.Production.Product'\r\n$FileExport  = 'C:\\temp\\Product.dat'\r\n$BCPOptions = @\"\r\n\"$TableExport\" out \"$FileExport\" -S \"$ServerInstance\" -T -n\r\n\"@\r\n\r\nStart-Process -FilePath \"bcp.exe\" -ArgumentList @\"\r\n$BCPOptions\r\n\"@ -Wait -NoNewWindow -PassThru -RedirectStandardOutput C:\\temp\\BCPError.txt \r\n<\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2425-6fe665c3-4ae0-4fb6-aa9e-688b28058f3b.png\" alt=\"2425-6fe665c3-4ae0-4fb6-aa9e-688b28058f3\" \/><\/p>\n<p>Something to be aware of is that, if we get an error using this coding style, it will not be visible. The output will be the same. The difference will be that the <strong>BCPError.txt<\/strong> will have the error. Let&#8217;s export a table that does not exists called <strong>IdontExist<\/strong> so as to deliberately cause an error:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">$ServerInstance = 'DeathStar'\r\n$Database = 'Dagobah'\r\n$TableExport = 'AdventureWorks2014.IDontExist'\r\n$FileExport  = 'C:\\temp\\Product.dat'\r\n$BCPOptions = @\"\r\n\"$TableExport\" out \"$FileExport\" -S \"$ServerInstance\" -T -n\r\n\"@\r\n\r\nStart-Process -FilePath \"bcp.exe\" -ArgumentList @\"\r\n$BCPOptions\r\n\"@ -Wait -NoNewWindow -PassThru -RedirectStandardOutput C:\\temp\\BCPError.txt \r\n<\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2425-a395d8e4-72cd-4dfe-88e6-22da98b23ab4.png\" alt=\"2425-a395d8e4-72cd-4dfe-88e6-22da98b23ab\" \/><\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2425-1-f6be4e8b-5eb4-43f2-87d6-2c96f57be8d1.png\" alt=\"2425-1-f6be4e8b-5eb4-43f2-87d6-2c96f57be\" \/><\/p>\n<p><strong><em>&#8216;So Laerte, how can I test in my code if it was successful or not?&#8217;<\/em><\/strong><\/p>\n<p>One approach is to use a property that is returned by the start-process called <strong>ExitCode<\/strong>. If it is 0, it was running with no errors and 1, of course, an error occurred.<\/p>\n<h5><strong>Saving the output in a variable:<\/strong><\/h5>\n<pre class=\"theme:powershell-ise lang:ps\">$Output = Start-Process -FilePath \"bcp.exe\" -ArgumentList @\"\r\n$BCPOptions\r\n\"@ -Wait -NoNewWindow -PassThru -RedirectStandardOutput C:\\temp\\BCPError.txt\r\n\r\n$Output.ExitCode \r\n<\/pre>\n<pre class=\"theme:powershell-output lang:ps\">PS C:\\WINDOWS\\system32&gt; . 'C:\\temp\\Untitled15.ps1' &lt;# script is not saved yet #&gt;\r\n\r\nHandles  NPM(K)    PM(K)      WS(K) VM(M)   CPU(s)     Id  SI ProcessName                                                                                                \r\n-------  ------    -----      ----- -----   ------     --  -- -----------                                                                                                \r\n     23       3      384       1988    11     0.00  11148   3 bcp                                                                                                        \r\n\r\n\r\n\r\nPS C:\\WINDOWS\\system32&gt; . 'C:\\temp\\Untitled15.ps1' &lt;# script is not saved yet #&gt;\r\n1 \r\n<\/pre>\n<h5>Saving only the <strong>ExitCode<\/strong> property:<\/h5>\n<pre class=\"theme:powershell-ise lang:ps\">$Output = (Start-Process -FilePath \"bcp.exe\" -ArgumentList @\"\r\n$BCPOptions\r\n\"@ -Wait -NoNewWindow -PassThru -RedirectStandardOutput C:\\temp\\BCPError.txt).ExitCode\r\n\r\n$Output \r\n<\/pre>\n<h5>Or even, testing the property directly:<\/h5>\n<pre class=\"theme:powershell-ise lang:ps\">if ((Start-Process -FilePath \"bcp.exe\" -ArgumentList @\"\r\n$BCPOptions\r\n\"@ -Wait -NoNewWindow -PassThru -RedirectStandardOutput C:\\temp\\BCPError.txt).ExitCode) { \r\n  Write-Error \"Some Error ocurred. Check the BCPError.txt file\"\r\n}\r\n<\/pre>\n<pre class=\"theme:powershell-output lang:ps\">  : Some Error ocurred. Check the BCPError.txt file\r\n    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException\r\n    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException \r\n<\/pre>\n<p>You can read more about BCP here: <a href=\"https:\/\/www.simple-talk.com\/sql\/database-administration\/working-with-the-bcp-command-line-utility\/\">Working with the bcp Command-line Utility<\/a><\/p>\n<h4>Querying SQL Server via the .NET data provider<\/h4>\n<p>If you don&#8217;t need SMO for any other reason, you can query SQL metadata or data, or to work with any kind of SQL Server Object, by using the <strong>System.Data.SqlClient<\/strong> Namespace &#8211; the .NET Framework Data Provider for SQL Server. It will allow you to use T-SQL commands and then manage what you need in much the same way as you would with the SMO database connection methods. It does not require you to load anything extra, because the namespace is already loaded by PowerShell.<\/p>\n<p>In your test database, execute this&#8230;<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Create table test (id int)\r\ninsert into test values (1),(2),(3)\r\n<\/pre>\n<p>And then, in PowerShell, amend the server instance name and database, and run the script<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">$Server = 'DeathStar'    \r\n$database = 'Dagobah'\r\n$Connection = New-Object System.Data.SQLClient.SQLConnection\r\n$Connection.ConnectionString = \"server=$($Server);database=$($Database);trusted_connection=true;\"\r\n$Connection.Open()\r\n$Command = New-Object System.Data.SQLClient.SQLCommand\r\n$Command.Connection = $Connection\r\n$Command.CommandText = 'Select * from test'\r\n$Reader = $Command.ExecuteReader()\r\n$Datatable = New-Object System.Data.DataTable\r\n$Datatable.Load($Reader)\r\n$Datatable\r\n$Connection.Close() \r\n<\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2425-80464892-8efa-4b70-ab7c-c0fe550895c0.png\" alt=\"2425-80464892-8efa-4b70-ab7c-c0fe550895c\" \/><\/p>\n<p>If your result set returns more than one table, you can use the <strong> DataAdapter<\/strong> and <strong>Dataset<\/strong> class:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Create table test (id int)\r\ninsert into test values (1),(2),(3)\r\n\r\nCreate table test1 (id int)\r\ninsert into test1 values (4),(5),(6),(7)\r\n\r\n     \r\n$Connection = New-Object System.Data.SQLClient.SQLConnection\r\n$Connection.ConnectionString = \"server=$($Server);database=$($Database);trusted_connection=true;\"\r\n$Connection.Open()\r\n$Command = New-Object System.Data.SQLClient.SQLCommand\r\n$Command.Connection = $Connection\r\n$Command.CommandText = 'Select * from test;Select * from test1'\r\n$DataAdapter = new-object System.Data.SqlClient.SqlDataAdapter $Command\r\n$Dataset = new-object System.Data.Dataset\r\n$DataAdapter.Fill($Dataset)\r\n$DataSet.Tables[0]\r\n$DataSet.Tables[1]\r\n$Connection.Close() \r\n<\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2425-bdf1c0fd-1e68-4608-a2c9-9ad3eb4da7f5.png\" alt=\"2425-bdf1c0fd-1e68-4608-a2c9-9ad3eb4da7f\" \/><\/p>\n<p>In addition, you may want to execute a stored procedure:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Create procedure usp_foo \r\nas\r\nSelect * from test1\r\n\r\n\r\n$Server = 'DeathStar'    \r\n$database = 'Dagobah'\r\n$Connection = New-Object System.Data.SQLClient.SQLConnection\r\n$Connection.ConnectionString = \"server=$($Server);database=$($Database);trusted_connection=true;\"\r\n$Connection.Open()\r\n$Command = New-Object System.Data.SQLClient.SQLCommand\r\n$Command.Connection = $Connection\r\n$Command.CommandText = 'exec usp_foo'\r\n$Reader = $Command.ExecuteReader()\r\n$Datatable = New-Object System.Data.DataTable\r\n$Datatable.Load($Reader)\r\n$Datatable.Rows\r\n$Connection.Close() \r\n<\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2425-8d604ef2-901a-4582-a6de-1f4905ff97b6.png\" alt=\"2425-8d604ef2-901a-4582-a6de-1f4905ff97b\" \/><\/p>\n<h4>Using SMO<\/h4>\n<p>SMO is a .NET library that enables you to work with SQL Server as objects, classes, methods and so on. When you access a database on a server, the database object provides an open <strong>System.Data.SqlClient<\/strong> connection that is exposed by the <strong>Database.ExecuteNonQuery<\/strong> and <strong>Database.ExecuteWithResults<\/strong> methods that you can use for querying either the server or a database. You can use it in the same way that you use any other <strong>SqlClient <\/strong>connection. Check out my <a href=\"http:\/\/www.sqlservercentral.com\/articles\/SMO\/140966\/\">Stairway to Server Management Objects (SMO) Level 1: Concepts and Basics<\/a> on SQLServerCentral.com.<\/p>\n<p>Unlike the previous method, you need to load the SMO assembly before you can use SMO. The traditional way of doing this is to use the .NET method <strong>LoadWithPartialName<\/strong>.<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')  | out-null\r\n<\/pre>\n<p>This isn&#8217;t ideal, and we&#8217;ll be discussing refinements to this method later on in the article.<\/p>\n<h4>Using SQLPS<\/h4>\n<p>If you use SQLPS, you will not only be able to use SMO&#8217;s own .NET connection for querying databases but you will also have the SQL CmdLet&#8217;s. Everytrhing you need is loaded for you under the covers.The Cmdlet to use for executing SQL is <strong>Invoke-SQLCMD<\/strong>, but you can get a complete list of CmdLet&#8217;s that are available for use once you have loaded SQLPS by using the PowerShell command &#8230;<\/p>\n<pre class=\"listing\">Get-Command\u00a0-Module\u00a0SQLPS<\/pre>\n<h5><strong>Invoke-SQLCMD<\/strong><\/h5>\n<p>Invoke-SQLCMD is a compiled cmdlet from SQLPS.<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">import-module sqlps -DisableNameChecking\r\nInvoke-Sqlcmd -query 'Select * from test' -ServerInstance deathstar -Database dagobah  \r\n<\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2425-c42bb5bb-9ad9-41c1-9bf8-9c7dd163827a.png\" alt=\"2425-c42bb5bb-9ad9-41c1-9bf8-9c7dd163827\" \/><\/p>\n<p>You can also run several .SQL files.<\/p>\n<p>For that let&#8217;s use the amazing <a href=\"https:\/\/sqlserverperformance.wordpress.com\/2014\/09\/17\/sql-server-diagnostic-information-queries-for-september-2014\/\">SQL Server Diagnostic Information Queries for September 2014 from Glenn Berry<\/a>, copy some SQL from there and split into .sql files. I\u00b4ve created three .sql files: <strong>FileNamesPaths.SQL<\/strong>, <strong>ServerProperties.SQL<\/strong> and <strong>SQLServerAgentjobsCategory.SQL<\/strong><\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2425-92b930e2-e5e4-41e2-8969-429547c70e9e.png\" alt=\"2425-92b930e2-e5e4-41e2-8969-429547c70e9\" \/><\/p>\n<p>One approach is using the :r parameter inside a .sql file. First create a .sql file with the .SQL files to run: Let&#8217;s call this file SQLFiles.SQL<\/p>\n<pre class=\"listing\">PRINT 'executing the first file' \r\ngo\r\n:r C:\\temp\\FileNamesPaths.SQL\r\n:On Error exit\r\nPRINT 'Executing the second file' \r\ngo\r\n:r C:\\temp\\ServerProperties.SQL\r\n:On Error exit\r\nPRINT 'Executing the last file' \r\ngo\r\n:r C:\\temp\\SQLServerAgentjobsCategory.SQL\r\n<\/pre>\n<p>Then it\u00b4s just run the .sql file<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Invoke-Sqlcmd -InputFile C:\\temp\\SQLFiles.sql -ServerInstance deathstar -Database master -Verbose<\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2425-d1db0b5f-a8cf-4d3c-9361-5de7760bcc2d.png\" alt=\"2425-d1db0b5f-a8cf-4d3c-9361-5de7760bcc2\" \/><\/p>\n<p>It will run all the .SQL files you have specified in the SQLFiles.SQL<\/p>\n<p>You may want to export to a txt file the output of all files ran:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">import-module sqlps -DisableNameChecking\r\nInvoke-Sqlcmd -InputFile C:\\temp\\SQLFiles.sql -ServerInstance deathstar -Database master -Verbose |\r\nout-file C:\\temp\\output.txt \r\n<\/pre>\n<p>This approach can be very useful if you just need to check the output, not generating reports, or even create objects in SQL Server &#8211; an full environment for instance.<\/p>\n<p>Another approach is to call invoke-SQLCMD for each file. For that we don&#8217;t need the SQLFiles.SQL<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">import-module sqlps -DisableNameChecking\r\nget-childitem c:\\temp\\*.sql |\r\nForEach-Object { \r\n  Invoke-Sqlcmd -InputFile $_ -ServerInstance deathstar -Database master \r\n}\r\n<\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2425-30c81aa8-6e12-4ba9-83e3-e55d6833ee3e.png\" alt=\"2425-30c81aa8-6e12-4ba9-83e3-e55d6833ee3\" \/><\/p>\n<p>You can, of course, export the results to CSV files:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">import-module sqlps -DisableNameChecking\r\n#delete all csv files \r\nRemove-Item -Path c:\\temp\\*.csv -Force -ErrorAction SilentlyContinue\r\n\r\nget-childitem -path c:\\temp\\*.sql |\r\nForEach-Object { \r\n  Invoke-Sqlcmd -InputFile $_ -ServerInstance deathstar -Database master |\r\n  Export-Csv -Path \"c:\\temp\\$(($_.name -split '[.]')[0]).csv\" -NoTypeInformation -NoClobber \r\n}\r\n<\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2425-fcfa900b-10b4-434a-ae50-a8b1c978f3eb.png\" alt=\"2425-fcfa900b-10b4-434a-ae50-a8b1c978f3e\" \/><\/p>\n<p>Even better, you can write an XLSX file from one or more CSV files with each CSV file turned into a Worksheet. For that, we can use the function of my fellow PowerShell MVP Boe Prox called <strong>Convert-CSVToExcel<\/strong> (you can download the source from the top of this article)<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">import-module sqlps -DisableNameChecking\r\n#delete all csv files \r\nRemove-Item -Path c:\\temp\\*.csv -Force -ErrorAction SilentlyContinue\r\n\r\nget-childitem -path c:\\temp\\*.sql |\r\nForEach-Object { \r\n  Invoke-Sqlcmd -InputFile $_ -ServerInstance deathstar -Database master |\r\n  Export-Csv -Path \"c:\\temp\\$(($_.name -split '[.]')[0]).csv\" -NoTypeInformation -NoClobber \r\n}\r\n\r\nConvert-CSVToExcel -inputfile (Get-ChildItem c:\\temp\\*.csv)\t\t-output  'c:\\temp\\DiagnosticInformationQueries.xlsx' \r\n<\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2425-c8c3d143-1a34-439a-b410-dbdcbcaa9da7.png\" alt=\"2425-c8c3d143-1a34-439a-b410-dbdcbcaa9da\" \/><\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2425-ca0dc1db-7a56-474e-a8ba-a0c60a26b28e.png\" alt=\"2425-ca0dc1db-7a56-474e-a8ba-a0c60a26b28\" \/><\/p>\n<h4>Loading Community functions<\/h4>\n<p>You can also run Community functions such as <a href=\"https:\/\/github.com\/RamblingCookieMonster\/PowerShell\/blob\/master\/Invoke-Sqlcmd2.ps1\">Invoke-SQLCMD2<\/a>, a System.Data.SQLClient based function wrote by Chad Miller that extends the functionality of Invoke-SQLCMD. You just need to download it and run it inside your script, save into .ps1 file and call the function as a .ps1 script, load it in your profile or even save the code in a <strong>psm1<\/strong> file (script module file) and save in any path shown by the <strong>$env:psmodulepath<\/strong> to load directly when calling it (PowerShell 3.0 and higher)<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Invoke-Sqlcmd2 -query 'Select * from test' -ServerInstance deathstar -Database dagobah  -As PSObject -QueryTimeout 30 -ConnectionTimeout 30\r\n<\/pre>\n<h3>Loading and handling the extras<\/h3>\n<h4>Installing Module.<\/h4>\n<p>To Install a PowerShell module means nothing more than to copy the module files to a folder in a computer. To load a module in small projects is pretty simple. You just need to use the <strong>Import-Module<\/strong> cmdlet. If you don&#8217;t specify the path of the module PowerShell will look in the environment variable <strong>$ENV:PSMODULEPATH<\/strong> and check if it is there and automatically will load it. In PowerShell 2.0 you need to explicitly the import-module in your profile.<\/p>\n<p>Further reading:<\/p>\n<ul>\n<li><a href=\"https:\/\/www.simple-talk.com\/sysadmin\/powershell\/persistent-powershell-the-powershell-profile\/\">Persistent PowerShell: The PowerShell Profile<\/a><\/li>\n<li><a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/dd878350(v=vs.85).aspx\">Installing a PowerShell Module<\/a><\/li>\n<\/ul>\n<h4>Loading an assembly<\/h4>\n<p>Most of the code you will see that uses SMO does not leave the loading of assemblies to SQLPS, but instead uses the <strong>loadwithpartialname<\/strong> method. This method is deprecated because it doesn&#8217;t give you control of the version of SMO that will be loaded if you have several versions in your system. The choice of file is controlled by GAC*. On the other hand, using the ADD-Type can be a pain because you need to be explicit about the version and change your code. Let&#8217;s take a look at some examples:<\/p>\n<h4>LoadWithPartialName<\/h4>\n<p>The general way:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') <\/pre>\n<p>But we also can check if there is any SMO installed and return an error if not, otherwise it will be loaded:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">if ([Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') -eq $null)  {\r\n\tThrow 'SMO not avaliable'\r\n} \r\n<\/pre>\n<h4>Add-Type:<\/h4>\n<p>General way:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Add-Type -AssemblyName \"Microsoft.SqlServer.SMO, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91\" <\/pre>\n<p>Testing:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">try {\r\n  Add-Type -AssemblyName \"Microsoft.SqlServer.SMO, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91\" -ErrorAction Stop\r\n} catch {\r\n  Throw 'SMO not avaliable'\r\n} \r\n<\/pre>\n<p>The recommend way is to use ADD-Type, but what is the best approach? Well, it depends. If you are dealing with several version of SMO and it&#8217;s not a problem for your PowerShell environment then <strong>loadwithpartialname<\/strong> will be easier; otherwise, use <strong>add-type<\/strong>. But always remember <strong>LoadwithpartialName<\/strong> is a deprecated method and in some point will be excluded from the framework<\/p>\n<h4>Checking which version of SMO or SQLPS was loaded in the session<\/h4>\n<p>By default, PowerShell loads some assemblies when you open a session. Of course, it needs some basic assemblies to work properly. SMO is a .NET library and it needs to be loaded. The same happens with SQLPS module. To find out which assemblies were loaded, you can use this code (credits to fellow PowerShell MVP <a href=\"https:\/\/richardspowershellblog.wordpress.com\/2007\/09\/30\/assemblies-loaded-in-powershell\/\">Richard Siddaway &#8211; Assemblies loaded in PowerShell:<\/a>)<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">[appdomain]::currentdomain.getassemblies() |\r\nSort-Object -property fullname | \r\nformat-table fullname \r\n<\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2425-bef6f627-f44c-4340-b9ca-5afec8073d74.png\" alt=\"2425-bef6f627-f44c-4340-b9ca-5afec8073d7\" \/><\/p>\n<p>For our purposes, we just need to filter for SMO:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">([appdomain]::currentdomain.getassemblies()).where{$_.fullname -like '*smo*'}|\r\nSort-Object -property fullname | \r\nformat-table fullname \r\n<\/pre>\n<pre class=\"theme:powershell-output lang:ps\">FullName                                                                                           \r\n--------                                                                                           \r\nMicrosoft.SqlServer.Smo, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91        \r\nMicrosoft.SqlServer.Smo, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91        \r\nMicrosoft.SqlServer.SmoExtended, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91\r\nMicrosoft.SqlServer.SmoExtended, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91 \r\n<\/pre>\n<p>Finding out the version of the SQLPS module is a little different. PowerShell has a cmdlet called <a href=\"https:\/\/technet.microsoft.com\/en-us\/library\/hh849700.aspx\">Get-Module<\/a> that shows all the information on the modules.<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-Module<\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2425-529117bf-e738-43d5-b6dd-0c33f7876775.png\" alt=\"2425-529117bf-e738-43d5-b6dd-0c33f787677\" \/><\/p>\n<p>Also it has a parameter to check whether a specific module was loaded:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-Module -Name SQLPS<\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2425-87dff3e7-75ab-4027-970c-2d5c950d6b69.png\" alt=\"2425-87dff3e7-75ab-4027-970c-2d5c950d6b6\" \/><\/p>\n<p>Also you can check what modules are available to load using the parameter <strong>&#8211;<\/strong> <strong>ListAvaliable<\/strong><\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-Module -ListAvailable<\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2425-8bab422a-48f3-46f7-b436-62dfdf62168c.png\" alt=\"2425-8bab422a-48f3-46f7-b436-62dfdf62168\" \/><\/p>\n<p>The <strong>Get-Module<\/strong> cmdlet will search all the modules available in the Environment variable <strong>$Env:PSMODULEPATH,<\/strong> a special variable that contains all the paths for the installed modules and will list them for you.<\/p>\n<pre class=\"theme:powershell-output lang:ps\">PS C:\\temp&gt; $env:psmodulepath\r\nC:\\Users\\Laerte Junior\\Documents\\WindowsPowerShell\\Modules;C:\\Program Files\\WindowsPowerShell\\Modules;C:\\WINDOWS\\system32\\WindowsPowerShell\\v1.0\\Modules;C:\\Program Files \r\n(x86)\\Microsoft SQL Server\\120\\Tools\\PowerShell\\Modules\\;C:\\Program Files\\Microsoft Message Analyzer\\PowerShell\\;C:\\Program Files (x86)\\Microsoft SQL Server\\110\\Tools\\Pow\r\nerShell\\Modules\\ \r\n<\/pre>\n<h4>Determining the version of SQLPS that PowerShell will load<\/h4>\n<p>If you use the <strong>name<\/strong> parameter, PowerShell will always check in the <strong>$env:psmodulepath<\/strong> variable and then will, according fellow PowerShell MVP Joel Bennet, import them in the following order:<\/p>\n<p>Path order, then alphabetic order, then highest version if you have side-by-side modules in the same folder (in PS5)<\/p>\n<h4>Loading a specific version of SQLPS or SMO<\/h4>\n<p>That\u00b4s an interesting question that comes out in the comments on the SMO <a href=\"http:\/\/www.sqlservercentral.com\/articles\/SMO\/140966\/\">stairway<\/a>, and I will give a different answer for SQLPS, which is a module, and SMO which is a group of assemblies.<\/p>\n<p><strong>Loading a particular version of SQLPS <\/strong><\/p>\n<p>As we saw, if we use the parameter name in the import-module it will load the SQLPS in the order of the environment variable . So in this specific case we use the parameter <strong>-FullyQualifiedName<\/strong><\/p>\n<pre class=\"theme:powershell-output lang:ps\">Import-Module -FullyQualifiedName 'C:\\Program Files (x86)\\Microsoft SQL Server\\110\\Tools\\PowerShell\\Modules\\SQLPS\\sqlps' <\/pre>\n<p>Or even the parameter -name with the FQDN:<\/p>\n<pre class=\"theme:powershell-output lang:ps\">PS C:\\Users\\Laerte Junior&gt; Import-Module -name 'C:\\Program Files (x86)\\Microsoft SQL Server\\120\\Tools\\PowerShell\\Modules\\SQLPS\\sqlps' <\/pre>\n<p>The point is that you need to specify the path for the SQLPS module.<\/p>\n<p>Shawn Melton showed, in the comments, a very cool function to make the job easy:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">function Load-SQLModule {\r\n\t[cmdletbinding()]\r\n\tparam (\r\n\t\t$version = 130\r\n\t)\r\n\t\r\n\tGet-Module -ListAvailable -Name\tSQLPS | where {$_.path -match $version} | Import-module -DisableNameChecking | Out-Null\r\n\t}\r\n<\/pre>\n<p>In this function, you can pass as a parameter the version you want to import and the default is 130.<\/p>\n<p>This example will load the highest SQLPS version<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-Module -ListAvailable -Name\tSQLPS | \r\nSort-Object -Property Path -Descending | \r\nSelect-Object -First 1 -ExpandProperty Path |\r\nImport-module -DisableNameChecking  \r\n<\/pre>\n<p>We ended up with another version of Shawn\u00b4s function where you can choose the most recent SQLPS version by using a switch parameter <strong>-highest<\/strong><\/p>\n<pre class=\"theme:powershell-ise lang:ps\">function Load-SQLModule {\r\n\r\n [CmdletBinding( DefaultParameterSetName='Version' )]\r\n  param(\r\n [Parameter( Position=0,\r\n Mandatory=$false,\r\n ParameterSetName = 'Version')]\r\n [string] $version,\r\n\r\n [Parameter( Position=1,\r\n Mandatory=$false,\r\n ParameterSetName = 'Highest')]\r\n [Switch] $HighestOne\r\n )\r\n\r\n  If ($version) {\r\n      Get-Module -ListAvailable -Name SQLPS |\r\n      Where-Object {$_.path -match $version} |\r\n      Import-module -DisableNameChecking\r\n      $moduleBase = Get-Module -Name SQLPS | Select-Object ModuleBase\r\n  }\r\n  else {\r\n      Get-Module -ListAvailable -Name SQLPS |\r\n      Sort-Object -Property Path -Descending |\r\n      Select-Object -First 1 -ExpandProperty Path |\r\n      Import-module -DisableNameChecking\r\n      $moduleNBase = Get-Module -Name SQLPS | Select-Object ModuleBase\r\n  }\r\n  Write-Verbose \"Imported Module: $($modulebase)\"\r\n\r\n} \r\n<\/pre>\n<h5><strong>Loading a particular version of SMO:<\/strong><\/h5>\n<p>If you use the code that I included earlier on, it will only show you the assemblies that are already loaded in your session. If you use the <strong>loadwithpartialname<\/strong> method to load the SMO, this is controlled by the GAC* . If you want to load a specific version of SMO, I recommend that you should use <strong>ADD-Type<\/strong> and you can then specify which version will be loaded:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Add-Type -AssemblyName \"Microsoft.SqlServer.SMO, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91\"<\/pre>\n<p>Using the same idea from Shawn, we could think of doing something similar with SMO, but for this we would need to query the GAC: Let&#8217;s say we want to load the highest version SMO installed<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">$HighestOne = (Get-ChildItem -path C:\\Windows\\assembly -recurse | \r\nWhere-Object {$_.name -eq 'Microsoft.SQLServer.SMO.dll'} | \r\nSort-Object -Property fullname -desc |\r\nSelect-Object -First 1).fullname\r\n\r\nAdd-Type -LiteralPath $HighestOne  \r\n<\/pre>\n<p>We could then develop this to speed it up by saving on the pipeline traffic. This is achieved by doing the selection within the <strong>Get-ChildItem<\/strong> cmdlet.<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Add-Type -literalpath (Get-ChildItem -path \"$($env:windir)\\assembly\\GAC_MSIL\\Microsoft.SqlServer.Smo\" -include 'Microsoft.SQLServer.SMO.dll' -recurse | \r\nSort-Object -Property fullname -desc |\r\nSelect-Object -First 1).fullname\r\n<\/pre>\n<p>You can read more about how modules work in PowerShell <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/dd878284(v=vs.85).aspx\">here: (Importing a PowerShell Module<\/a>)<\/p>\n<p>*GAC or Global Assembly Cache is a machine-wide code where the assemblies are installed and stored to be shared with applications on the computer.<\/p>\n<h4>Creating a Custom Type Accelerator for SMO<\/h4>\n<p>You will sometimes find that when you load an assembly like SMO, that specifying the NET types is laborious. Type accelerators are a great help for getting around this. Type Accelerators are like shortcuts for .NET types. It can simplify your code and make it more intelligible if you use them. For instance [adsi] is a type accelerator for System.DirectoryServices.DirectoryEntry or [pscustomobject] for System.Management.Automation.PSObject. You can do the same for SMO<\/p>\n<p>This code shows the type accelerators currently defined in your session:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">[PSObject].Assembly.GetType('System.Management.Automation.TypeAccelerators')::get <\/pre>\n<pre class=\"theme:powershell-output lang:ps\">PS C:\\Users\\Laerte Junior&gt; [PSObject].Assembly.GetType('System.Management.Automation.TypeAccelerators')::get\r\n\r\nKey                          Value                                                             \r\n---                          -----                                                             \r\nAlias                        System.Management.Automation.AliasAttribute                       \r\nAllowEmptyCollection         System.Management.Automation.AllowEmptyCollectionAttribute        \r\nAllowEmptyString             System.Management.Automation.AllowEmptyStringAttribute            \r\nAllowNull                    System.Management.Automation.AllowNullAttribute                   \r\nArgumentCompleter            System.Management.Automation.ArgumentCompleterAttribute           \r\narray                        System.Array                                                      \r\nbool                         System.Boolean                                                    \r\nbyte                         System.Byte                                                       \r\nchar                         System.Char                                                       \r\nCmdletBinding                System.Management.Automation.CmdletBindingAttribute   \r\n<\/pre>\n<p>To create a custom type accelerator you just need to add the key and the .net type:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">[PSObject].Assembly.GetType('System.Management.Automation.TypeAccelerators')::add(\"Key\",\".Net Type\") <\/pre>\n<p>So for instance, to create a type accelerator for the SMO Server:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">[PSObject].Assembly.GetType('System.Management.Automation.TypeAccelerators')::Add(SMOSERVER','Microsoft.SqlServer.Management.Smo.Server') <\/pre>\n<p>Then I can easily access all properties and methods using the new type accelerator.<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">([SMOSERVER]'DeathStar').databases <\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2425-258062b3-15a8-44fb-9d7c-1ae173404c4e.png\" alt=\"2425-258062b3-15a8-44fb-9d7c-1ae173404c4\" \/><\/p>\n<p>Or also call a method:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">([SMOSERVER]'DeathStar').KillAllProcesses('Dagobah') <\/pre>\n<p>Now it\u00b4s just play around it and add in your PowerShell profile to load your custom SMO type accelerators<\/p>\n<p>Reference: <a href=\"https:\/\/learn-powershell.net\/2013\/10\/10\/quick-hits-finding-creating-and-removing-powershell-type-accelerators\/\">Boe Prox &#8211; Quick Hits: Finding, Creating and Removing PowerShell Type Accelerators<\/a><\/p>\n<h2>Conclusion<\/h2>\n<p>There really isn&#8217;t a single best method of accessing SQL Server via PowerShell. What you use depends on what you need to achieve. Every approach has its advantages and disadvantages. In this article, I&#8217;ve described how to get up and running with each of these approaches, and I&#8217;ve tried to suggest the best ways of approaching the task.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>The worst part for a DBA of getting started with PowerShell  is often just figuring out the best way of working with SQL Server. The most suitable approach to accessing SQL Server depends on the sort of task you need to produce a script for. Laerte Junior aims at a simple guide to the most common approaches and when to use them.&hellip;<\/p>\n","protected":false},"author":221715,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527],"tags":[],"coauthors":[6819],"class_list":["post-26193","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/26193","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221715"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=26193"}],"version-history":[{"count":11,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/26193\/revisions"}],"predecessor-version":[{"id":92200,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/26193\/revisions\/92200"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=26193"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=26193"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=26193"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=26193"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}