PowerShell Commands for SQL Server Instance and Database Settings


By:   |   Updated: 2021-04-21   |   Comments (3)   |   Related: 1 | 2 | 3 | More > 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.

login options

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.

login error

If you specify an instance that either doesn't exist or there are issues connecting, the script will notify you as follows.

login error

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.

script collecting instance info

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.

script collecting database info

This is what the output file should look like after the script has completed.

report output

Here is the database information.

report output

Download the PowerShell Script

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.



Last Updated: 2021-04-21


get scripts

next tip button



About the author
MSSQLTips author Alejandro Cobar Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

View all my tips



Comments For This Article




Wednesday, April 21, 2021 - 11:06:48 PM - Alejandro Cobar Back To Top (88583)
@Tina, probably in your system there's currently a policy that doesn't allow you to run PowerShell scripts. But you can try to execute "Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass" and see if that solves your issue.

@Francesco, you are right, there's a bug with the version of the script uploaded in the article. However, I have already requested the admins. to update it with the one that addresses such issue. Thanks for letting me know!

Wednesday, April 21, 2021 - 11:54:47 AM - Tina Somot Back To Top (88578)
Ah, I was excited trying to try it out but my system said "'
PS C:\temp> .\Properties-Retriever.ps1
.\Properties-Retriever.ps1 : File C:\temp\Properties-Retriever.ps1 cannot be loaded. The file C:\temp\Properties-Retriever.ps1 is
not digitally signed. You cannot run this script on the current system. For more information about running scripts and setting
execution policy, see about_Execution_Policies at http://go.microsoft.com/fwlink/?LinkID=135170.
At line:1 char:1
+ .\Properties-Retriever.ps1
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : SecurityError: (:) [], PSSecurityException
+ FullyQualifiedErrorId : UnauthorizedAccess"

Wednesday, April 21, 2021 - 5:56:08 AM - Francesco Mantovani Back To Top (88572)
While your previous script was working this script has a bug in the login loop: I can choose T,W,S but they all keep asking the same question


download





Recommended Reading

PowerShell Commands for SQL Server Best Practices

Powershell Commands for SQL Server Always On Availability Groups

Understanding the COLLATE DATABASE_DEFAULT clause in SQL Server

Verify Connectivity to SQL Server

SQL Server Page Verify CHECKSUM vs NONE Performance














get free sql tips
agree to terms