Press "Enter" to skip to content

Using PowerShell to report on thousands of Microsoft SQL Instances & Databases

Joshua Stenhouse 0

When managing a large Microsoft SQL environment one of the biggest challenges is often staying on top of your data. When you have hundreds to thousands of DBs and instances, PowerShell and Invoke-Sqlcmd can be an absolute lifesaver. Why? Because a single PowerShell script can query each remote SQL instance then collate the data into a single table.

Answers to questions such as; how many DBs are there on this instance? When was the last log backup on this DB? Was it within the last 2 hours? How many instances am I running? What is the size of this DB? How many files does it have? All the answers are now just 1 PowerShell script away. To get you started click on the link below:

MSSQLDBReportingv1.zip

Unzip the file to “C:\MSSQLDBReportingv1\”, edit MSSQLInstances.csv and enter your server and instance names, I.E:

SQL16-VM01.lab.local
SQL16-VM01.lab.local\MSSQLInstance01

Open the .ps1 script and edit the variables for $FullBackupSLAInhours, $DiffBackupSLAInhours, $LogBackupSLAInMinutes. These values will be used to calculate whether the last backup of each is within the SLA defined. If you’re not doing Diff or Log backups (I.E it’s a simple DB) then the SLA isn’t calculated.

Once configured, run the script and you’ll be prompted for your SQL credentials which are saved securely for subsequent runs. The script requires a SQL or SA account and it expects you to use the same credentials for all instances. If you can’t load SQL Server Management Studio and connect to the same server/instance name with the credentials specified, then the PowerShell script won’t ever work (as it’s essentially doing the same thing).

If successful, you’ll see the following reports produced:

DB Sizes:

SQLDBSizes

DB Files:

SQLDBFiles

DB Backups:

SQLDBBackups

Instance Summary:

SQLInstanceSummary

Environment Summary:

SQLEnvironmentSummary

You now have a global view on exactly what’s happening in your SQL environment! Pretty cool huh? You could send the result by email, export it to a CSV, or even write it into another SQL DB with Invoke-Sqlcmd. Thanks to my local SQL MVP Chris Lumnah for giving me his excellent query for full, diff, and log backups. Happy scripting,

Joshua

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: