{"id":2147,"date":"2016-01-21T00:00:00","date_gmt":"2016-01-21T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/the-posh-dba-towards-the-re-usable-powershell-script\/"},"modified":"2021-08-24T13:39:34","modified_gmt":"2021-08-24T13:39:34","slug":"the-posh-dba-towards-the-re-usable-powershell-script","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/the-posh-dba-towards-the-re-usable-powershell-script\/","title":{"rendered":"The PoSh DBA &#8211; Towards the Re-usable PowerShell Script"},"content":{"rendered":"<div id=\"pretty\">\n<p class=\"start\">How would you improve a PowerShell script in order to make it more versatile? How can you ensure that it is adaptable to the many similar tasks for which you are likely to need a script? In a nutshell, you turn into functions those common component tasks that you&#8217;ve written as scripts, and from these simple functions develop advanced functions. In doing so, you make the leap from PowerShell Zero to PowerShell Hero.\u00a0\u00a0 In this article, I&#8217;ll walk you through a practical example and demonstrate the steps along the way<\/p>\n<p>Before we get too deep into the details, let&#8217;s start by providing a few general rules for developing in PowerShell.<\/p>\n<h1>The General Rules<\/h1>\n<p>There are the rules that I use in my day-to-day work in PowerShell. They&#8217;ve worked well for me over the years, but I&#8217;m not saying that they are carved in stone.<\/p>\n<ul>\n<li><strong>Learn the language<\/strong><br \/>\n            <br \/>You need to treat PowerShell as a serious .NET language. Like C#, F# or VB, it is impossible to know what the language can do, such as its features or commands without understanding the language and its paradigms? You can cut and splice other people&#8217;s scripts but you must have a good feel for the way that the language works before you can proceed any further<\/li>\n<li><strong>Use The Help<\/strong><br \/>\n            <br \/>For PowerShell, the help system is the first thing you must reach for: it must be your best friend. The designers of the language intended it to be well-used.<\/li>\n<li><strong>Use the\u00a0 PowerShell Community<\/strong><br \/>\n            <br \/>The PowerShell community is unique, because it has people who have come from a wide range of IT backgrounds. They bring their experience and wisdom with them. They will know more than you.\u00a0 The combination of skills multiplies the speed at which the language develops. Read their posts, download their script and learn from them.<\/li>\n<li>\u00a0<strong>Keep It Simple <\/strong><\/li>\n<li><strong>Do not use Aliases<\/strong>, except for deliberate obfuscation.<\/li>\n<li><strong>\u00a0Write with consideration<\/strong><br \/>\n            <br \/>Do not try to cram all your scripted process into one line.\u00a0 In the Shared and Corporate environment other people will maintain your code and will not necessarily have the same PowerShell knowledge as you. Be kind in your code.<\/li>\n<\/ul>\n<h1>When do not use PowerShell<\/h1>\n<p>Once you&#8217;ve gained confidence in PowerShell, there is a temptation to use it for everything. It is definitely not the solution for everything in SQL Server, because we already have a great deal of power in Transact SQL. One of the major skills for a Database person that uses PowerShell is to know when not to use PowerShell.<\/p>\n<p>PowerShell is the primary script language for Windows-based systems, providing a solid platform for automation, provisioning servers and so on. In a SQL Server, it is no different. You can do practically everything with PowerShell, via SMO and DACFx, but it worth it the time and effort?<\/p>\n<p>The answer to this question will hit you square in the face if you ever try to create a table using PowerShell and SMO.\u00a0 &#8216;Ha Laerte, but I can use T-SQL called by PowerShell for that.&#8217; Yes! You can but unless you need to automate, scale out, or repeat this operation, it is better to use SSMS and T-SQL.<\/p>\n<h1>Oh Boy! Script or Function?<\/h1>\n<p>All script files are actually functions. You can call them from another script by referring to their filepath, and also provide parameters to them.<\/p>\n<p>Technically speaking, a function is similar to a script variable, which is really an anonymous function or &#8216;closure&#8217;. What we refer to as a function is only a script to which\u00a0 you added a <b>function<\/b> keyword, its name, some parameters, some\u00a0 keywords and voooalla !!! We have a function that can be called by name.<\/p>\n<p>A working IT professional will, if writing a single-purpose\u00a0 solution, generally write a script. I did this in my latest article on <a href=\"http:\/\/www.simple-talk.com\/content\/article.aspx?article=2292\">automating SQL Server Best Practices Review<\/a> in Word. This is because:<\/p>\n<ul>\n<li>It\u00b4s a single solution<\/li>\n<li>Whoever will use should not be required to know anything about PowerShell, so If I had written a module with a lot of functions, this person would have had to perform an extra step to copy the module function to its correct path.<\/li>\n<li>It does not need to be fully-reusable<\/li>\n<\/ul>\n<p>If, however, you will use your script for more than one purpose, with flexible outputs, especially if you will use it in other scripts then make it reusable. You&#8217;ll need to switch to using a function and add this function to your function module. If you are unsure how a set of scripts will develop, but it is likely that they will be extended and enhanced, you will need to use functions.<\/p>\n<h1>From Zero<\/h1>\n<p>Imagine this. Your devoted boss comes to you, with a couple coffee for you, Brazilian of course, and with an ingratiating smile.<\/p>\n<p>&#8220;Could you please get the Name and Recovery Model of all of our databases and instances and export them to a csv file for me, if it isn&#8217;t too much trouble? I need it for the Business Recovery plan.&#8221;<\/p>\n<p>&#8220;Of course, boss. The sight of that coffee you&#8217;re offering me makes me putty in your hands&#8221;<\/p>\n<p>And then you script\u00a0 it :<\/p>\n<pre class=\"theme:powershell-ise lang:tsql\">[Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.SMO\")\r\n[Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.ConnectionInfo\")\r\nGet-Content c:\\laerte\\ST\\4article\\InstanceNames.txt |\r\nForEach-Object {\r\n\u00a0\u00a0\u00a0 $InstanceName = $_ -replace '\\\\' ,'_'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $connection = new-object (\"Microsoft.SqlServer.Management.Common.ServerConnection\")\u00a0 $_\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $connection.Connect()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $SQLServer = new-object (\"Microsoft.SqlServer.Management.Smo.Server\") $connection\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $SQLServer.databases |\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ForEach-Object {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [pscustomobject] @{\u00a0 'Name'=$_.name\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'RecoveryModel' = $_.RecoveryModel\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 } \r\n\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\u00a0\u00a0\u00a0 |\r\n\u00a0\u00a0\u00a0 Export-Csv -force -NoClobber -NoTypeInformation -Path \"c:\\laerte\\ST\\4article\\$($InstanceName)_DatabaseConfiguration.csv\"\r\n} \r\n<\/pre>\n<p>This code will export the Name and Recovery Model for each database within all SQL Server instances whose names are contained in the file Instance <code>Names.TXT<\/code> and write the data to a file called <code>InstanceName_DatabaseConfigurations.csv<\/code> .<\/p>\n<p>You will, at this stage, deliver the file, and save the script for later use. If you will use it just one time or even if you then adapt it for your best practices report, it is fine, both technically and conceptually. It is a waste of time to develop it further. However, on reflection, you may suspect that you&#8217;ll need something very similar to determine more information about the database configuration information. You could, of course, just re-work it for each occasion; it will keep working technically of course, but you will regret it. Let us check why:<\/p>\n<h2>Success breeds requests<\/h2>\n<p>Your boss pops his head around the door, He has a glazed smile.<\/p>\n<p>\u00a0&#8220;Could I please trouble you\u00a0 to show to me the same configuration information for the\u00a0 servers remote office in Sao Paulo? It turns out that these need to be in my report. I&#8217;ll need to know the owner as well, since this isn&#8217;t documented for that office&#8221;<\/p>\n<p>Ha. You will probably need to :<\/p>\n<ul>\n<li>Make a copy of your original script<\/li>\n<li>Re-code the copy of the script to support his conditions<\/li>\n<\/ul>\n<p>Sometime later, he&#8217;s back:<\/p>\n<p>&#8220;Excellent, excellent, and I just hope that it isn&#8217;t going to be too much effort to get the owners of all the databases, not just the ones in the remote office. The DR planners also need to know the size of the databases to get an estimate of recovery time. While you&#8217;re in the mood for this, the BI team need to know the collation for all the databases contributing to the warehouse. &#8220;<\/p>\n<p>Holy cow! You will have at least four scripts to perform practically the same operation. It looks as if it is time to take a step back and provide a design that allows you to get any configuration item you want from any combination of servers and databases<\/p>\n<h1>To 25%<\/h1>\n<h2>Identifying the component tasks<\/h2>\n<p>Lets study that script. It is connecting to each server and returning configuration information from SMO&#8217;s Server class and storing it in a $SQL Server object<\/p>\n<pre class=\"theme:powershell-ise lang:tsql\">$SQLServer =new-object (\"Microsoft.SqlServer.Management.Smo.Server\")$connection <\/pre>\n<p>This class contains a lot of information about the SQL Server Instance, all of which is relevant to the server level. Let&#8217;s just see what is in it by simply getting all the members of the class.<\/p>\n<p>\n        <a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2344-imgE3.jpg\"> <img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2344-clip_image002.jpg\" alt=\"2344-clip_image002.jpg\" width=\"567\" height=\"399\" \/><\/a>\n    <\/p>\n<p>Hmm. There is a heck of a lot of useful information in there. Therefore, if our devoted boss by chance returns and wants some more information about it, I will know where to find it, and have access to it.<\/p>\n<p>That script will also return ALL information about the SMO Database class and return all database objects: Let&#8217;s see what is available from the database class.<\/p>\n<p>\n        <a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2344-imgE2.jpg\"> <img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2344-clip_image004.jpg\" alt=\"2344-clip_image004.jpg\" width=\"567\" height=\"384\" \/><\/a>\n    <\/p>\n<p>\u00a0Yes, there is plenty there that is useful information. At this point, we can see the potential advantages of splitting that script into two functions, one to connect and return the SMO Server class and other one to return each SMO Database class. Let us do it<\/p>\n<h1>Advancing to 50%<\/h1>\n<p>First we need to separate the code to the two functions: One to connect and return the Server SMO class, and other one to return the SMO Database collection.<\/p>\n<h2>Server SMO<\/h2>\n<pre class=\"theme:powershell-ise lang:tsql\">[Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.SMO\")\r\n[Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.ConnectionInfo\") \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $connection = new-object (\"Microsoft.SqlServer.Management.Common.ServerConnection\")\u00a0 $_\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $connection.Connect()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $SQLServer = new-object (\"Microsoft.SqlServer.Management.Smo.Server\") $connection \r\n<\/pre>\n<h2>Database SMO<\/h2>\n<p>It is the Server SMO Plus Databases SMO<\/p>\n<pre class=\"theme:powershell-ise lang:tsql\">[Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.SMO\")\r\n[Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.ConnectionInfo\") \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $connection = new-object (\"Microsoft.SqlServer.Management.Common.ServerConnection\")\u00a0 $_\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $connection.Connect()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $SQLServer = new-object (\"Microsoft.SqlServer.Management.Smo.Server\") $connection \r\n$SQLServer.databases \r\n<\/pre>\n<p>What code is common in the two ones? The connection and SMO Server: This points to the advantage of calling the Server SMO Function inside Database SMO Function.<\/p>\n<p>Why, at this point, am I not considering the task of exporting the data as CSV, displaying it on the screen or even choosing the database properties to display? This is because a function is best if it does just one thing very well. In this case, the SMO Server function just needs to deal with contacting the SMO Server and the same applies to the Database SMO function. They will both need to do it robustly, eventually with appropriate error handling. The idea is to use the features of the language such as the pipeline, the native output cmdlets (export-&#8230;., format-..), display cmdlets (write-..) and select property cmdlets (select-object).\u00a0 They are all there to help.<\/p>\n<p>Before we start, we should decide &#8230;<\/p>\n<ul>\n<li>The Function name:\u00a0 It should make it obvious as to what the function does. Use Approved Verbs &#8211; <a href=\"https:\/\/technet.microsoft.com\/en-us\/library\/ms714428(v=vs.85).aspx\"> Approved Verbs for Windows PowerShell Commands<\/a><b><\/b><\/li>\n<li>The Parameter Keyword and Parameter Name<br \/>\n            <br \/>Use a parameter name that says for what it is. For instance, it&#8217;s a SQL Server Instance Name, so <code>ServerInstance<\/code> should be nice. use common sense please: Don&#8217;t use names like<code> ThisIsTheParameterToUseSQLServerInstance<\/code><\/li>\n<li>Datatype of the Parameter.<br \/>\n            <br \/> Choose\u00a0 the type carefully and specify it explicitly. If it is an Int, use <code>int<\/code>, if it is Boolean, use<code> switch<\/code> &#8211; why <code>switch<\/code>?\u00a0 In PowerShell, we rarely use<code> Boolean<\/code> as parameter, because we have a special and more appropriate datatype for that. The <code>Switch<\/code> one. It works like a n on\/off light-switch. When the parameter with a <code>switch<\/code> datatype is typed, PowerShell uses like <code>ON<\/code>. When it is not passed, PowerShell assumes <code>OFF<\/code>. Again, learn about the language.<\/li>\n<\/ul>\n<p>We have the functions <code>Get-SQLServer <\/code>and <code>Get-SQLDatabase<\/code><\/p>\n<h3>SQL Server Server SMO<\/h3>\n<pre class=\"theme:powershell-ise lang:tsql\">\t\tFunction Get-SQLServer {\r\n\u00a0\u00a0\u00a0 param (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [string]$ServerInstance\r\n\u00a0\u00a0\u00a0 )\r\n\u00a0\r\n\u00a0\u00a0\u00a0 [Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.SMO\") | Out-Null\r\n\u00a0\u00a0\u00a0 [Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.ConnectionInfo\") | Out-Null\r\n\u00a0\u00a0\u00a0 $connection = new-object (\"Microsoft.SqlServer.Management.Common.ServerConnection\")\u00a0 $ServerInstance\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $connection.Connect()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $SQLServer = new-object (\"Microsoft.SqlServer.Management.Smo.Server\") $connection\r\n\u00a0\u00a0\u00a0 Write-Output $SQLServer\r\n\u00a0\r\n}\r\n\u00a0\r\nGet-SQLServer\u00a0 -ServerInstance ObiWan \r\n<\/pre>\n<h3>Database SMO<\/h3>\n<p>Because\u00a0 Get-SQLServer is\u00a0 now a reusable function, so I will reuse-it.<\/p>\n<pre class=\"theme:powershell-ise lang:tsql\">Function Get-SQLDatabase {\r\n\u00a0\u00a0\u00a0 param (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [string]$ServerInstance\r\n\u00a0\r\n\u00a0\u00a0\u00a0 )\r\n\u00a0\u00a0\u00a0 $SQLServer = Get-SQLServer\u00a0 -ServerInstance $ServerInstance\r\n\u00a0\u00a0\u00a0 Write-Output $SQLServer.Databases\r\n}\r\n\t<\/pre>\n<p>And so we will test it.<\/p>\n<pre class=\"theme:powershell-ise lang:tsql\">Get-SQLDatabase -ServerInstanceObiWan <\/pre>\n<p>Wait! Wait! wait!, it is missing something. <code>Get-SQLDatabase<\/code> needs to have a parameter to choose the database name of course ; If it is not passed, assume all databases.<\/p>\n<pre class=\"theme:powershell-ise lang:tsql\">Function Get-SQLDatabase {\r\n\u00a0\u00a0\u00a0 param (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [string]$ServerInstance,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [String]$DatabaseName\r\n\u00a0\r\n\u00a0\u00a0\u00a0 )\r\n\u00a0\u00a0\u00a0 $SQLServer = Get-SQLServer\u00a0 -ServerInstance $ServerInstance\r\n\u00a0\r\n\u00a0\u00a0\u00a0 if ($PSBoundParameters.ContainsKey('DatabaseName'))\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Write-Output $SQLServer.Databases[$DatabaseName]\r\n\u00a0\u00a0\u00a0 } else {\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Write-Output $SQLServer.Databases\r\n\u00a0\u00a0\u00a0 }\r\n\u00a0\r\nGet-SQLDatabase -ServerInstance ObiWan -DatabaseName R2D2 \r\n<\/pre>\n<p>Time to go back in our first script. Let&#8217;s rewrite it using our functions :<\/p>\n<pre class=\"theme:powershell-ise lang:tsql\">Get-Content c:\\laerte\\ST\\4article\\InstanceNames.txt |\r\nForEach-Object {\r\n\u00a0\u00a0\u00a0 $InstanceName = $_ -replace '\\\\' ,'_'\r\n\u00a0\u00a0\u00a0 Get-SQLDatabase -ServerInstance $_ |\r\n\u00a0\u00a0\u00a0 Select-Object Name,RecoveryModel |\r\n\u00a0\u00a0\u00a0 Export-Csv -force -NoClobber -NoTypeInformation -Path \"c:\\laerte\\ST\\4article\\$($InstanceName)_DatabaseConfiguration.csv\"\r\n} \r\n<\/pre>\n<p>With these two functions, we can select whatever data we need, not just the Name and Recovery Model, using <code>Select-Object<\/code>. We can order the report as we like, and export the result in a variety of formats with the &#8216;ConvertTo-&#8216;, and &#8216;Export&#8217; Cmdlets. Your boss can ask you to export to a text file, xml document, whatever he wants; \u00a0and better, can choose what property he wants. You just need to change your final script, not the core of your solution. We can improve the core solution in just one place.<\/p>\n<h1>Getting to 75%<\/h1>\n<p>One of the most important native cmdlets is <code>Get-Member.<\/code> It shows you all the members &#8211; properties, methods, events and so on &#8211;\u00a0 of the object. So far, we&#8217;ve used it to check what members we can use to\u00a0 output from our functions\u00a0 :<\/p>\n<pre class=\"theme:powershell-ise lang:tsql\">Get-SQLServer\u00a0 -ServerInstance 'obiwan' | Get-member <\/pre>\n<p>\n        <a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2344-imgE4.jpg\"> <img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2344-clip_image006.png\" alt=\"2344-clip_image006.png\" width=\"567\" height=\"186\" \/><\/a>\n    <\/p>\n<h2>The Pipeline<\/h2>\n<p>&#8216;Hey Laerte, that pipeline is cool! I am piping the stream of\u00a0 objects produced by the <code> Get-SQLServer <\/code>object to the <code> Get-Member<\/code> cmdlet, so can I pipe the Instance Names to Get-SQLServer?&#8217;<br \/>\n        <br \/> Not yet..lets just\u00a0 go to 75%.<\/p>\n<p>What allows\u00a0 us to pipe information to cmdlets or functions is a very cool advanced parameter argument called <code> \u00a0ValueFromPipeline<\/code> and <b>ValueFromPipelineByPropertyName<\/b>. In PowerShell 3.0 and later you can just specify this argument in the parameter keyword, in the version 2.0 you need to assign $true to that. Here is an example of each technique<\/p>\n<h3>PowerShell 2.0<\/h3>\n<div class=\"indent\">\n<pre class=\"theme:powershell-ise lang:tsql\">Param\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [parameter(Mandatory=$true,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValueFromPipeline=$true)]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [String]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0$ServerInstance\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ) \r\n\u00a0\r\n\u00a0Param\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [parameter(Mandatory=$true,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValueFromPipelineByPropertyName=$true)]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [String]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $ServerInstance\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ) \r\n<\/pre>\n<\/div>\n<h3>PowerShell 3.0 and later<\/h3>\n<div class=\"indent\">\n<pre class=\"theme:powershell-ise lang:tsql\">\t\t\t \u00a0\u00a0\u00a0Param\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [parameter(Mandatory,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValueFromPipeline)]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [String]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $ServerInstance\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ) \r\n\u00a0\r\n\u00a0Param\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [parameter(Mandatory,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValueFromPipelineByPropertyName)]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [String]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $ServerInstance\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\u00a0 \r\n\t<\/pre>\n<\/div>\n<h2>Advanced Functions<\/h2>\n<p>&#8216;Is that all there is to it?&#8217;<br \/>\n        <br \/> well, not yet. We know that PowerShell streams objects using the pipeline, but we may need to do some preliminary work at the start of the flow of objects, and maybe something else at the end to wrap up. If so, then we also need to add some block keywords to deal appropriately with the steaming information.\u00a0 For a further information, check <a href=\"https:\/\/www.simple-talk.com\/sql\/sql-tools\/the-posh-dba-grown-up-powershell-functions\/\"> The PoSh DBA: Grown-Up PowerShell Functions<\/a><\/p>\n<p>Basically as I said in the article I&#8217;ve indicated that &#8230;<\/p>\n<ul>\n<li><code> Begin<\/code> {&lt;Block&gt;} : The code inside this block, will be executed first.<\/li>\n<li><code> Process<\/code> {&lt;Block&gt;}\u00a0 : The code inside this block will execute once FOR EACH value piped<\/li>\n<li><code> End <\/code> {&lt;Block&gt;} : When everything is processed, this block executes the code inside it once.<\/li>\n<\/ul>\n<p>Our function <code>Get-SQLServer<\/code> could have :<\/p>\n<ul>\n<li>Begin Block to load the assemblies<\/li>\n<li>Process Block to get the piped value<\/li>\n<li>No End Block &#8211; We don&#8217;t need it<\/li>\n<\/ul>\n<pre class=\"theme:powershell-ise lang:tsql\">\t\tFunction Get-SQLServer {\r\n\t\t \u00a0\u00a0\u00a0 param (\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [parameter(\u00a0\u00a0 Mandatory,\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValueFromPipeline,\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValueFromPipelineByPropertyName)]\r\n\t\t\u00a0\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [string]$ServerInstance\r\n\t\t \u00a0\u00a0\u00a0 )\r\n\t\t \u00a0\u00a0\u00a0 begin {\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.SMO\") | Out-Null\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.ConnectionInfo\") | Out-Null\r\n\t\t \u00a0\u00a0\u00a0 }\r\n\t\t \u00a0\u00a0\u00a0 Process {\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $connection = new-object (\"Microsoft.SqlServer.Management.Common.ServerConnection\")\u00a0 $ServerInstance\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 $connection.Connect()\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 $SQLServer = new-object (\"Microsoft.SqlServer.Management.Smo.Server\") $connection\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0  Write-Output $SQLServer\r\n\t\t \u00a0\u00a0\u00a0 }\r\n\t\t \u00a0\u00a0\u00a0 End{}\r\n\t\t<\/pre>\n<p>And we can pipe the SQL Server Instances to our function or take them from a list in a file:<\/p>\n<pre class=\"theme:powershell-ise lang:tsql\">\t\t\t\t\"ObiWan\",\"Vader\",\"R2D2\" | Get-SQLServer \r\n\t\t\u00a0\r\n\t\tGet-Content c:\\laerte\\ST\\4article\\InstanceNames.txt |\r\n\t\tGet-SQLServer\u00a0  \r\n<\/pre>\n<p>If you&#8217;ve been following the reasoning, you will realize that, if you use the intellisense from PowerShell ISE, you will be able to see a list of all the possible parameters when you start to type and you will see some other parameters that we haven&#8217;t\u00a0 mentioned.<\/p>\n<p>\u00a0<img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2344-clip_image008.png\" alt=\"2344-clip_image008.png\" width=\"341\" height=\"201\" \/><\/p>\n<p>Those guys in the intellisense list (&#8216;verbose&#8217; downwards) are called common parameters. As the name says, they are common to every cmdlet &#8211; or function that now acts like a cmdlet.<\/p>\n<p>&#8220;What ? What ? You mean a function that acts like a cmdlet ?&#8221;<\/p>\n<p>Yes. They are called &#8216;advanced functions&#8217; and you can change to using them by two different ways :<\/p>\n<ul>\n<li>Specifying the <code>cmdletbinding()<\/code><\/li>\n<li>Just adding the parameter argument inside the <code> param <\/code>keyword as we did. It will then assume the <code>cmdletbinding()<\/code><\/li>\n<\/ul>\n<pre class=\"theme:powershell-ise lang:tsql\">\t\tparam (\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [parameter(\u00a0\u00a0 \r\n<\/pre>\n<p>Again, there is a lot more on the advanced functions. You can check some on <a href=\"https:\/\/www.simple-talk.com\/sql\/sql-tools\/the-posh-dba-grown-up-powershell-functions\/\">The PoSh DBA: Grown-Up PowerShell Functions<\/a>\u00a0\u00a0<\/p>\n<p>Ok. Our functions already accepts pipeline input but have you realized that, in some cmdlets, there are some parameters whose type is <code>string[] <\/code>? Not only <code>string<\/code>, they have the <code>[] also<\/code>.<\/p>\n<p>This means that this parameter accepts an array of strings.<\/p>\n<p>We can do it in our code too. Let&#8217;s\u00a0 just change the parameter to string[] and also as now it accepts an array, using a <code>foreach<\/code> statement to support the code :<\/p>\n<pre class=\"theme:powershell-ise lang:tsql\">\t\tFunction Get-SQLServer {\r\n\t\t \u00a0\u00a0\u00a0 param (\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [parameter(\u00a0\u00a0 Mandatory,\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValueFromPipeline,\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValueFromPipelineByPropertyName)]\r\n\t\t\u00a0\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [string[]]$ServerInstance\r\n\t\t \u00a0\u00a0\u00a0 )\r\n\t\t \u00a0\u00a0 \u00a0begin {\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.SMO\") | Out-Null\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.ConnectionInfo\") | Out-Null\r\n\t\t \u00a0\u00a0\u00a0 }\r\n\t\t \u00a0\u00a0\u00a0 Process {\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 foreach ($ServerInstances in $ServerInstance) {\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $connection = new-object (\"Microsoft.SqlServer.Management.Common.ServerConnection\")\u00a0 $ServerInstances\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $connection.Connect()\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $SQLServer = new-object (\"Microsoft.SqlServer.Management.Smo.Server\") $connection\r\n\t\t \u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Write-Output $SQLServer\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\t\t \u00a0\u00a0\u00a0 }\r\n\t\t \u00a0\u00a0\u00a0 End{}\r\n\t\t\u00a0\r\n\t\t} \r\n\t\t<\/pre>\n<p>And we can now pass an array of Instances to the parameter <b>serverinstance<\/b> :<\/p>\n<pre class=\"theme:powershell-ise lang:tsql\">\t\tGet-SQLServer -ServerInstance \"ObiWan\",\"Vader\",\"R2D2\"\r\n\t\tGet-SQLServer -ServerInstance (Get-Content c:\\laerte\\ST\\4article\\InstanceNames.txt ) \r\n<\/pre>\n<p>Yes baby. Now we have pipeline input, parameters that accept arrays and common parameters. We haven&#8217;t got to 100% yet. To do that, we need a little more, but very important: Error-handling:<\/p>\n<h1>Reaching 100%<\/h1>\n<h2>Errors<\/h2>\n<p>We need to handle the errors inside the function. Let&#8217;s try and catch them :<\/p>\n<pre class=\"theme:powershell-ise lang:tsql\">\t\tFunction Get-SQLServer {\r\n\t\t \u00a0\u00a0\u00a0 param (\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [parameter(\u00a0\u00a0 Mandatory,\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValueFromPipeline,\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValueFromPipelineByPropertyName)]\r\n\t\t\u00a0\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [string[]]$ServerInstance\r\n\t\t \u00a0\u00a0\u00a0 )\r\n\t\t \u00a0\u00a0\u00a0 begin {\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.SMO\") | Out-Null\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.ConnectionInfo\") | Out-Null\r\n\t\t \u00a0\u00a0\u00a0 }\r\n\t\t \u00a0\u00a0\u00a0 Process {\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 foreach ($ServerInstances in $ServerInstance) {\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 try {\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $connection = new-object (\"Microsoft.SqlServer.Management.Common.ServerConnection\")\u00a0 $ServerInstances\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $connection.Connect()\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $SQLServer = new-object (\"Microsoft.SqlServer.Management.Smo.Server\") $connection\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Write-Output $SQLServer\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 } catch {\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Write-Error $_.exception\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0  }\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\t\t \u00a0\u00a0\u00a0 }\r\n\t\t \u00a0\u00a0\u00a0 End{}\r\n\t\t\u00a0\r\n\t\t} \r\n<\/pre>\n<p>Why this is so important? We have the common parameter <code>ErrorAction,<\/code> and we can easily deal with the flow of the script with <b>try<\/b> &#8230; <code>catch <\/code>blocks and the <code>ErrorAction<\/code> parameter<\/p>\n<p>\u00a0Perform the test :<\/p>\n<pre class=\"theme:powershell-ise lang:tsql\">\t\ttry {\r\n\t\t \u00a0\u00a0\u00a0 Get-SQLServer -ServerInstance \"DoesNotExists\" \r\n\t\t} catch {\r\n\t\t\u00a0\r\n\t\t \u00a0\u00a0\u00a0 \"Oops! something went wrong, Laerte \"\r\n\t\t} \r\n\t\t<\/pre>\n<p>This code will stop the script rather than catch and recover. It will use the error handler inside the function. If you want the error to be logged but prevent the script from stopping, you may need to use the <code>ErrorAction<\/code>\u00a0 parameter :<\/p>\n<pre class=\"theme:powershell-ise lang:tsql\">\t\ttry {\r\n\t\t \u00a0\u00a0\u00a0 Get-SQLServer -ServerInstance \"DoesNotExists\" -ErrorAction Stop\r\n\t\t} catch {\r\n\t\t\u00a0\r\n\t\t \u00a0\u00a0\u00a0 \"Oops! something went wrong, Laerte\"\r\n\t\t} \u00a0\r\n\t<\/pre>\n<p>Let&#8217;s apply the same code to the <code>Get-SQLDAtabase<\/code> function :<\/p>\n<pre class=\"theme:powershell-ise lang:tsql\">\t\tFunction Get-SQLDatabase {\r\n\t\t\u00a0\r\n\t\t \u00a0\u00a0\u00a0 param (\r\n\t\t \u00a0\u00a0\u00a0\u00a0 [parameter(\u00a0\u00a0 Mandatory,\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValueFromPipeline,\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValueFromPipelineByPropertyName)]\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [string[]]$ServerInstance,\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [String]$DatabaseName\r\n\t\t\u00a0\r\n\t\t \u00a0\u00a0\u00a0  )\r\n\t\t \u00a0\u00a0\u00a0 begin{}\r\n\t\t \u00a0\u00a0\u00a0 process{\r\n\t\t\u00a0\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 foreach ($ServerInstances in $ServerInstance) {\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 try {\r\n\t\t\u00a0\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $SQLServer = Get-SQLServer\u00a0 -ServerInstance $ServerInstances\r\n\t\t\u00a0\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if ($PSBoundParameters.ContainsKey('DatabaseName'))\u00a0 {\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Write-Output $SQLServer.Databases[$DatabaseName]\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 } else {\u00a0\u00a0\u00a0 \r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Write-Output $SQLServer.Databases\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 } catch {\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Write-Error $_.exception\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\t\t \u00a0\u00a0\u00a0 }\r\n\t\t \u00a0\u00a0\u00a0 end {}\r\n\t\t}\r\n\t\t<\/pre>\n<p>Do you remember our first attempt at the\u00a0 code?<\/p>\n<pre class=\"theme:powershell-ise lang:tsql\">\t\t\t\t#System.IO.StreamReader or Microsoft.VisualBasic.FileIO.TextFieldParser f\r\n\t\t\u00a0\r\n\t\t[Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.SMO\")\r\n\t\t[Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.ConnectionInfo\")\r\n\t\tGet-Content c:\\laerte\\ST\\4article\\InstanceNames.txt |\r\n\t\tForEach-Object {\r\n\t\t \u00a0\u00a0\u00a0 $InstanceName = $_ -replace '\\\\' ,'_'\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $connection = new-object (\"Microsoft.SqlServer.Management.Common.ServerConnection\")\u00a0 $_\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $connection.Connect()\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $SQLServer = new-object (\"Microsoft.SqlServer.Management.Smo.Server\") $connection\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $SQLServer.databases |\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ForEach-Object {\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [pscustomobject] @{\u00a0 'Name'=$_.name\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'RecoveryModel' = $_.RecoveryModel\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0 } \r\n\t\t\u00a0\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\u00a0\u00a0\u00a0 |\r\n\t\t \u00a0\u00a0\u00a0 Export-Csv -force -NoClobber -NoTypeInformation -Path \"c:\\laerte\\ST\\4article\\$($InstanceName)_DatabaseConfiguration.csv\"\r\n\t\t} \r\n<\/pre>\n<p>We now can rewrite it to :<\/p>\n<pre class=\"theme:powershell-ise lang:tsql\">\t\tGet-Content c:\\laerte\\ST\\4article\\InstanceNames.txt |\r\n\t\tGet-SQLDatabase |\r\n\t\tSelect-Object\u00a0\u00a0 @{N='Server Name';E={$_.Parent.Name}},\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Name,\r\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RecoveryModel |\r\n\t\tExport-Csv -force -NoClobber -NoTypeInformation -Path \"c:\\laerte\\ST\\4article\\DatabaseConfiguration.csv\" \r\n\t<\/pre>\n<p>Powercool!\u00a0<\/p>\n<h1>Conclusions<\/h1>\n<p>The next step could be save it in a script module to reuse it in your PowerShell sessions. For the details of doing this, see again, <a href=\"https:\/\/www.simple-talk.com\/sql\/sql-tools\/the-posh-dba-grown-up-powershell-functions\/\">The PoSh DBA: Grown-Up PowerShell Functions<\/a> &#8211; Working with Modules<\/p>\n<p>\u00a0We have walked through how you might create your first reusable solution using PowerShell. There is plenty more scope for refining what we&#8217;ve achieved but we have got to 100% in our aim of being able to create functions so as to allow us to reuse scripts for a variety of tasks. The more versatile we make our individual functions, the greater the scope for reuse and at the same time we can keep the maintenance overhead within bounds. This way, we don&#8217;t get swamped by a multitude of scripts all very slightly different.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>When starting out with PowerShell, it is hard to escape from the detail to work out the best strategy for creating scripts. Laerte explains how, when and why it pays to think in terms of versatile functions to meet varying demands.&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":[4170,4635,4150,5651],"coauthors":[6819],"class_list":["post-2147","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","tag-database-administration","tag-powershell","tag-sql","tag-the-posh-dba"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2147","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=2147"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2147\/revisions"}],"predecessor-version":[{"id":92206,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2147\/revisions\/92206"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=2147"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=2147"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=2147"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=2147"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}