Here’s just a quick Powershell script I knocked up to find out the server-level and database collations on multiple servers. Just specify each SQL Server in the array called $servers and you’re good to go. ?View Code POWERSHELL# Load SMO [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null; # Specify servers here $servers = @("localhost\sqlexpress", "localhost"); foreach($server in $servers) [...]
On some of our SQL Servers the Fulltext catalog locations are not excluded from anti-virus scans so I was after an easy way to get this information quickly. Once again Powershell proves its worth! Just change the variable $server to query a particular server. The script will list all fulltext catalogs on the instance. ?View [...]
Today I needed to quickly audit the edition of all our active SQL Server machines. This is a snap with a little bit of Powershell. Just execute the below code, replacing the names of each sql instance as appropriate. ?View Code POWERSHELL$sqlservers = @("sqlserver1", "sqlserver2", "sqlserver3", "sqlserver4", "sqlserver5", "sqlserver6", "sqlserver7"); # Load smo [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | [...]
I was recently asked for a list providing size details of all the database and transaction log backups we take for SQL Server. Along with this I was asked to provide the approximate daily backup size. Since I’m no fan of trawling through folders, ordering by date modified, and then viewing properties to get the [...]
Here’s a few of the keyboard shortcuts for Powershell I always find myself using during the day at work. Pause output on the screen Sometimes a command may produce lots of output causing the screen to scroll rapidly. Pressing Control & S will pause the output currently on the screen. Press any key to continue [...]
As it’s looking increasingly likely I’ll be deploying Windows Failover Clustering, as a HA solution at work, I thought it would be prudent to swot up on a little related Powershell. I’ve picked out a few clustering cmdlets that will be helpful for building scripts to manage and monitor a cluster. First things first! If [...]
Sometimes I’m writing Powershell scripts to gather information on a seemingly ad-hoc basis and then someone says; "Oh, and can you send that to me in an email every day|week|month". These scripts would often use a bunch of Write-Host statements to output to the console. Modifying these to send the output in an email can [...]
I’ve written a post about Documenting Databases with Powershell that’s featured today on the Microsoft Scripting Guys blog. Head on over and check it out!
I’ve been working on a performance benchmarking project recently to gauge the effect of new releases to our systems. Powershell happens to be very useful gathering various system statistics using the Get-Counter cmdlet. There’s literally a tonne of available counters for everything to disk use, memory usage to sql server specific counters. You can list [...]
Recently I needed to check which of our databases, on our many SQL Servers, had the TRUSTWORTHY property set to true. This property, when set to false, can reduce certain threats from malicious assemblies or modules. Obviously this should only be enabled where it needs to be. Here’s a quick Powershell script that will enable [...]
