Find SQL Server Installation Date for all Instances with PowerShell

By:   |   Comments (1)   |   Related: More > Database Administration


Problem

You are starting as a new DBA and there are hundreds of SQL Server instances that have been installed by someone no longer with the company. Wouldn't it be nice to be able to know, for these hundreds of instances, when each was installed? That can give you a rough idea of how long the SQL Server services (now under your care) have been around.

Solution

In this tip I'm going to present you a PowerShell script that you can use to fetch the installation date for all the SQL Server instances that you specify, within a single run.

Code explained, along with some assumptions

To be able to use this, you need to have compiled (or at least have been handed) a list of servers under your care. You are free to choose the way to feed the PowerShell script with the list of instances to traverse (e.g. a separate text file with such list), but in this tip (and as I always do within all of my tips) I'm going to use a table that I have populated with such information.

Here is a brief overview of each section of the PowerShell script below.

##SECTION 1

  • The script can work with the parameter $populateInstances (which by default is set to 0).
    • When 0, it means that you already populated the instances table and won't be doing so within the script.
    • When 1, it means that the script will populate the instances table for you, which requires you to enter them in ##SECTION 3.
  • The $server and $inventoryDB values should be modified to specify your SQL Server instance and database where you will be centralizing the information collected.

##SECTION 2

  • In this section, 3 tables will be created for you, in case they don't already exist.
    • The first one is where the instances list will be stored, and a table with the name of instances.
    • The second one is where the results fetched from each instance will be stored.
    • The third one is an error log table to capture any error encountered while trying to fetch the results from each individual instance.

##SECTION 3

  • In this section, the $populateInstances parameter is evaluated and if you enter 1 then it will attempt to insert the respective instance in the instances table.
    • Please make sure to replace the XXXX with the values to fit your use case (feel free to add more parenthesis if you need).

##SECTION 4

  • In this section, the list of instances to traverse is specified and executed. After the list has been retrieved from the central SQL Server instance, it will be stored within the $instances variable.

##SECTION 5

  • This section contains the TSQL code that will retrieve the installation date from all the instances.

##SECTION 6

  • Prior to attempting to do anything, the table "instances_installation_date" is TRUNCATED so that results obtained within each script execution are not accumulated over time. If you wish to accumulate results over time, for whatever reason, feel free to comment or delete this section.
  • This section contains the main mechanics that perform the work, using the following high-level approach:
    • For each instance found in the instances table, apply the TSQL from ##SECTION 5.
    • Within a try/catch block, the connection to the instance is made and the information is fetched from it.
    • If there's an error while trying to connect to the instance, or if there's an error with the query itself being passed, then such error will be logged in the table called "error_log" so that you can perform the respective troubleshooting later. If an error is encountered, then that particular instance will be skipped, and the script will continue with the rest without interrupting its execution.
    • Finally, the respective INSERT statement that is dynamically built during the loop is applied and the information can be viewed in the target table at the end.
PowerShell Script to Find SQL Server Installation Date for All Instances
##SECTION 1 BEGIN
param(
    $populateInstances = 0
)
 
#This is where the data will be fetched and stored, so update it according to your case
$server = "Localhost"
$inventoryDB = "master"
##SECTION 1 END
 
##SECTION 2 BEGIN
#Create the central table where you will store the information gathered from all the instances (if it doesn't exist of course
$masterServerListTableCreationQuery = "
IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'instances') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
CREATE TABLE instances(
   [instance]  [VARCHAR](32) NOT NULL PRIMARY KEY
) ON [PRIMARY]
END
"
Invoke-Sqlcmd -Query $masterServerListTableCreationQuery -Database $inventoryDB -ServerInstance $server
 
$resultsTableCreationQuery = "
IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'instances_installation_date') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
CREATE TABLE instances_installation_date(
   [instance]                  [VARCHAR](32) NOT NULL PRIMARY KEY,
   [installation_date]         [DATETIME] NOT NULL,
   [data_collection_timestamp] [DATETIME] NOT NULL
) ON [PRIMARY]
END
"
Invoke-Sqlcmd -Query $resultsTableCreationQuery -Database $inventoryDB -ServerInstance $server
 
$errorLogTableCreationQuery = "
IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'error_log') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
CREATE TABLE error_log(
   [script]          [VARCHAR](32) NOT NULL,
   [instance]        [VARCHAR](32) NOT NULL,
   [message]         [VARCHAR](MAX) NOT NULL,
   [error_timestamp] [DATETIME] NOT NULL
) ON [PRIMARY]
END
"
Invoke-Sqlcmd -Query $errorLogTableCreationQuery -Database $inventoryDB -ServerInstance $server
##SECTION 2 END

##SECTION 3 BEGIN
#If you choose to populate the instances table, please complete this query text accordingly by replacing XXXX with the name of each instance.
#Note: Feel free to add more parenthesis if you need to suit your case (these are just for reference only).
#If you choose not to, then the script assumes that the instances table is already populated to do its thing.
if($populateInstances -eq 1){
    $insertInstancesQuery = "
    INSERT INTO instances VALUES
        ('XXXX'),
        ('XXXX'),
        ('XXXX')
    "
    Invoke-Sqlcmd -Query $insertInstancesQuery -Database $inventoryDB -ServerInstance $server
}
##SECTION 3 END
 
##SECTION 4 BEGIN
#Fetch all the instances from the list you specify
$instanceLookupQuery = "
SELECT instance
FROM instances
" 
$instances = Invoke-Sqlcmd -Query $instanceLookupQuery -Database $inventoryDB -ServerInstance $server 
##SECTION 4 END
 
##SECTION 5 BEGIN
#Let's prepare the query that will fetch the installation date for the instance
$installationDateQuery = "
SELECT create_date FROM sys.server_principals WHERE sid = 0x010100000000000512000000
"
##SECTION 5 END
 
##SECTION 6 BEGIN
#Let's truncate the target table so that you can always have a fresh data collection set, without accumulating it with each execution
Invoke-Sqlcmd -Query "TRUNCATE TABLE instances_installation_date" -Database $inventoryDB -ServerInstance $server 
 
#Let's perform the lookup for each instance
$insertQuery = "INSERT INTO instances_installation_date VALUES "
 
foreach ($instance in $instances){
    #Go grab the information for the instance
    Write-Host "Fetching installation date from instance:" $instance.instance
 
    try{
        $result = Invoke-Sqlcmd -Query $installationDateQuery -ServerInstance $instance.instance -ErrorAction Stop -querytimeout 30
        $insertQuery += "('"+$instance.instance+"','"+$result[0]+"',GETDATE()),"
    }
    catch {
        Write-Host -ForegroundColor White -BackgroundColor Red "Error while trying to connect to instance" $instance.instance "please check the error_log table..."
        $query = "INSERT INTO error_log VALUES('Instances Installation Date','"+$instance.instance+"','"+$_+"',GETDATE())"
        Invoke-Sqlcmd -Query $query -ServerInstance $server -Database $inventoryDB
    }  
}
 
#Apply the insert to the target table
Invoke-Sqlcmd -Query $insertQuery.Substring(0,$insertQuery.LastIndexOf(',')) -ServerInstance $server -Database $inventoryDB
##SECTION 6 END
 
Write-Host "Done!"

In my case I have 3 instances, so initially my instances table will look like below. I have entered an instance called "Localhost2", which doesn't exist, that will be used to show what happens when the script can't make a connection to a valid instance.

list of sql server instances

Since I already have the instances table populated, the script will be executed with the $populateInstances parameter set to 0, and this is what you will see in the console:

process info

As you can see, all the results were successfully fetched from each valid instance and the error was correctly reported for the "invalid" one. If you take a look at the error log table, it will look like this:

sql server instance installation date 003
  • The first column gives you a hint to know which script logged the error, in case multiple different scripts are running in your environment and use the exact same table.
  • The second column tells you the name of the instance with the issue, at least it tells you the name of the instance supplied to the script that throws the error.
  • The message column contains the actual error message thrown by SQL Server, for troubleshooting purposes.
  • The error_timestamp column helps you keep track of when the error was reported.

Here's the results table after being populated by the script:

query results
Next Steps
  • You can go a bit further and automate this execution through a SQL Server Agent Job. This will make sure that when a new instance is deployed, then it will have its installation date updated in your inventory.
    • The addition of the new instance would have to be manual, but it is totally worth the effort.
  • You might think: "Hey, I can do the exact same thing against a set of Registered Servers in SQL Server Management Studio" … and yes, you are totally right! The purpose of this is to give you one more way to accomplish the task.
  • Download the PowerShell script and test it in your development / test environment prior to moving to production.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Monday, October 21, 2019 - 8:39:20 AM - shivkumar Back To Top (82842)

Thanks for sharing useful information about sql server.














get free sql tips
agree to terms