By: Alejandro Cobar | Comments (3) | Related: 1 | 2 | 3 | > Database Configurations
Problem
During our run as professional DBAs, we definitely spend a lot of time acting as firefighters (not saying that it's something ideal, but it is what it is), and during that time we don't have enough time to implement important stuff (e.g. monitoring mechanisms) to give us that desired "peace of mind" state. Of course, you can do a ton of things with 3rd party monitoring solutions, but not everyone has the budget to afford one, sadly.
Imagine that you are a DBA taking care of hundreds of SQL Server instances, and in one unfortunate day, the server where one of those is hosted simply decides to rest in peace, and there's no way to recover anything. Of course you, as a committed DBA, have database backups to restore the databases, but do you have the necessary information to re-install the SQL Server instance with the exact same configuration values/settings that the defunct instance had? Hardly, since you probably were firefighting all the way up to this point.
To address this, I have prepared a PowerShell script that can help you gather this information for your instances, so that you are covered against this particular scenario, which can turn into a complete nightmare.
Solution
As a part of a series of articles, called PowerShell Tools for the DBA, I'm presenting a PowerShell tool that can help any SQL Server DBA collect important information about their SQL Server instances, regarding their own particular properties and settings.
Initial considerations
Here are some assumptions before diving into the details of the script:
- For all the tests to work flawlessly, make sure that the account running the script has access to execute the SERVERPROPERY stored procedure, read access to the sys.databases table, and read access to the sys.configurations table.
- The items are not displayed in any particular order.
- The output file, with all the information related to your instance, is generated in the exact same path where the PowerShell script is located and triggered, so make sure that you have write permissions there.
- If you execute the PowerShell script multiple times, for the same instance, then any previous existing output file will be overwritten and only the most recent one will be kept. Therefore, if you need to keep a very specific file, make sure to manually save it somewhere else.
- There will be certain properties that might not be available, or may not apply, for your SQL Server version, so the value displayed for that property shows as "N/A".
Using the PowerShell script
Here's what you can expect from the script:
Go to the location where you have placed the PowerShell script file, and run it as shown below.
I'm using C:\temp as an example and the script expects, as a parameter, the name of the instance you want to generate the output file from. If no name is specified, 'localhost' will be assumed.
PS C:\temp> .\Properties-Retriever.ps1 SERVER\INSTANCE
The only thing the script will ask you for is the type of login you want to use to connect to the instance.
- Trusted = the connection to the SQL Server instance will be made with the same user being used for the execution of the PowerShell script (basically, you don't have to specify any credentials, it will assume them based on the context).
- Windows Login = A Windows login has to be provided for the correct authentication.
- SQL Login = A SQL login has to be provided for the correct authentication.
No matter what option you choose, make sure that it has enough privileges in the instance to perform the checks.
If you choose a type of login that requires you to input credentials, the script will notify you if such credentials fail to connect to the specified replica as shown below.
If you specify an instance that either doesn't exist or there are issues connecting, the script will notify you as follows.
With the correct information specified, the script will start to collect the information for the specified instance and will generate a .txt named after the instance, so make sure that a file with the exact same name doesn't already exist or it will override it.
As you can see in the screenshot above, the script will tell you all the properties that it has successfully written to the file. All of these properties are collected from the stored procedure "SERVERPROPERTY" and there are several properties fetched from the sys.configurations table as well.
After collecting the properties, it will start gathering information about the databases. I thought it was an important thing to capture as well, but if you don't think so, you can simply comment out the function call "Get-Databases $instance" in the script.
The script is very intuitive, so you will understand right away what is being collected.
This is what the output file should look like after the script has completed.
Here is the database information.
Download the PowerShell Script
- You can download the PowerShell script here.
Next Steps
If you'd like to collect information from multiple instances at the same time, you can span multiple PowerShell windows and throw a different instance at each.
I'm certain that there are a ton of properties and settings I didn't cover, but this can serve as a stepping stone. In the end, for this particular concept to fully reach its potential, it definitely requires the feedback from members of the MSSQLTips community.
About the author
Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
