SQL Server – PowerShell Script – Getting Properties and Details

I write and play around a lot with SQL Server Management Studio and I love working with them. Having said that, I also explore the ways people want to run code to achieve certain objectives. In the DBA’s world when the number of servers we are talking is not a single digit, they are looking for ways to automate and script out. Recently I was at a retail company backend team in India and they said they have close to 1500+ databases which are running at their various outlets and point of sale counters and the DBA team working to manage these were still less than 10. I was pleasantly surprised by this level of details. In this blog post we will learn about Powershell Script.

Either the people managing the environment are too good or the product SQL Server is very good. I tend to understand it must be both. Coming back to this blog post, this was inspired by one of them. They told me they run patch management, etc and use powershell scripts to find if the databases are at the latest.

I am not going to say much about the work they did, I went about exploring some options with Powershell and in that pursuit I figured out that Powershell can give you more information than you guess. Below is a simple script that gives you information from SQL Server properties that are of value. Some of the nice information that can be got is around default backup location, Collation, Errorlogpath, Edition of server, databases involved, Case sensitive, logins, memory, logical processors etc. This is exhaustive.

Import-Module SQLPS -DisableNameChecking
#Run below command in sequence in powershell under Admin mode to get the detail information about the instance. Replace localhost with servername.
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList localhost $server | Get-Member | Where {$_.MemberType -eq "Property" -and $_.Name -ne "SystemMessages"} | select Name, @{Name="Value";Expression={[string]$server.($_.Name)}} 

A typical output for the same would look like:

SQL Server - PowerShell Script - Getting Properties and Details server-details-ps-01-800x296

As I wrap up this blog, please let me know if you have some interesting script usecases you want to share? I would be more than happy to blog around those scenario’s too. Let me know via comments below. Please do share your favorite powershell script with me via email and I will be happy publish it with due credit.

Reference: Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – CREATE OR ALTER Supported in Service Pack 1 of SQL Server 2016
Next Post
SQL SERVER – FIX – Error – Msg 4928, Level 16, State 1. Cannot Alter Column Because it is ‘Enabled for Replication or Change Data Capture’

Related Posts

7 Comments. Leave new

  • Hi, could you recommend any books or resources for getting started with powershell?

    Reply
  • Hey Pinal,
    Instead of messing with all that SMO stuff, just use the Get-SqlDatabase cmdlet:

    Get-SqlDatabase -ServerInstance localhost -Name msdb |
    SELECT *

    Reply
  • Francesco Mantovani
    August 1, 2018 2:26 am

    I saved the .ps1 file but is not working:
    `New-Object : A positional parameter cannot be found that accepts argument ‘$null’.`

    Reply
  • I have fixed formatting issue. Can you check now?

    Reply
  • The following script will just combine all of the CSV files in a directory into a single CSV file. This code will take the header from the first file for use in the new stitched CSV. Headers in the remaining source files will be ignored.

    $getFirstLine = $true

    get-childItem “c:\path\to\files\*.csv” | foreach {
    $filePath = $_

    $lines = $lines = Get-Content $filePath
    $linesToWrite = switch($getFirstLine) {
    $true {$lines}
    $false {$lines | Select -Skip 1}

    }

    $getFirstLine = $false
    Add-Content “c:\path\to\files\final.csv” $linesToWrite
    }
    This will also work for TSV and TXT files. Simply change the file extensions in the above script

    Reply

Leave a Reply

Menu