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:
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:
DB Files:
DB Backups:
Instance Summary:
Environment Summary:
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