Automate SQL server version and database compatibility report using PowerShell

by Sandeep Arora on Aug 10, 2016 12:00:00 AM

PROBLEM

What version of SQL Server are you using? Is the current version supported as per Microsoft Support Lifecycle policies? Are you running the latest Service Pack? Are you still clinging to old database compatibility levels? Earlier this year when Microsoft Retired Support for SQL Server 2005 we were tasked to review all the Servers within the client environment and provide answers to all of the above questions. Now there are 2 ways of doing the same, or at least I could only think of the following:

Log into all the Servers one by one, and run some SQL Scripts to collect the data. Use Excel or Word to organize the data and then verify the details online. This approach is interesting, and based on the number of servers you have in your environment you will doing this repetitive task on all those servers again and again and again before you have the final report.
Automate the entire report using Powershell. All you need to do is provide a list of SQL Instances and wait for a little while Powershell does it magic to generate a HTML formatted report for you.

I am not going to discuss both options because that's not why you guys are here. Follow along to get the answers you're looking for.

SOLUTION

First of all, I wanted to share the motive behind doing this code. I was tasked to review around 200 SQL Instances for Current SQL Server Version, Current SQL Server Service Pack, and Database Compatibility Levels. I then had to share a report with a list of instances\databases to be upgraded or altered. I'm not sure about you, but 200 servers are a lot for me to check manually. I won't repeat the same steps even on a second server, so 200 was obviously out of question. Then I decided to automate it using Powershell. When you need to do a similar task more than once you should always think about automating it. The Powershell script shared in the following link will accept a list of parameters,which is primarily going to be list of SQL Server Instances and location to dump the report. The script will give you a complete summary of Current SQL Server Versions, Current Service Pack, Database Compatibility Level details, and will make suggestions on what to upgrade or alter. I am not going to discuss why you should really care about the old versions of SQL Servers, not applying the latest service pack, or old database comparability levels. We both know you are not here for that kind of information. I believe you already know why Latest SQL Server versions or Service Pack and Database Compatibility levels makes sense for you or maybe not depending upon your application requirements. Verified on following platforms

Powershell v2.0 and higher versions
Windows Server 2008 and higher versions

ACTUAL SCRIPT

Click the below link to download the Powershell Script
 SQL Server Compatibility Report Code

How to Execute the Script? I am keeping things simple by keeping everything on Desktop but you can use it by choosing the location of your liking

Copy the File to Desktop
Open PowerShell console as Administrator and set execution policy to remotesigned
Execute the Script with required parameters

What if I have a long list of SQL Instances to review? If you have a long list of SQL Instances and passing Instance names as comma delimited list is going to be laborious job don't worry.

Create a text file and copy the list of SQL Instances delimited by end of line between records
Go to console and import it into a variable using get-content and pass that variable as a parameter

Comments (1)

See all

Share this