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:
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)
7 Comments. Leave new
Hi, could you recommend any books or resources for getting started with powershell?
Hey Ian, I recommend the book SQL Server 2014 with PowerShell v5 Cookbook by Donabel Santos. It covers SQLPS, but if you can install SQL Server Management Studio 2016, there’s SQLPS’s predecessor, SqlServer which loads faster and has a lot more commands.
Hey Pinal! Check out the SqlServer module — there’s no more need to use -DisableNameChecking and it has a bunch of bug fixes. It is only available in SQL Server 2016, however.
https://cloudblogs.microsoft.com/sqlserver/2016/06/30/sql-powershell-july-2016-update/
Hey Pinal,
Instead of messing with all that SMO stuff, just use the Get-SqlDatabase cmdlet:
Get-SqlDatabase -ServerInstance localhost -Name msdb |
SELECT *
Good Point!
I saved the .ps1 file but is not working:
`New-Object : A positional parameter cannot be found that accepts argument ‘$null’.`
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