Finding lost or forgotten SQL Servers

I have recently been tasked with finding all SQL Servers for inventory purposes. As I set to this task, I identified a couple of issues immediately. First, was that my new place of employment doesn't like to purchase any 3rd party applications. Also, most free tools including Microsoft Assessment and Planning toolkit (MAP) scan servers tend to look for the SQL Browser. These kinds of products aren't going to work well because if the SQL Browser is off, then the server will not be found. So, I set out to accomplish the task using PowerShell. Think of PowerShell as a sql query window for every Microsoft product. This script will look for the SQL Server service, running or stopped. Also, it will execute once a week through a SQL Agent job, so I can see what new servers have been installed recently. PowerShell is a wonderful tool, and not just for network administrators. All right, now the good stuff.

First, since it will be executing through a SQL job, the SQL Agent will need to be setup with a security ID strong enough to reach all Windows operating systems throughout the network. This script will go across domains, so no need to run in each domain.

IMPORTANT: if the ID does not have sufficient rights to all computers then it will simply skip past the server even though it saw the server on the network, so your list will not be complete. You may want to reach out to the network security group and let them know in advance, before they think there is a virus running rampant.

Next, we need to create a simple database and table to store the discovered SQL Server names. The following script sets up the tables and databases (you may need to change the file paths):

/******
Stored Procedure builds a list of new SQL Servers found and emails a report to @EmailRecipients.
by Clay Punnett
PunnettC@Yahoo.com
@SQLHeap
******/
CREATE Procedure [dbo].[sp_SendWeeklySQLReport]
as

Note that when you see new SQL Servers, it may be because the computer simply was not on during the previous runs. These scripts pull in just the computer name. Also, They can be modified to gather more data such as instance name, sql version, sql edition, etc.

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.