{"id":761,"date":"2010-01-21T00:00:00","date_gmt":"2009-12-23T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/exceptional-powershell-dba-pt-2-the-morning-checklist\/"},"modified":"2021-08-24T13:40:32","modified_gmt":"2021-08-24T13:40:32","slug":"exceptional-powershell-dba-pt-2-the-morning-checklist","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/exceptional-powershell-dba-pt-2-the-morning-checklist\/","title":{"rendered":"Exceptional PowerShell DBA Pt 2 – The Morning Checklist"},"content":{"rendered":"
\n

I confess that when I started working with PowerShell, I envisioned functions as behaving the same way as Visual Basic or C#, i.e. everything being done within the single function. Thus it was big and full of conditions. Yet one of the great features of PowerShell is consistency; Everything in PowerShell is an object, and the output from one cmdlet can be easily “piped” into the input of another. This means your functions are generic, and over just two or three lines of code you can filter, apply summations, dictate output format, manage inserts into a SQL Server table etc…<\/p>\n

As a result, PowerShell is an incredibly versatile scripting language which makes proactive database administration potentially pain-free. In my previous article, I demonstrated how to deal with orphaned users using PowerShell and SMO. This time around, I’ll take you through some more of my daily checklists and management tasks which have been ‘PowerShelled’ to make my life infinitely easier! Bear in mind that, as with my last article, my scripts require PowerShell 2.0, and all I’ll be giving you today are my scripts (which you can download from the top of this article) and examples on how to use them. I think they’re incredibly useful, and if you have any questions about any of them, feel free to leave a comment. So, without further ado:<\/p>\n

Recording log information<\/h1>\n

Let’s start by talking a little more about Save-MSSQLMsg. It is one of most important functions here, because it will generate a log of all messages, be they error messages or simply information. As a result, I will always use this function in conjunction with others to ensure I know exactly what is going on in my servers. It takes 6 parameters, and only the first two (file and server names) are mandatory. The other 4 are not informed of the default values, as they are used by the script to insert information into the log files.<\/p>\n

\r\nFunction Save-MSSQLMsg () \r\n{\r\n    #requires -Version 2\r\n    \r\n    <#\r\n        .SYNOPSIS\r\n        Save  log in file\r\n        .PARAMETER NamePS1\r\n        File Name \r\n        .PARAMETER Server\r\n        Server name\r\n        .PARAMETER databasename \r\n        DatabaseName\r\n        .PARAMETER Message default \u001c\u001d\r\n        Message To Log\r\n        .PARAMETER PathFileLog default \u001c\u001d\r\n        Path to generate file \r\n        Default = C:\\temp\r\n        .PARAMETER TodayDate\r\n        Date to Log default getdate\r\n    #>\r\n\r\n\r\n\r\n    [CmdletBinding()]\r\n    \r\n    Param (\r\n    [Parameter(position=1,Mandatory = $true )][String] $NamePS1,\r\n    [Parameter(position=2,Mandatory = $true )][String] $Server,\r\n    [Parameter(position=3,Mandatory = $false )][String] $DatabaseName = \",\r\n    [Parameter(position=4,Mandatory = $false )][String] $Message = \" ,\r\n    [Parameter(position=5,Mandatory = $false )][String] $PathFileLog = \r\n                                                              \"C:\\temp\",\r\n    [Parameter(position=6,Mandatory = $false )][String] $TodayDate = \r\n                                        (Get-Date -Format \"yyyyMMddhhmmss\")\r\n    )\r\n    process \r\n    {\r\n    \r\n        #Test if path wich will contains the error file exists. If not,\r\n        # create \r\n        \r\n        if (!(Test-Path -path $PathFileLog))\r\n        {\r\n            try {\r\n                New-Item $PathFileLog -itemtype directory -ErrorAction  \r\n                Stop  | Out-Null\r\n            }\r\n            catch {\r\n                Write-Host \"Can not create log file path\"\r\n                break;\r\n            }\r\n        } \r\n    \r\n    \r\n        $NameFileFull = Join-Path $PathFileLog \"$NamePS1$TodayDate.log\" \r\n        \r\n        $TDate = $TodayDate.Substring(0,4) + \"-\" + $TodayDate.Substring(4,2) \r\n                 + \"-\"     + $TodayDate.Substring(6,2) \r\n        \r\n        \"Server : \" + $Server + \" Database : \" + $DatabaseName + \" Date : \"  \r\n        + $TDate + \" Message: \"  + $Message | Out-file  $NameFileFull \r\n        -append \r\n    } \r\n}<\/pre>\n
So, for generating an error message or simply information, we use :<\/p>\n
Save-MSSQLMsg \"ScriptName1\" \"Server1\" \"Database1\" \"MessageToDisplay1\" \"C:\\TEMP\"<\/pre>\n
\"900-logfile.jpg\"<\/p>\n
But if we want only one file to be generated, we must constantly pass the same script name and date to the function, i.e., we need to set this in the parameters at the beginning of the function, and then we can use the function like this:<\/p>\n
\r\n$ScriptName = \"ScriptName1\" $TodaydateMSG = Get-Date -Format \"yyyyMMddhhmmss\"\r\n\r\nSave-MSSQLMsg $ScriptName \"Server1\" \"Database1\" \"MessageToDisplay1\" \"C:\\TEMP\" $TodaydateMSG\r\nSave-MSSQLMsg $ScriptName \"Server2\" \"Database2\" \"MessageToDisplay2\" \"C:\\TEMP\" $TodaydateMSG\r\nSave-MSSQLMsg $ScriptName \"Server3\" \"Database3\" \"MessageToDisplay3\" \"C:\\TEMP\" $TodaydateMSG<\/pre>\n
\"900-logfile2.jpg\"<\/p>\n
Returning Backup Information<\/h1>\n
“Laerte, I need all backup information about backups in our databases, and if we had some of them without backup”<\/em><\/p>\n
“Yes boss. Right away. How do you want that information? Email ? Snail Mail ? Smoke Signal ? Drums ?”<\/em><\/p>\n
Not a problem; You’re an Exceptional DBA who has everything ready for your checks, and PowerShell under your command. As the heading suggests, this function returns information about your database backups. The properties return by the object are :<\/p>\n\n\n\n\n\n\n\n\n\n
\n
LineNumber<\/p>\n<\/td>\n
\n
(Int)<\/p>\n<\/td>\n<\/tr>\n
\n
Date<\/p>\n<\/td>\n
\n
(DateTime)<\/p>\n<\/td>\n<\/tr>\n
\n
ServerName<\/p>\n<\/td>\n
\n
(string)<\/p>\n<\/td>\n<\/tr>\n
\n
DatabaseName<\/p>\n<\/td>\n
\n
(string)<\/p>\n<\/td>\n<\/tr>\n
\n
LastBackupDate<\/p>\n<\/td>\n
\n
(DateTime)<\/p>\n<\/td>\n<\/tr>\n
\n
LastDifferentialBackupDate<\/p>\n<\/td>\n
\n
(DateTime)<\/p>\n<\/td>\n<\/tr>\n
\n
LastLogBackupDate<\/p>\n<\/td>\n
\n
(DateTime)<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Bear in mind that you can control which servers this script checks by supplying a list of targets in a flat text file – C:\\TEMP\\Servers.txt. If you don’t supply this file, the script will only check the default server. Take a look at the script below, and I’ll give some examples of how to use it after the jump><\/p>\n
\r\nFunction Get-MSSQLBackup () \r\n{\r\n#requires -Version 2\r\n\r\n<#\r\n    .SYNOPSIS\r\n    Returns information about backups\r\n    .DESCRIPTION\r\n    Write to Application LocalComputer Eventlog\r\n    .PARAMETER TxtServerList\r\n    List with servers, if not pass default server is used\r\n    .LINK\r\n    www.laertejuniordba.spaces.live.com\r\n\r\n#>\r\n\r\n\r\n[CmdletBinding()]\r\n\r\nPARAM    (\r\n            [Parameter(position=1,Mandatory=$false, \r\n            ValueFromPipelineByPropertyName=$true,HelpMessage=\"File Servers \r\n                                                               List\")]\r\n            [Alias(\"FullNameTXT\")]\r\n            [String] $TXTServersList = $env:COMPUTERNAME\r\n            \r\n        )\r\n\r\nbegin\r\n{\r\n    [reflection.assembly]::LoadWithPartialName(\"Microsoft.SqlServer.Smo\") \r\n    | out-null\r\n}\r\nProcess\r\n{\r\n\r\n    $verbosePreference=\"continue\"\r\n    [datetime] $TodayDate = get-date -Format \"yyyy\/MM\/dd hh:mm:ss\"\r\n    if ($TXTServersList.substring($TXTServersList.length -4,4) -eq \".TXT\")\r\n    {\r\n        try\r\n        {\r\n            $ServersList = get-content $TXTServersList    \r\n        } catch {\r\n                    $msg = $error[0]\r\n                    Write-Warning $msg\r\n                    break;\r\n        }\r\n    }    \r\n    else\r\n    {\r\n        $ServersList = $TXTServersList\r\n    }    \r\n    \r\n    \r\n    $LineNumber = 1\r\n    $FinalResult = @()\r\n\r\n    foreach ($svr in  $ServersList )\r\n    {\r\n        try \r\n        {\r\n        $DatabaseName = \"\r\n        $Server=New-Object \"Microsoft.SqlServer.Management.Smo.Server\" \r\n                           \"$svr\"\r\n        $Data = $Server.Databases| where-object {$_.IsAccessible } | \r\n                  foreach {\r\n    \r\n        [datetime] $LastBackupDate            = \"{0:yyyy\/MM\/dd hh:mm:ss}\" \r\n        -f [datetime] $_.LastBackupDate\r\n        [datetime]$LastDifferentialBackupDate = \"{0:yyyy\/MM\/dd hh:mm:ss}\" \r\n        -f [datetime] $_.LastDifferentialBackupDate  \r\n        [datetime]$LastLogBackupDate          = \"{0:yyyy\/MM\/dd hh:mm:ss}\" \r\n        -f [datetime] $_.LastLogBackupDate\r\n               [String] $DatabaseName = $_.name\r\n\r\n                $Object = New-Object PSObject -Property @{\r\n                   LineNumber                  = $LineNumber    \r\n                   Date                        = $TodayDate    \r\n                   ServerName                  = $svr\r\n                   DatabaseName                = $DatabaseName             \r\n                   LastBackupDate              = $LastBackupDate\r\n                   LastDifferentialBackupDate  = $LastDifferentialBackupDate\r\n                   LastLogBackupDate           = $LastLogBackupDate\r\n                                                          }\r\n\r\n                $FinalResult += $Object\r\n                $LineNumber ++\r\n                \r\n            }               \r\n        \r\n        }   catch    {\r\n              $msg = $error[0]\r\n              Write-Warning $msg\r\n              Save-MSSQLMsg \"Get-MSSQLBackup\" \"$svr\" \"$DatabaseName\" \"$msg\"\r\n              continue\r\n        }\r\n    }    \r\n    \r\n    Write-Output $FinalResult                \r\n}\r\n}<\/pre>\n
\n        
So, now that you’ve seen the function, here are some examples on how to use it :<\/p>\n
    \n
  • List all backup information on the default server:<\/li>\n<\/ul>\n
    Get-MSSQLBackup <\/pre>\n
      \n
    • List all backup information on target server[s] (supplied in the text file):<\/li>\n<\/ul>\n
      Get-MSSQLBackup \"C:\\TEMP\\Servers.txt\"<\/pre>\n
        \n
      • List all databases which haven’t been backed up for one day:<\/li>\n<\/ul>\n
        \r\nGet-MSSQLBackup \"C:\\TEMP\\servers1.txt\" | where-Object {( (get-Date) \r\n- ($_.LastBackupDate)).days -gt 1}  | Select LineNumber,Date,ServerName,\r\nDatabaseName,LastBackupDate,LastDifferentialBackupDate,LastLogBackupDate\r\n | Format-Table<\/pre>\n
          \n
        • List backup information for all databases, and store it in a SQL Server table:<\/li>\n<\/ul>\n
          \r\ncreate table BackupsXML (XMLCOL XML)\r\n(Get-MSSQLBackup \"C:\\TEMP\\servers.txt\" | Select LineNumber,Date,ServerName,\r\nDatabaseName,LastBackupDate,LastDifferentialBackupDate,LastLogBackupDate \r\n| ConvertTo-Xml -NoTypeInformation).save(\"c:\\temp\\Backups.xml\") \r\n$XML = (Get-Content \"C:\\Temp\\Backups.xml\" ) -replace \"'\", \"''\"\r\n$SQL = \"insert into BackupsXML (XMLCol) values ('$XML')\" \r\ninvoke-sqlcmd -ServerInstance $env:COMPUTERNAME -database \"tempdb\" \r\n-query $sql<\/pre>\n
            \n
          • Generate TSQL to list all data from the “BackupsXML” table, where the ServerName condition = “Jupiter”:<\/li>\n<\/ul>\n
            \r\nSelect t2.Col1.value('(Property[@Name=\"LineNumber\"]\/text())[1]', 'int') \r\n                    LineNumber,\r\n    t2.Col1.value('(Property[@Name=\"Date\"]\/text())[1]', 'nvarchar(255)') \r\n                 Date,\r\n    t2.Col1.value('(Property[@Name=\"ServerName\"]\/text())[1]', \r\n                 'nvarchar(max)') ServerName,\r\n    t2.Col1.value('(Property[@Name=\"DatabaseName\"]\/text())[1]', \r\n                 'nvarchar(255)') DatabaseName,\r\n    t2.Col1.value('(Property[@Name=\"LastBackupDate\"]\/text())[1]', \r\n                 'nvarchar(255)') LastBackupDate\r\nFROM dbo.BackupsXML\r\nCROSS APPLY xmlcol.nodes('\/Objects\/Object') As t2(Col1)\r\nwhere t2.Col1.value('(Property[@Name=\"ServerName\"]\/text())[1]', \r\n                   'nvarchar(max)') = 'Jupiter'<\/pre>\n
            Now that we’ve got this script working, let’s generate a CSV file and email it to our beloved leader:<\/p>\n
            \r\nGet-MSSQLBackup \"C:\\TEMP\\Servers.txt\" | Select Date,ServerName,DatabaseName,LastBackupDate | Export-Csv -Path \"C:\\temp\\Backups.csv\"\r\n-NoTypeInformation ââ¬\"Force\r\nSend-MailMessage -To \"YourBelovedBoss@contoso.com\" \r\n-Subject \"Database Without Backups My Beloved Boss\" -from \"ExceptionalPowershellDBA@contoso.com\" \r\n-Attachments \"C:\\TEMP\\backups.csv\" \r\n-SmtpServer \"10.0.0.10\"<\/pre>\n
            \"900-backupCSV.gif\"<\/p>\n
            Returns Linked Servers Information and Connectivity<\/h1>\n
            “Laerteeeee (yes..he loves my name) , Some of our remote procedures are returning errors. What is happening?”<\/em><\/p>\n
            “I will check the linked servers again, my lord”<\/em><\/p>\n
            I know..I know.. PowerShell is my middle name.<\/p>\n
            This function returns an object with information about all your linked servers and your connectivity. As with the others functions, you can pass a file to the script with all the servers you want to test, or else leave it empty if you want the current server. Likewise, this script will also generate a log file if some server is off or another problem occurs.The properties returned are :<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
            \n
            LineNumber<\/p>\n<\/td>\n
            		\n
            (int)<\/p>\n<\/td>\n<\/tr>\n
            \n
            Date<\/p>\n<\/td>\n
            		\n
            (datetime)<\/p>\n<\/td>\n<\/tr>\n
            \n
            ServerName<\/p>\n<\/td>\n
            		\n
            (string)<\/p>\n<\/td>\n<\/tr>\n
            \n
            LinkedServerName<\/p>\n<\/td>\n
            		\n
            (string)<\/p>\n<\/td>\n<\/tr>\n
            \n
            DataSource<\/p>\n<\/td>\n
            		\n
            (string)<\/p>\n<\/td>\n<\/tr>\n
            \n
            DateLastModified<\/p>\n<\/td>\n
            		\n
            (datetime)<\/p>\n<\/td>\n<\/tr>\n
            \n
            CollationCompatible<\/p>\n<\/td>\n
            		\n
            (boolean)<\/p>\n<\/td>\n<\/tr>\n
            \n
            DataAccess<\/p>\n<\/td>\n
            		\n
            (boolean)<\/p>\n<\/td>\n<\/tr>\n
            \n
            RPC<\/p>\n<\/td>\n
            		\n
            (boolean)<\/p>\n<\/td>\n<\/tr>\n
            \n
            RpcOut<\/p>\n<\/td>\n
            		\n
            (boolean)<\/p>\n<\/td>\n<\/tr>\n
            \n
            UseRemoteCollation<\/p>\n<\/td>\n
            		\n
            (boolean)<\/p>\n<\/td>\n<\/tr>\n
            \n
            CollationName<\/p>\n<\/td>\n
            		\n
            (string)<\/p>\n<\/td>\n<\/tr>\n
            \n
            ConnectionTimeOut<\/p>\n<\/td>\n
            		\n
            (int)<\/p>\n<\/td>\n<\/tr>\n
            \n
            QueryTimeOut<\/p>\n<\/td>\n
            		\n
            (int)<\/p>\n<\/td>\n<\/tr>\n
            \n
            Distributor<\/p>\n<\/td>\n
            		\n
            (boolean)<\/p>\n<\/td>\n<\/tr>\n
            \n
            Publisher<\/p>\n<\/td>\n
            		\n
            (boolean)<\/p>\n<\/td>\n<\/tr>\n
            \n
            Subscriber<\/p>\n<\/td>\n
            		\n
            (boolean)<\/p>\n<\/td>\n<\/tr>\n
            \n
            LazySchemaValidation<\/p>\n<\/td>\n
            		\n
            (boolean)<\/p>\n<\/td>\n<\/tr>\n
            \n
            EnablePromotionofDistributedTransactionsForRPC<\/p>\n<\/td>\n
            		\n
            (boolean)<\/p>\n<\/td>\n<\/tr>\n
            \n
            ProviderName<\/p>\n<\/td>\n
            		\n
            (string)<\/p>\n<\/td>\n<\/tr>\n
            \n
            ProductName<\/p>\n<\/td>\n
            		\n
            (string)<\/p>\n<\/td>\n<\/tr>\n
            \n
            Connectivity<\/p>\n<\/td>\n
            		\n
            (boolean)<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
            \r\nFunction Get-MSSQLLinkedServer()\r\n<#\r\n#requires -Version 2\r\n\r\n<#\r\n    .SYNOPSIS\r\n    Returns information about linkedservers. Also test connectivity too.\r\n    .DESCRIPTION\r\n    Returns information about linkedservers. Also test connectivity too.\r\n    .PARAMETER TxtServerList\r\n    List with servers, if not pass default server is used \r\n    .LINK\r\n    www.laertejuniordba.spaces.live.com\r\n\r\n#>\r\n\r\n\r\n#>\r\n{\r\n[CmdletBinding()]\r\n\r\nPARAM    (\r\n            [Parameter(position=1,Mandatory=$false, \r\n               ValueFromPipelineByPropertyName=$true,\r\n               HelpMessage=\"File Servers List\")]\r\n            [Alias(\"FullNameTXT\")]\r\n            [String] $TXTServersList = $env:COMPUTERNAME\r\n            \r\n        )\r\n\r\nbegin \r\n{\r\n    [reflection.assembly]::LoadWithPartialName(\"Microsoft.SqlServer.Smo\") \r\n     | out-null \r\n}\r\nProcess\r\n{\r\n\r\n    $verbosePreference=\"continue\" \r\n    [datetime] $TodayDate = get-date -Format \"yyyy\/MM\/dd hh:mm:ss\"\r\n    if ($TXTServersList.substring($TXTServersList.length -4,4) -eq \".TXT\")\r\n    {\r\n        try\r\n        {\r\n            $ServersList = get-content $TXTServersList    \r\n        } catch {\r\n                    $msg = $error[0]\r\n                    Write-Warning $msg\r\n                    break;\r\n        }\r\n    }    \r\n    else\r\n    {\r\n        $ServersList = $TXTServersList\r\n    }    \r\n    \r\n    \r\n    $LineNumber = 1\r\n    $FinalResult = @()\r\n\r\n    foreach ($svr in  $ServersList )\r\n    {\r\n        try \r\n        {\r\n            $Server=New-Object \"Microsoft.SqlServer.Management.Smo.Server\" \r\n                               \"$svr\"\r\n            $Data = $Server.linkedservers| where-object {$_.State \r\n                                                -eq \"Existing\"} | foreach {\r\n            \r\n                \r\n                \r\n                [datetime]$DateLastModified = \"{0:yyyy-MM-dd hh:mm:ss}\"\r\n                                         -f [datetime] $_.DateLastModified  \r\n                \r\n                try\r\n                {\r\n                    $_.testconnection() \r\n                    $Connectivity = $true\r\n                } catch {\r\n                    $Connectivity = $false\r\n                }\r\n\r\n                \r\n                $Object = New-Object PSObject -Property @{\r\n\r\nLineNumber                        = $LineNumber     \r\nDate                                  = $TodayDate     \r\nServerName                        = $svr\r\nLinkedServerName                  = $_.Name\r\nDataSource                        = $_.DataSource             \r\nDateLastModified                      = $DateLastModified\r\nCollationCompatible               = $_.CollationCompatible\r\nDataAccess                          = $_.DataAccess\r\nRPC                                 = $_.RPC\r\nRpcOut                              = $_.RPCOut\r\nUseRemoteCollation                  = $_.UseRemoteCollation\r\nCollationName                       = $_.CollationName        \r\nConnectionTimeOut                   = $_.ConnectTimeOut\r\nQueryTimeOut                        = $_.QueryTimeOut        \r\nDistributor                         = $_.Distributor            \r\nPublisher                           = $_.Publisher            \r\nSubscriber                          = $_.Subscriber            \r\nLazySchemaValidation                = $_.LazySchemaValidation\r\nEnablePromotionofDistributedTransactionsForRPC       \r\n                       = $_.IsPromotionofDistributedTransactionsForRPCEnable\r\nProviderName                        = $_.ProviderName            \r\nProductName                         = $_.ProductName\r\nConnectivity                        =  $Connectivity     }\r\n                \r\n                $FinalResult += $Object\r\n                $LineNumber ++ \r\n            } \r\n\r\n        \r\n        }    catch {        \r\n                    $msg = $error[0]\r\n                    Write-Warning $msg\r\n                    Save-MSSQLMsg \"Get-MSSQLLinkedServer\" \"$svr\" \" \"$msg\" \r\n                    continue\r\n        } \r\n        \r\n    }\r\n    \r\n    Write-Output $FinalResult\r\n}    \r\n\r\n}<\/pre>\n
            … And just as before, some examples on how to use it:<\/p>\n
              \n
            • List All the LinkedServers information for the default Server:<\/li>\n<\/ul>\n
              Get-SQLLinkedServer <\/pre>\n
                \n
              • >List All the LinkedServers information for All target servers:<\/li>\n<\/ul>\n
                Get-SQLLinkedServer \"c:\\temp\\servers.txt\" <\/pre>\n
                  \n
                • List All the LinkedServers information for All target servers, and filter the data down to just the name and connectivity test data:<\/li>\n<\/ul>\n
                  Get-SQLLinkedServer \"c:\\temp\\servers.txt\" | select LineNumber,Date,ServerName,LinkedServerName,Connectivity |Format-Table<\/pre>\n
                    \n
                  • List All the LinkedServers information for All target servers, and filter the data down to just the name and connectivity test data for results where Connectivity = false):<\/li>\n<\/ul>\n
                    Get-SQLLinkedServer \"c:\\temp\\servers.txt\" | where-object {!$_.Connectivity}\r\n| select LineNumber,Date,ServerName,LinkedServerName,Connectivity \r\n|Format-Table<\/pre>\n