{"id":446,"date":"2008-09-30T00:00:00","date_gmt":"2008-09-30T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/why-this-sql-server-dba-is-learning-powershell\/"},"modified":"2021-08-24T13:40:39","modified_gmt":"2021-08-24T13:40:39","slug":"why-this-sql-server-dba-is-learning-powershell","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/why-this-sql-server-dba-is-learning-powershell\/","title":{"rendered":"PowerShell for SQL Server DBAs: Managing Multiple Servers, SQL Tasks, WMI, and SMO &#8211; A DBA&#8217;s Introduction"},"content":{"rendered":"<div id=\"pretty\">\n<p class=\"start\">I started studying PowerShell because I was looking for a quicker, more efficient way to gather information regarding my SQL Servers, and to manage my server workload better. I thought I was just going to learn yet another new scripting language that would help me do this. In fact, alongside providing a powerful means to automate many of my common, repetitive server tasks and heath checks, I&#8217;ve found that learning PowerShell has also proved a useful springboard to improving my skills with other, related technologies. For example, while learning PowerShell, I&#8217;ve found that I&#8217;ve:<\/p>\n<ul>\n<li>Improved my knowledge of .NET, so that I can talk more intelligently to the Application Developers I support.<\/li>\n<li>Learned how to use Server Management Objects (SMO) to automate my database-related tasks.<\/li>\n<li>Learned about Windows Management Instrumentation (WMI), allowing me to query one or more servers for a piece of information.<\/li>\n<li>Become more comfortable with object-oriented programming.<\/li>\n<\/ul>\n<p>In this article, I will describe several examples of using PowerShell that I would hope a DBA would find useful. My scripts will demonstrate how to run SQL queries, WMI queries or SMO code on one or more machines, and help you to better manage multiple database servers. All of the scripts have been tested on SQL Server 2005.<\/p>\n<p>This article is not intended to be a PowerShell tutorial. I assume that you are familiar with the basic PowerShell terminology, how to get help with the cmdlets, how the command line works, how to run a script, what a pipeline is, what aliases are, and so on. If not, plenty of help can be found in various online articles, newsgroups and blogs (a reference section, at the end of the article, lists some of these sources). Some of the scripts I include with this article were derived from examples I encountered during my readings.<\/p>\n<h1>Managing Multiple Servers using PowerShell<\/h1>\n<p>At the heart of multiple server management with PowerShell is a simple list of the servers on which you wish to run your routine tasks and health checks.<\/p>\n<p>In my examples, I use a <b>AllServers.txt<\/b> file that simply contains a list of my servers, in the following format:<\/p>\n<pre>Server1\r\nServer2\r\nServer3\r\n...\r\n<\/pre>\n<p>As I will demonstrate in the examples, I use this list to run a task against each listed server, using a <b>foreach<\/b> loop. This simple server list forms the cornerstone for completing repetitive tasks.<\/p>\n<p>I work primarily in a Microsoft environment and I&#8217;m finding it quicker to perform repetitive tasks with PowerShell than I previously did with Python. For instance, whereas Python needs multiple lines to open, read, and close a file, the <b>get-content<\/b> cmdlet in PowerShell reads a file with one line of code:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\"># Read a file\r\nget-content \"C:\\AllServers.txt\" \r\n<\/pre>\n<p>If even that feels like too much typing, you can invoke the get-content cmdlet via its alias:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">gc \"C:\\AllServers.txt\" \r\n<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/576-image002.jpg\" alt=\"576-image002.jpg\" width=\"331\" height=\"219\" \/><br \/>\n<b>Figure1:<\/b> Invoking the get-content cmdlet<\/p>\n<p>The defined best practice is to use the alias at the command line and the complete cmdlet in scripts, for readability.\u00a0 You can list all the PowerShell aliases with the <b>get-alias<\/b> cmdlet:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\"># List aliases, sort by name or definition\r\nget-alias | sort name\r\nget-alias | sort definition\r\n<\/pre>\n<p>PowerShell is both an interactive command line and scripting environment.<\/p>\n<p>I start solving a problem by executing commands at the command line. \u00a0When I have established the correct sequence of commands, I save them to a script file with the .ps1 extension and execute it as needed.<\/p>\n<h1>Automating Repetitive Tasks<\/h1>\n<p>PowerShell makes it easier for me to automate common, repetitive tasks across all my servers, and to deal quickly and efficiently with the seemingly-endless stream of ad-hoc requests for some bit of information about each of them.<\/p>\n<p>The following sections describe just some of the PowerShell scripts I&#8217;ve created to automate these repetitive tasks. The examples progress from those I found the easiest to convert to PowerShell to those that took more effort to solve.<\/p>\n<h2>SQL Tasks<\/h2>\n<p>The easiest tasks to convert from Python to PowerShell were those that executed a SQL query against multiple machines. In these examples, the basic procedure followed in each script is as follows:<\/p>\n<ol>\n<li>Read a list of database servers and for each server<\/li>\n<li>Create a data table to store the results<\/li>\n<li>Establish a connection to the server<\/li>\n<li>Run the query and format its output.<\/li>\n<\/ol>\n<h3>Checking Versions of SQL Server on Multiple Servers<\/h3>\n<p>I run the following script to determine if my servers are at the approved patch level for our company:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\"># SQLVer.ps1\r\n# usage: .\/SQLVer.ps1\r\n\r\n# Check SQL version\r\nforeach ($svr in get-content \"C:\\data\\AllServers.txt\")\r\n{\r\n\u00a0 $con = \"server=$svr;database=master;Integrated Security=sspi\" \r\n\u00a0 $cmd = \"SELECT SERVERPROPERTY('ProductVersion') AS Version, SERVERPROPERTY('ProductLevel') as SP\"\r\n\u00a0 $da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)\r\n\u00a0 $dt = new-object System.Data.DataTable\r\n\u00a0 $da.fill($dt) | out-null\r\n\u00a0 $svr\r\n\u00a0 $dt | Format-Table -autosize\r\n}\r\n\u00a0\r\n<\/pre>\n<p>The script follows the standard template I use for executing all of my SQL scripts against multiple servers. It uses a <b>foreach <\/b>loop to read through a list of servers, connect to the server and execute a query that returns the names of the user databases on each server. For this article, I have formatted the examples with comments in Green, PowerShell code in Blue and SQL in Red.<\/p>\n<h3>Reconciling Actual Database Inventory with Internal Database Inventory<\/h3>\n<p>On a monthly basis, I must reconcile my real database inventory with an internally-developed database inventory system that is used as a resource by other applications.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\"># inv.ps1\r\n# usage: .\/inv.ps1\r\n# Database inventory\r\nforeach ($svr in get-content \"C:\\data\\AllServers.txt\")\r\n{\r\n\u00a0 $con = \"server=$svr;database=master;Integrated Security=sspi\" \r\n\u00a0 $cmd = \"SELECT name FROM master..sysdatabases WHERE dbid &gt; 4 AND name NOT IN ('tracedb','UMRdb','Northwind','pubs','PerfAnalysis') ORDER BY name\"\r\n\u00a0 $da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)\r\n\u00a0 $dt = new-object System.Data.DataTable\r\n\u00a0 $da.fill($dt) | out-null\r\n\u00a0 $svr\r\n\u00a0 $dt | Format-Table -autosize\r\n}\r\n\u00a0\r\n<\/pre>\n<p>The query returns the database names for all non-Microsoft supplied databases, sorted by server, and I then compare this to a report generated by the database inventory system.<\/p>\n<h3>Remove BUILTIN\\Administrators Group from the Sysadmin Role<\/h3>\n<p>This script, instead of a <b>foreach<\/b> loop, defines a function that allows me to remove the <b>BUILTIN\\Admin<\/b> group from the sysadmin role on any server, simply by typing:<\/p>\n<pre>rmba ServerName\r\n<\/pre>\n<p>The function accepts one parameter, establishes the connection to the server and executes the <b>sp_dropsrvrolemember <\/b>system stored procedure.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\"># Remove BUILTIN\\Administrators from sysadmin role\r\nfunction rmba ($s)\r\n{\r\n\u00a0\u00a0 $svr=\"$s\"\r\n\u00a0\u00a0 $cn = new-object System.Data.SqlClient.SqlConnection \"server=$svr;database=master;Integrated Security=sspi\"\r\n\u00a0\u00a0 $cn.Open()\r\n\u00a0\u00a0 $sql = $cn.CreateCommand()\r\n\u00a0\u00a0 $svr\r\n\u00a0\u00a0 $sql.CommandText = \"EXEC master..sp_dropsrvrolemember @loginame = N'BUILTIN\\Administrators', @rolename = N'sysadmin';\"\r\n\u00a0\u00a0 $rdr = $sql.ExecuteNonQuery();\r\n}\r\n\u00a0\r\n<\/pre>\n<p>This script saves me time since I don&#8217;t have to jump into Management Studio to complete the task. In the SMO section you&#8217;ll find two other functions that I created to list the members of the sysadmin group and a server&#8217;s local administrators.<\/p>\n<h2>Windows Management Instrumentation (WMI) Tasks<\/h2>\n<p>My next task was to get a quick view of free space on all my servers. In order to do this, I had to dip my toes into the world of WMI, which provides an object model that exposes data about the services or applications running on your machines.<\/p>\n<p>The first obstacle here is figuring out what WMI has to offer. As soon as you start looking you&#8217;ll realize that the object model for WMI (and SMO) is vast and you&#8217;ll need to invest a little time in browsing through it and working out what does what.<\/p>\n<p>MSDN is the best place to go for documentation on the WMI classes.<\/p>\n<h3>Browsing the Win32 WMI Classes<\/h3>\n<p>The WMI classes that will be the most useful to a DBA are the Win32 classes\u00a0 and you can use the following one-liner to get the list of all the available Win32 classes:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\"># Browse Win32 WMI classes\r\nget-wmiobject -list | where {$_.name -like \"win32*\"} | Sort-Object\r\n<\/pre>\n<p>The classes I found interesting initially were:<\/p>\n<ul>\n<li><b>Win32_LogicalDisk<\/b> \u00e2 gives you stats on your disk drives<\/li>\n<li><b>Win32_QuickFixEngineering<\/b> \u00e2 enumerates all the patches that have been installed on a computer.<\/li>\n<\/ul>\n<p>The following examples will highlight the use of these and other classes of interest.<\/p>\n<h3>Checking Disk Space<\/h3>\n<p>The simplest way to check disk space is with the <b>Win32_LogicalDisk <\/b>class.\u00a0 DriveType=3 is all local disks.\u00a0 <b>Win32_LogicalDisk<\/b> will not display mountpoint information.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\"># Check disk space on local disks on local server\r\nGet-WmiObject -Class Win32_LogicalDisk -Filter \"DriveType=3\" \r\n\u00a0\r\n<\/pre>\n<p>&nbsp;<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\"># Check disk space on a remote server.\r\nGet-WmiObject -Class Win32_LogicalDisk -Filter \"DriveType=3\" -ComputerName ServerName\r\n<\/pre>\n<p>After a little digging around I found that, rather than try to calculate free space percentages from the output of the <b>Win32_LogicalDisk<\/b> class, it was easier to use the <b>Win32_PerfFormattedData_PerfDisk_LogicalDisk<\/b> class and its property <b>PercentFreeSpace<\/b>. In this example, I&#8217;m checking for drives with less than 20% free space.\u00a0 The other reason\u00a0 to use this class for checking disk space is that it will display mountpoint information.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\"># Checking\u00a0 disk space\r\nforeach ($svr in get-content \"C:\\AllServers.txt\") \r\n\u00a0\u00a0 { \r\n\u00a0\u00a0 \u00a0\u00a0 $svr; Get-WmiObject Win32_PerfFormattedData_PerfDisk_LogicalDisk -ComputerName $svr | where{$_.Name -ne \"_Total\" -and $_.PercentFreeSpace -lt 20} | select-object Name, PercentFreeSpace | format-list\r\n\u00a0\u00a0 }\r\n\r\n<\/pre>\n<p>&nbsp;<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\"># Local computer example\r\nGet-WmiObject -class Win32_PerfFormattedData_PerfOS_Processor -Property Name,PercentProcessorTime | where{$_.Name -eq \"_Total\"}\r\n<\/pre>\n<h3>Checking what Services are Running on a Server<\/h3>\n<p>A quick way to find out which services are running on a remote server is to use the <b>get-wmiobject<\/b>, specifying the class, <b>Win32_Service<\/b>, and then the properties you want to list, and finally the computer that you wish to query. In this example, I&#8217;m just retrieving the name of the service:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\"># Checking what services are running on a computer.\r\nget-wmiobject win32_service -computername COMPUTER | select name\r\n<\/pre>\n<h3>Is IIS Running on your SQL Server? Oh, No!<\/h3>\n<p>If you want to see if a specific service is running on a remote machine use the filter parameter, <b>-f<\/b>, and specify the name of the service.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\"># Is IIS running on your server?\r\nget-wmiobject win32_Service -computername COMPUTER -f \"name='IISADMIN'\"\r\ngwmi win32_Service -co COMPUTER -f \"name='IISADMIN'\"\r\n<\/pre>\n<p>The output will look as shown in Figure 2:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/576-image004.jpg\" alt=\"576-image004.jpg\" width=\"543\" height=\"205\" \/><br \/>\n<b>Figure 2: <\/b>IIS is running on my SQL Server. Panic!<\/p>\n<h2>SQL Server Management Objects (SMO) Tasks<\/h2>\n<p>SQL Server Management objects is a collection of objects that allows you to automate any task associated with managing Microsoft SQL Server.<\/p>\n<p>Again, the biggest obstacle for the DBA who is unfamiliar with object-oriented programming is working through the rather intimidating object model. Again, as with WMI, you need to know how to examine an object to determine its available properties and methods.<\/p>\n<p>In the SMO examples, you will again see the use of a <b>foreach<\/b> loop being used to execute SMO code against multiple servers.\u00a0 All of the examples begin by setting a reference to the SMO assembly. Once, you have established this reference, the script can then instantiate new objects derived from the classes in the assembly.<\/p>\n<h3>Browsing the SMO Classes<\/h3>\n<p>The SMO classes are documented in Books Online but it&#8217;s also helpful if you learn to list an object&#8217;s properties and methods.\u00a0 To browse the SMO classes, you need to set a reference to, and then use, the <b>get-member (gm) <\/b>cmdlet to display that object&#8217;s properties and methods.\u00a0<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">#\u00a0 To examine the SMO Server object in PowerShell:\r\n[reflection.assembly]::LoadWithPartialName(\"Microsoft.SqlServer.Smo\") | out-null\r\n$svr = new-object (\"Microsoft.SqlServer.Management.Smo.Server\") \"ServerName\"\r\n$svr | get-member\r\n<\/pre>\n<p>To examine different objects change the second and third lines of the above script accordingly.<\/p>\n<h3>List the members of the sysadmin role on a server<\/h3>\n<p>Depending on your previous experience, figuring out how the SMO object model works might prove tricky. I understood the basics of object-oriented programming but it didn&#8217;t really sink in until I was working on a script to list the sysadmin role members on my servers. Initially, I tried to use the code shown in Figure 3 and received the displayed error message:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\"># Before I understood the concept of objects completely, I tried... \r\n[reflection.assembly]::LoadWithPartialName(\"Microsoft.SqlServer.Smo\") | out-null\r\n$svr=\"ServerName\"\r\n$srv=New-Object \"Microsoft.SqlServer.Management.Smo.Server\" \"$svr\" \u00a0 \r\n$svrole = 'sysadmin'\r\n$svrole\r\n<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/576-image006.jpg\" alt=\"576-image006.jpg\" width=\"576\" height=\"115\" \/><br \/>\n<b>Figure 3:<\/b> Incorrect object reference<\/p>\n<p>I had a minor epiphany at this point. The concept of object-oriented programming and &#8220;Everything is an object&#8221; in PowerShell finally made sense to me. In the example in Figure 3, I had successfully created an instance of the Server object and, from there, wanted to work my way down to the ServerRole object for the sysadmin role. So, I set the variable, <b>$svrole<\/b>, to the string value &#8216;sysadmin&#8217;. \u00a0<\/p>\n<p>I then tried to invoke a method on that string object, thinking that I was, in effect, invoking a method on the ServerRole object. In this case, the <b>$svrole<\/b> variable only contained a string object not a reference to a ServerRole object. Thus, the error was thrown.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">Method invocation failed because [System.String] doesn't contain a method named 'EnumServerRoleMembers'.\r\nAt line:1 char:30\r\n+ $svrole.EnumServerRoleMembers( &lt;&lt;&lt;&lt; )\r\n<\/pre>\n<p>The following code sets the object reference correctly:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\u00a0\u00a0 $svrole = $srv.Roles | where {$_.Name -eq 'sysadmin'}\r\n<\/pre>\n<p>Then, the command, <b>$svrole.EnumServerRoleMembers()<\/b>, will enumerate the members of the sysadmin server role on the selected server, as shown in Figure 4:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/576-image008.jpg\" alt=\"576-image008.jpg\" width=\"526\" height=\"234\" \/><br \/>\n<b>Figure 4: <\/b>Enumerating the members of the sysadmin server role<\/p>\n<p>The following script wraps the PowerShell code needed to list the sysadmins on a server into a function.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\"># create sa function to list sysadmin members\r\n# usage: sa ServerName\r\n[reflection.assembly]::LoadWithPartialName(\"Microsoft.SqlServer.Smo\") | out-null\r\n\u00a0\r\nfunction sa ($s)\r\n{\r\n\u00a0\u00a0 $svr=\"$s\"\r\n\u00a0\u00a0 $srv=New-Object \"Microsoft.SqlServer.Management.Smo.Server\" \"$svr\" \r\n\u00a0\u00a0 $svrole = $srv.Roles | where {$_.Name -eq 'sysadmin'}\r\n\u00a0\u00a0 $svr\r\n\u00a0\u00a0 $svrole.EnumServerRoleMembers() \r\n}\r\n<\/pre>\n<h3>List the Local Administrators on a server<\/h3>\n<p>I use the following script (and the previous one) to keep to a minimum the number of people who have admin rights on a server and in SQL Server.<\/p>\n<p>This example was inspired by Microsoft MVP Ying Li&#8217;s post on his blog demonstrating how to list local admins on a server (see the Reference section at the end of this article for the link). The function is supplied a server name. It then connects to the specified server and lists the members of the local Administrators group.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\"># create ListAdmins function to list local Administrators on a server.\r\n# usage: ListAdmins ServerName\r\nfunction ListAdmins ($svr)\r\n{\r\n\u00a0\u00a0 $domain = [ADSI]\"\"\r\n\u00a0\u00a0 $strComputer = $svr\u00a0 \r\n\u00a0\u00a0 $computer = [ADSI](\"WinNT:\/\/\" + $strComputer + \",computer\")\r\n\u00a0\u00a0 $computer.name;\r\n\u00a0\u00a0 $Group = $computer.psbase.children.find(\"administrators\")\r\n\u00a0\u00a0 $Group.name\r\n\u00a0\u00a0 $members= $Group.psbase.invoke(\"Members\") | %{$_.GetType().InvokeMember(\"Name\", 'GetProperty', $null, $_, $null)}\r\n\u00a0\u00a0 $members\r\n}\r\n\u00a0\r\n<\/pre>\n<h3>Find a Login or AD group on Multiple Servers<\/h3>\n<p>One of my first SMO examples was inspired by my supervisor, who asked me to find out which database servers the data modeling group had access to.\u00a0 She was hoping it was only the development servers.<\/p>\n<p>The following example ends up being five to seven lines of code, depending how you format it, but it will find a login\/group on however many servers you have on your list.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\"># Find a login or AD group on multiple servers\r\n[reflection.assembly]::LoadWithPartialName(\"Microsoft.SqlServer.Smo\") | out-null\r\nforeach ($svr in get-content \"C:\\AllServers.txt\")\r\n\u00a0 { \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $srv=New-Object \"Microsoft.SqlServer.Management.Smo.Server\" \"$svr\" \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 trap {\"Oops! $_\"; continue } $srv.Logins | where {$_.Name -eq 'DOMAIN\\ITS_DATA_ADMIN'} | select Parent, Name \r\n\u00a0 }\r\n<\/pre>\n<p>The <b>trap<\/b> statement handles an error if one is encountered when the connection is made to a server. In this example, if there is an error connecting to the server, it will return the name of the server and the error message. \u00a0Occasionally, I will see &#8220;<b>Oops! Failed to connect to server SERVERNAME.<\/b>&#8221; in the output.<\/p>\n<h3>Check for failed SQL Agent jobs on multiple servers<\/h3>\n<p>Every morning I run the following script to check for any failed SQL Agent jobs on my servers:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\"># Check for failed SQL jobs on multiple servers\r\n[reflection.assembly]::LoadWithPartialName(\"Microsoft.SqlServer.Smo\") | out-null\r\nforeach ($svr in get-content \"C:\\AllServers.txt\")\r\n{\r\n\u00a0\u00a0 write-host $svr\r\n\u00a0\u00a0 $srv=New-Object \"Microsoft.SqlServer.Management.Smo.Server\" \"$svr\"\r\n\u00a0\u00a0 $srv.jobserver.jobs | where-object {$_.lastrunoutcome -eq \"Failed\" -and $_.isenabled -eq $TRUE} | format-table name,lastrunoutcome,lastrundate -autosize\r\n}\r\n\u00a0\r\n<\/pre>\n<h2>Miscellaneous Tasks<\/h2>\n<p>Pure PowerShell examples follow from here on out to answer a few more questions a DBA might have.<\/p>\n<h3>Checking for installed Hot Fixes<\/h3>\n<pre class=\"theme:ssms2012 lang:tsql\"># List all installed hotfixes on a server\r\nget-wmiobject Win32_QuickFixEngineering\u00a0 \r\n\u00a0\r\n# Check if a specific hotfix is installed on a server\r\nget-wmiobject Win32_QuickFixEngineering | findstr KB928388\u00a0 \r\n<\/pre>\n<h3>Finding a Port Number<\/h3>\n<p>I&#8217;m often asked by a developer for the port number of a named instance. By combining in a short pipeline two cmdlets, <b>get-content<\/b> and <b>select-string<\/b>, you have a one-liner that find a port number in the errorlog text. This is much quicker than searching the errorlog manually, or running a bit of SQL code to return this result.<\/p>\n<p>I did try to use <b>select-string <\/b>by itself to search the errorlog but, for some reason, <b>select-string<\/b> can&#8217;t read the active errorlog unless combined with <b>get-content<\/b>. In the following example, I search for the word &#8220;listening&#8221; in a SQL Server 2005 errorlog:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\"># Find a port number\r\ngc \\\\ServerName\\ShareName\\MSSQL2005\\MSSQL.2\\MSSQL\\LOG\\ERRORLOG | select-string \"listening\"\r\n<\/pre>\n<p>The result is shown in Figure 5:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/576-image010.jpg\" alt=\"576-image010.jpg\" width=\"576\" height=\"120\" \/><br \/>\n<b>Figure 5<\/b>: Searching a SQL 2005 errorlog<\/p>\n<p>Keep in mind that if you have cycled the error log on your server the line you need to find might not be in the current error log and you will need to adjust the command below to search through your archived errorlogs by appending .1, .2, .3, etc. to ERRORLOG.<\/p>\n<p>If you are searching an errorlog on a named instance of SQL Server 2000, you will need to escape the <b>$<\/b> in the path with a backtick, as follows:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">get-content \\\\ServerName\\ShareName\\MSSQL2000\\MSSQL`$SQL100\\LOG\\ERRORLOG | select-string \"listening\"\r\n<\/pre>\n<h3>Generate a Random Password<\/h3>\n<p>If you need to generate a random password for a SQL login, use the following .NET class:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\"># generate a random password\r\n[Reflection.Assembly]::LoadWithPartialName(\"System.Web\" ) | out-null\r\n[System.Web.Security.Membership]::GeneratePassword(10,2) # 10 bytes long\r\n[System.Web.Security.Membership]::GeneratePassword(8,2)\u00a0 #\u00a0 8 bytes long\r\n\r\n<\/pre>\n<h3>Checking that Database Backups are Current on Multiple Servers<\/h3>\n<p>In my environment, I have two database configurations and the backups don&#8217;t always land in a standard location. As such, I use a &#8216;brute force&#8217; solution for checking my backups.<\/p>\n<p>I have developed a script that checks that the backups are current for each of the servers that I support. The basic template is as follows:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\"># Checking backups are current\r\nwrite-host ''\r\nwrite-host 'ServerName'\r\nget-childitem \\\\ServerName\\ShareName\\dump_data\\*.bak | where-object { $_.LastWriteTime -ge (Get-Date).AddDays(-1) } | Select name, LastWriteTime\r\n<\/pre>\n<p>In the script, I repeat the above statement block for each server I need to check. Example output is shown in Figure 6:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/576-image012.jpg\" alt=\"576-image012.jpg\" width=\"575\" height=\"202\" \/><br \/>\n<b>Figure 6: <\/b>Checking that Backups are up-to-date<\/p>\n<p>If the server has multiple drives to be checked, I repeat the <b>get-childitem<\/b> cmdlet for the additional drives.<\/p>\n<p>Here&#8217;s a slice of my full <b>ChkBkups.ps1<\/b> script.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\"># checking three dump locations on a default instance.\r\nwrite-host ''\r\nwrite-host 'Server1'\r\nget-childitem \\\\Server1\\e$\\dump_data\\ServerName\\*.bak | where-object { $_.LastWriteTime -ge (Get-Date).AddDays(-1) } | Select name, LastWriteTime\r\nget-childitem \\\\Server1\\g$\\dump_data\\ServerName\\*.bak | where-object { $_.LastWriteTime -ge (Get-Date).AddDays(-1) } | Select name, LastWriteTime\r\nget-childitem \\\\Server1\\i$\\dump_data\\ ServerName \\*.bak | where-object { $_.LastWriteTime -ge (Get-Date).AddDays(-1) } | Select name, LastWriteTime\r\n\u00a0\r\n# checking one dump location on a named instance.\r\nwrite-host ''\r\nwrite-host ' Server2'\r\nget-childitem \\\\Server2\\ShareName\\dump_data\\ServerName\\Instance\\db_dump\\*.bak | where-object { $_.LastWriteTime -ge (Get-Date).AddDays(-1) } | Select name, LastWriteTime\r\n<\/pre>\n<p>I run this script each morning. We do have a set of automated routines that run nightly that handle the standard DBA tasks like backups, consistency checks, index maintenance, etc. Each server&#8217;s maintenance process sends e-mails reporting on their status. This script saves me from having to read multiple e-mails.<\/p>\n<h2>Summary<\/h2>\n<p>I think using PowerShell will make me a better DBA because I have the means to automate mundane tasks, gather information quicker regarding my servers and manage my workload better. I&#8217;ve also found that using it has stretched my knowledge into areas I might not normally go, which can only be a good thing.<\/p>\n<p>It is really striking to me how a few lines of PowerShell can do so much.\u00a0<\/p>\n<p>In my opinion, time spent learning PowerShell is time well spent.<\/p>\n<h2>References<\/h2>\n<p>I started with Bruce Payette&#8217;s book and the &#8220;Getting Started Guide&#8221;.\u00a0 I found a lot of good examples via Google by using simple search terms like: powershell sql; powershell smo; powershell wmi; powershell adsi; powershell blogs.<\/p>\n<p>Bruce Payette&#8217;s book laid the foundation but all the people who have contributed to the newsgroup, blogs, and articles filled in the gaps. The section below is a just a subset of the materials I&#8217;ve used to learn PowerShell.<\/p>\n<p><b>ADO.Net<\/b><\/p>\n<ul>\n<li>Books24x7.com &#8211; subscription required&#8221;&gt; <a href=\"http:\/\/www.books24x7.com\/toc.asp?bkid=13569\">Microsoft ADO.NET 2.0 Step by Step by Rebecca M. Riordan<\/a><\/li>\n<\/ul>\n<p><b>SMO<\/b><\/p>\n<ul>\n<li>SQL Server Books Online:<br \/>\nhttp:\/\/msdn.microsoft.com\/en-us\/library\/ms162169.aspx<\/li>\n<li>Blogs:<br \/>\n<a href=\"http:\/\/blogs.msdn.com\/mwories\/archive\/tags\/SMO+Samples\/default.aspx\">http:\/\/blogs.msdn.com\/mwories\/archive\/tags\/SMO+Samples\/default.aspx<\/a><\/li>\n<\/ul>\n<p><b>WMI<\/b><\/p>\n<ul>\n<li><a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/aa394582.aspx\">http:\/\/msdn.microsoft.com\/en-us\/library\/aa394582.aspx<\/a><\/li>\n<li><a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/aa394572(VS.85).aspx\">http:\/\/msdn.microsoft.com\/en-us\/library\/aa394572(VS.85).aspx<\/a><\/li>\n<\/ul>\n<p><b>PowerShell <\/b><\/p>\n<ul>\n<li>&#8220;Getting Started Guide&#8221; <br \/>\n<a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/aa973757(VS.85).aspx\">http:\/\/msdn.microsoft.com\/en-us\/library\/aa973757(VS.85).aspx<\/a><\/li>\n<\/ul>\n<ul>\n<li>Books.\n<ul>\n<li>&#8220;Windows PowerShell In Action&#8221; by Bruce Payette<\/li>\n<li>Windows PowerShell: TFM&#8221; by Don Jones and Jeffery Hicks<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul>\n<li>Newsgroups\n<ul>\n<li>microsoft.public.windows.powershell<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul>\n<li>Blogs and articles:\n<ul>\n<li><a href=\"http:\/\/www.mssqlengineering.com\/\">http:\/\/www.mssqlengineering.com\/<\/a><\/li>\n<li><a href=\"http:\/\/www.simple-talk.com\/sql\/database-administration\/managing-sql-server-using-powersmo\/\">http:\/\/www.simple-talk.com\/sql\/database-administration\/managing-sql-server-using-powersmo\/<\/a><\/li>\n<li><a href=\"http:\/\/sqlblog.com\/blogs\/allen_white\/archive\/2008\/01\/25\/using-powershell-and-sql-server-together.aspx\">http:\/\/sqlblog.com\/blogs\/allen_white\/archive\/2008\/01\/25\/using-powershell-and-sql-server-together.aspx<\/a><\/li>\n<li><a href=\"http:\/\/blogs.msdn.com\/mwories\/archive\/2008\/06\/14\/SQL2008_5F00_PowerShell.aspx\">http:\/\/blogs.msdn.com\/mwories\/archive\/2008\/06\/14\/SQL2008_5F00_PowerShell.aspx<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>A SQL Server DBA&#8217;s practical introduction to PowerShell &#8211; managing multiple servers, automating SQL tasks (version checks, inventory reconciliation, sysadmin audits), WMI tasks (disk space, services), and SMO tasks (login audits, job monitoring, backup verification). Complete scripts for every example.&hellip;<\/p>\n","protected":false},"author":221826,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527],"tags":[4143,4170,4635,4150,4151],"coauthors":[50394],"class_list":["post-446","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","tag-net","tag-database-administration","tag-powershell","tag-sql","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/446","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\/221826"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=446"}],"version-history":[{"count":8,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/446\/revisions"}],"predecessor-version":[{"id":74699,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/446\/revisions\/74699"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=446"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=446"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=446"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=446"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}