PowerShell script that displays all SQL Server folder locations from the registry.

Recently I was working on an issue where the SQL Server agent was missing from the SQL Server configuration manager. After going through sqlauthority blog realized there was a wrong entry in the registry. Once I updated the SQL Server agent exe location in the registry with the correct location then I was able to see the SQL Server agent back in the configuration manager.

 

So after this task, I thought of writing a PowerShell script that can display all the SQL Server folder locations from the registry instead of searching manually. This script helps in displaying all the SQL Server registry entries of all the instances installed in the server where this script is being run. Also, it will store the output of the script by default in ‘C:\’ drive with the name “Script_Registry_Entries_1.1_OutPut.txt”.

 

NOTE: Please remember incorrect updating of the registry will cause permanent damage to the server and data. So, please test all the scripts in lower environments first.

 

This single script named Script_MSSQL_Registry_Entries_1.1.PS1 will query the registry and display below details of all the SQL Server instances installed in the current server.

 

1. Startup Parameters

2. AgentErrorLog

3. SQLBinaryRoot  

4. SQLDataRoot 

5. ExternalLibraries

6. SQLServerPath

7. SQLServerProgramDirectory 

8. SQLServerErrorDump 

9. SQLServerBackupDirectory

10.SQLServerBrowser 

11.SQLServerCEIPEXE

 

Please be careful while working with the registry. As any incorrect modification of the registry will cause data loss. 

 

IMPORTANT NOTE:

 

 ** This script will work only in Powershell ISE or Powershell 62 bit.

 ** It should be run as an ADMINISTRATOR.

 

I invite you to follow me @my blog: https://sqlserverstore.blogspot.com/ and @ Twitter: @vinayprasadv for more useful articles.

You can download the script from below:

Sample output screenshots:

I am working on updating this script further and will re-share details once I complete it.

This script will be very helpful when we need to verify the SQL Server root folder locations in the registry while working on tasks like database migrations, database upgrades, SQL server registry issues, SQL server services missing, and so on.

 

Thanks VV!!

#powershell #registry #mssql #sqlserver #sql #script #automation

How to convert PowerShell script into an exe.

Task: I want to run a script regularly on different servers.

We face this kind of tasks several times, basic examples like to find when the server was last rebooted or what are the current free spaces of all disks of a particular server and so on.

So basically I have a script to do some ‘xyz’ work and I want to use the same script in many other servers and even my team members also should be able to run it when needed. So the better way to do is converting the PowerShell script into an exe and distribute the exe to all my team members or place it in a shared location where my entire team has access.

How to convert PowerShell script to exe:

1.    To do this first you need to install ‘PowerGUI Script Editor’ if you don’t have it already.

2.    Open ‘PowerGUI Script Editor’: Start à All Programs à PowerGUI à PowerGUI Script Editor

3.    Open new page by pressing ‘Ctrl+N’. Paste the script you want to convert to exe.

4.    Execute the script by clicking ‘Play’ button which will be on top or by pressing shortcut ‘F5’. This is just to test script is working without any errors. My sample script to get last reboot date time is giving results as expected.

             Now steps to convert PowerShell script to exe

5.    Click on ‘Tools’ and select ‘Compile Script’ or shortcut for this is Ctrl+F9. Screenshot below:

6.    Once compile script window opens give the path to save the script as exe, as shown in screenshot below:

7.    Now you have the exe created for our script.

8.    Now you can run your script by just double-clicking the exe. This exe will run fine in any of the machines where PowerShell is installed.

Please share what are the steps you follow to convert your scripts to run on different servers.

Thanks VV!!