Remotely Determine SQL Server Instances via Command-Line

Article
1/17/2024

Purpose

Many times a database administrator will want to know what SQL Server instances are running on a remote server.
Below is a simple, command-line method for determining that information, either against a single server or multiple servers.
Go HERE to download the full script to have a complete working copy of both the wrapper (calling script) and the main (called) script

Assumptions

You have a c:\scripts folder
You have adequate permissions/privileges to run necessary commands against a remote server
You know how to enter / invoke the Windows command shell

Steps

1) How to Execute Against a single server

From any computer where the user has appropriate privileges, in the Windows command shell, after saving the “instances.bat” script below to your “c:\scripts” folder, type:

C:\scripts\Instances remote-server

Where “remote-server” is the name of the server whose SQL instances you want to list.

2) How to Execute Against Multiple Servers

To use in a batch file, against multiple SQL servers, create 2 batch scripts:
a) One batch script to be "called" by the other.
b) One batch script to "call" the first ("called") script.

For example, instances.bat (below) will be the "called" script:

Create a Script to be Invoked (Called) by Another Script

[INSTANCES.BAT will be "called" by call-instances.bat]

@echo off
...
rem - c:\scripts\Instances.bat
echo.
echo "Listing instances of SQL Server..."
... 
sc ... [NOTE: This is a SNIPPET (sample) only; please download full script by clicking HERE
echo.
echo. >> %loc%\%pfx%-log.txt
rem

Create a "Calling" Script to Invoke (call) the Previous (Called) Script

[CALL-INSTANCES.BAT will be the "calling" script.]
[This script will contain the list of all remote servers to be reported against, 3 in this example.]

 
call [your]instances.bat batch remote-sqlserver1
rem - SAMPLE ONLY, Click HERE to download full script (the one script contains both wrapper & main script) - thanks!
echo "Finished..."

NOTE: In the above script, remote-sqlserver1, 2 & 3 are the names of the remote SQL servers

Execute the "Calling" Script"

From Windows command shell, type:

c:\scripts\call-instances.bat

Review the Log Results

Review [your] instances-log.txt (NOTE: The script automatically opens the log/report for your review)

Share via