SQL Server Database Inventory using PowerShell

Dathuraj Pasarge

0.00/5 (No votes)

8 May 2023CPOL4 min read

6.9K

Extract SQL Server DB inventorybaseline using TSQL and PowerShell scripts

This code extracts the SQL server database inventorybaseline using TSQL and PowerShell scripts.

Introduction

It's very essential to have an accurate inventory report for every service line, and moreover database is one of the vital components as its dynamic nature on DB creation\deletion from an instance, hence a much relevant PowerShell script is required to generate the monthly SQL Database inventory for an internal team DBA reference and for the management.

One simple, yet very relevant script is required to generate the instance and database details along with other environment details, such as domain, version, edition, environment type, etc. As most of you are aware, it's difficult to get some of the static details such as whether SQL instance is Prod, DR, Test, Dev, UAT and which application this database server is used for and on which Data Center this DB server resides, etc. However, only the option here we have is giving these static data from the input file and getting the final results along with the data captured from the SQL instances and databases.

Background

Although many scripts and articles are already available on the web, they still fulfill only to an extent and miss a lot of vital information. Every organisation would require many more details as mentioned above to generate the DBA team, baseline trend, billing team, project teams, etc.

Here, I provide two different ways using which you can retrieve the SQL inventory:

Method #1. Using Powershell DataTable object

Method #2. Using SQL Server Database and Table

Inventory Scripts

Method # 1. Using PowerShell Datatable Object

In this method, we will not be creating any SQL database and table, rather we are storing the query result set into the DataTable object of PowerShell. This is the best method as we are not creating any objects on the SQL instance and no changes and approvals would be needed for implementing it in your environment.

From whatever server you are trying to execute the PS script, all the below objects were created through the PowerShell script on first run, and only changes that we need in servers.txt by providing the HostName, SQLInstanceName, ApplicationName, Environment, and DC.

Here are different files\folders used in this script:

Folders – Created automatically if they were not present on the server.

Servers.txt – A new text file will be created if it does not exist already. This file is created with few columns and one dummy instance record for your reference, you should not make any changes in the first line (which are column names and referred to in Part two of PowerShell script). Only the second row must be updated with the server details. These must be updated without any spaces, if any value with spaces must be enclosed with double quotes “ “. A null index error will be triggered if file contains the blank rows in between or at the end of the file and if any spaces at the end of each row.

SQLDatabaseInventory_DDMMYYYYHHMMSS.csv – Every time you execute the second part of the script, a new csv file will be created with datetime stamp and imports the inventory data.

PowerShell

//

# Start of Step 1
cls
$server = $env:computername

$FolderCheck ="D:\SQLHealthCheck\InventoryScript\Reports"
$ServersFile ="D:\SQLHealthCheck\InventoryScript\servers.txt"
$Errorlog="D:\SQLHealthCheck\InventoryScript\logs"

if ((Test-Path -Path $FolderCheck) -and (Test-Path -Path $Errorlog)) {
   "Path exists!"
} else {
   md "D:\SQLHealthCheck\InventoryScript\Reports"
   md "D:\SQLHealthCheck\InventoryScript\logs"

}

if (Test-Path -Path $ServersFile) {
   "File exists!"
} else {
ni $ServersFile
Add-Content -Path $ServersFile "HostName,SQLInstanceName,
                                ApplicationName,Environment,DC"
Add-Content -Path $ServersFile "SampleSQLServer001,'SampleSQLServer001\Instance1',
                                CRM,NonPord,Hyderabad"
}

# End of Step 1

# Start of Step 2

$instances = import-csv $ServersFile

if ($instances.HostName.Contains('SampleSQLServer001'))
{
   $psPopup = New-Object -ComObject Wscript.Shell
   $p=$psPopup.Popup("Update servers.txt file with your 
       environment details and rerun the script",0,"Input Servers",64)
}
else
{

$CurrentDate=Get-Date -Format G
$CurrentDate

$GetInstanceDetails ="
SELECT
  SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as HostName,
SERVERPROPERTY('SERVERNAME') AS 'SQLInstanceName',
case SERVERPROPERTY('IsClustered')
when 1 then 'Clustered'
when 0 then 'Standalone'
end as 'IsClustered',
     sys.databases.name AS 'DBName',
     CONVERT(INT,SUM(size)*8.0/1024) AS 'DBSize',
     sys.databases.create_date AS 'DBCreationDate',
     sys.databases.state_desc AS 'State',
sys.databases.recovery_model_desc AS 'RecoveryModel',
(select (substring(@@version,11,15))) as 'SQLVersion',
SERVERPROPERTY('ProductLevel') AS 'SPLevel',
     SERVERPROPERTY('ProductVersion') AS 'ProdVersion',
SERVERPROPERTY('Edition') AS 'Edition',
sys.databases.collation_name AS 'CollationName'
    
FROM    sys.databases
JOIN    sys.master_files
ON     sys.databases.database_id = sys.master_files.database_id
GROUP BY  sys.databases.name, sys.databases.state_desc,
            sys.databases.create_date,sys.databases.collation_name,
            sys.databases.recovery_model_desc
ORDER BY  sys.databases.name
"

 $Datatable = New-Object System.Data.DataTable

 $Datatable.Columns.Add("HostName", "System.String")
 $Datatable.Columns.Add("IP", "System.String")
 $Datatable.Columns.Add("SQLInstanceName", "System.String")
 $Datatable.Columns.Add("isClustered", "System.String")
 $Datatable.Columns.Add("ApplicationName", "System.String")
 $Datatable.Columns.Add("Environment", "System.String")
 $Datatable.Columns.Add("DataCenter", "System.String")
 $Datatable.Columns.Add("DBName", "System.String")
 $Datatable.Columns.Add("DBCreationDate", "System.String")
 $Datatable.Columns.Add("DBSize", "System.String")
 $Datatable.Columns.Add("State", "System.String")
 $Datatable.Columns.Add("RecoveryModel", "System.String")
 $Datatable.Columns.Add("OSVersion", "System.String")
 $Datatable.Columns.Add("SQLVersion", "System.String")
 $Datatable.Columns.Add("SPLevel", "System.String")
 $Datatable.Columns.Add("ProdVersion", "System.String")
 $Datatable.Columns.Add("Edition", "System.String")
 $Datatable.Columns.Add("Domain", "System.String")
 $Datatable.Columns.Add("CPUCores", "System.String")
 $Datatable.Columns.Add("Memory", "System.String")
 $Datatable.Columns.Add("CollationName", "System.String")
 $Datatable.Columns.Add("ReportDate", "System.String")
   
#For each instance, fetch the list of databases and few other instance details

foreach ($instance in $instances)
{
    Write-Host "Fetching the data from:" $instance.SQLInstanceName
   
    $DBDetails = Invoke-Sqlcmd -Query $GetInstanceDetails 
                    -ServerInstance $instance.SQLInstanceName 
                    -ErrorAction SilentlyContinue -querytimeout 60
  
    $IPAddress= Get-WmiObject -ComputerName $instance.HostName 
                   win32_networkadapterconfiguration | 
                   where { $_.ipaddress -like "1*" } | select -ExpandProperty ipaddress
  
    $OSDetails=Invoke-Command -ComputerName $instance.HostName 
       -ScriptBlock {(Get-wmiobject Win32_ComputerSystem).Domain,
       (Get-CimInstance Win32_ComputerSystem).NumberOfLogicalProcessors,
       [Math]::Round((Get-CimInstance Win32_ComputerSystem).
        TotalPhysicalMemory/1024/1024/1024),
       (Get-WmiObject -class Win32_OperatingSystem).Caption}  

 if($DBDetails.Length -ne 0)
 {   
  foreach($db in $DBDetails)
  {   
   $row = $Datatable.NewRow()
    
   $row.HostName = $db.HostName
   $row.IP = $IPAddress
   $row.SQLInstanceName = $db.SQLInstanceName
   $row.IsClustered = $db.IsClustered
   $row.ApplicationName=$instance.ApplicationName
   $row.Environment=$instance.Environment
   $row.DataCenter=$instance.DC
   $row.DBName = $db.DBName
   $row.DBSize = $db.DBSize
   $row.DBCreationDate = $db.DBCreationDate  
   $row.State = $db.State
   $row.RecoveryModel = $db.RecoveryModel
   $row.OSVersion=$OSDetails[3]
   $row.SQLVersion = $db.SQLVersion
   $row.SPLevel = $db.SPLevel
   $row.ProdVersion = $db.ProdVersion
   $row.Edition = $db.Edition
   $row.Domain= $OSDetails[0]
   $row.CPUCores= $OSDetails[1]
   $row.Memory= $OSDetails[2]
   $row.CollationName = $db.CollationName
   $row.ReportDate=$CurrentDate
   $Datatable.Rows.Add($row)   
   }
   }    
  }   
} 
 
   $inputInstanceCount=$instances.Count 
   Write-Host "Total no.of instances requested for an inventory:" $inputInstanceCount
   $ConnectedInstancesCount=($Datatable | select SQLInstanceName -unique).Count
   Write-Host "Total no.of Instances connected:" $ConnectedInstancesCount

   $CurrentDate = $CurrentDate.ToString().Replace(':','').
                   Replace('/','-').Replace(' ','').Replace('-','')
   $Datatable.Rows | Export-Csv 
                   -Path "$FolderCheck\SQLInventoryReport_$($CurrentDate).csv"

   Write-Host "If any erors, please validate and fix the issues"
   Write-Host "Find the final report at $FolderCheck "

# End of Step 2
//

Method # 2. Using SQL Server Database and Table

This method is very identical to Method A, but here a new database and table will be created on the server on which you are executing the PowerShell script. By default, a default SQL instance will be selected by the script for creating an inventory database and table, if you wish to make changes, update instance name variable [$SQLInstanceName=$env:computername].

All the below objects were created through the PowerShell script on first run, and only changes that we need to amend is servers.txt by providing the HostName, SQLInstanceName, ApplicationName, Environment, and DC.

Every time you rerun the script, table – “tblDatabaseInventory” gets truncated and a complete fresh result set is inserted into the table.

PowerShell

//

# Initially, you must run only the Step1, 
# once after updating the server details in servers.txt, then execute the step2
# Update the SQL instance name (line # 3)
# on which you want create this inventory database and table.

# Step 1 Start  ==============  # Step 1 Start 

$server = $env:computername
$inventoryDB = "SQLInventoryDB"

# Update the below variable, if SQL database created on a named instance (hardcoded as 
# "SQLServer001\Instance01" incase of not the default instance (hostname).

$SQLInstanceName=$env:computername

$FolderCheck ="D:\SQLHealthCheck\InventoryScript\Reports"
$ServersFile ="D:\SQLHealthCheck\InventoryScript\servers.txt"

if (Test-Path -Path $FolderCheck) {
   "Path exists!"
} else {
   md "D:\SQLHealthCheck\InventoryScript\Reports"
}

if (Test-Path -Path $ServersFile) {
   "File exists!"
} else {
   ni $ServersFile
   Add-Content -Path $ServersFile "HostName,SQLInstanceName,
                 ApplicationName,Environment,DC"
   Add-Content -Path $ServersFile "SQLServer001,
                 'SQLServer001\Instance1',CRM,NonPord,Hyderabad"
}

$CreateDBQuery="
IF NOT EXISTS (SELECT * FROM master.sys.sysdatabases WHERE name = 'SQLInventoryDB')
CREATE DATABASE SQLInventoryDB
GO"
Invoke-Sqlcmd -ServerInstance $SQLInstanceName -Database master -Query $CreateDBQuery 

$CreateTableQuery = "
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'tblDatabaseInventory' _
               AND xtype = 'U')
CREATE TABLE [tblDatabaseInventory](
     [HostName] [varchar](155) ,
     [IP] [nvarchar](60) ,
     [SQLInstanceName] [Nvarchar](155) ,
   [isClustered?] [varchar](30) ,
   ApplicationName [Nvarchar](125) ,
   [Environment] [Nvarchar](50) ,
   [DC] [Nvarchar](50) ,
   [DB Name] [Nvarchar](255) ,
    [DBCreationDate] [datetime],
   [DB Size] [int] ,
     [state] [Nvarchar](50) ,
   [RecoveryModel] [Nvarchar](50) ,
   [OS Version] [Nvarchar](80) ,
   [SQL Version] [Nvarchar](50) ,
   [SPLevel] [Nvarchar](50) ,
   [ProdVersion] [Nvarchar](50) ,
   [Edition] [Nvarchar](50) ,
   [Domain] [Nvarchar](50) ,
   [CPUCores] INT ,
   [Memory] INT ,
   [CollationName] [Nvarchar](50) ,
     [ReportDate] [datetime],
   ReportID BIGINT DEFAULT 0)   
 ON [PRIMARY]
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'tblDatabaseInventory' AND xtype = 'U')
Truncate table tblDatabaseInventory;
GO
"

Invoke-Sqlcmd -ServerInstance $SQLInstanceName 
              -Database $inventoryDB -Query $CreateTableQuery

# End of Step 1 ============ # End of Step 1

# Step 2 Start ========== # Step 2 Start

$server = $env:computername
$SQLInstanceName=$env:computername
$inventoryDB = "SQLInventoryDB"

#Put in your query that returns the list of instances 
 as described in the example result set above
$instances = import-csv $ServersFile
$CurrentDate=0;
$CurrentDate = (Get-Date -Format G).ToString().Replace(':','').
                Replace('/','-').Replace(' ','').Replace('-','')

$GetInstanceDetails ="
SELECT
    SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as HostName,
   SERVERPROPERTY('SERVERNAME') AS 'SQLInstanceName',
   case SERVERPROPERTY('IsClustered')
       when 1 then 'Clustered'
       when 0 then 'Standalone'
   end as 'IsClustered?',
     sys.databases.name AS 'DB Name',
   sys.databases.create_date AS 'DBCreationDate',
     CONVERT(INT,SUM(size)*8.0/1024) AS 'DB Size',
     sys.databases.state_desc AS 'State',
   sys.databases.recovery_model_desc AS 'RecoveryModel',
   (select (substring(@@version,11,15))) as 'SQL Version',
   SERVERPROPERTY('ProductLevel') AS 'SPLevel',
     SERVERPROPERTY('ProductVersion') AS 'ProdVersion',
   SERVERPROPERTY('Edition') AS 'Edition',
   sys.databases.collation_name AS 'CollationName',
     GETDATE() as 'ReportDate'    
     
FROM    sys.databases
JOIN    sys.master_files
ON     sys.databases.database_id = sys.master_files.database_id
GROUP BY  sys.databases.name, sys.databases.state_desc,sys.databases.create_date,
            sys.databases.collation_name,sys.databases.recovery_model_desc
ORDER BY  sys.databases.name
"

#For each instance, fetch the list of databases 
 (along with a couple of useful pieces of information)

foreach ($instance in $instances){

  $DBDetails = Invoke-Sqlcmd -Query $GetInstanceDetails 
                -ServerInstance $instance.SQLInstanceName 
                -ErrorAction SilentlyContinue -querytimeout 60

#Perform an INSERT in the tblDatabaseInventory table only if it returns information

   if($DBDetails.Length -ne 0){
    #Build the insert statement
    $insert = "INSERT INTO tblDatabaseInventory VALUES"
    foreach($rows in $DBDetails){
     $insert+="
     (
     '"+$rows['HostName']+"',
     '"+$rows['IP']+"',
     '"+$rows['SQLInstanceName']+"',
     '"+$rows['isClustered?']+"',
     '"+$rows['ApplicationName']+"',
     '"+$rows['Environment']+"',
     '"+$rows['DC']+"',
     '"+$rows['DB Name']+"',
     '"+$rows['DBCreationDate']+"',
     '"+$rows['DB Size']+"',
     '"+$rows['State']+"',
     '"+$rows['RecoveryModel']+"',
     '"+$rows['OS Version']+"',
     '"+$rows['SQL Version']+"',
     '"+$rows['SPLevel']+"',
     '"+$rows['ProdVersion']+"',
     '"+$rows['Edition']+"',
       '"+$domain['Domain']+"',
       '"+$cpucores['CPUCores']+"',
     '"+$rows['Memory']+"',
     '"+$rows['CollationName']+"',
     '"+$rows['ReportDate']+"',
     '"+$rows['ReportID']+"' 
       ),
     "
    }
    #Store the results in the table created at the beginning of this script
    Invoke-Sqlcmd -Query $insert.Substring(0,$insert.LastIndexOf(',')) 
      -ServerInstance $SQLInstanceName 
      -Database $inventoryDB #-ErrorAction SilentlyContinue
  }
   
$HostName=$rows.HostName

Write-Host "Fetching the data from:" $rows.SQLInstanceName

$Env=$instance.Environment
$AppName=$instance.ApplicationName
$DC=$instance.DC
$IPAddress=Get-WmiObject 
-ComputerName $instance.HostName win32_networkadapterconfiguration | 
where { $_.ipaddress -like "1*" } | select -ExpandProperty ipaddress 
-ErrorAction SilentlyContinue
$domain = Invoke-Command -ComputerName $instance.HostName 
-ScriptBlock {(Get-wmiobject Win32_ComputerSystem).Domain} -ErrorAction SilentlyContinue
$CPUCores = Invoke-Command -ComputerName $instance.HostName 
-ScriptBlock {(Get-CimInstance Win32_ComputerSystem).NumberOfLogicalProcessors} 
-ErrorAction SilentlyContinue
$Memory =Invoke-Command -ComputerName $instance.HostName 
-ScriptBlock {[Math]::Round((Get-CimInstance Win32_ComputerSystem).
TotalPhysicalMemory/1024/1024/1024)} -ErrorAction SilentlyContinue
$OS=Invoke-Command -ComputerName $instance.HostName 
-ScriptBlock {(Get-WmiObject -class Win32_OperatingSystem).Caption} 
-ErrorAction SilentlyContinue

$IPDomainCPUUpdateQuery = "update tblDatabaseInventory set ApplicationName='$AppName', 
Environment='$Env',DC='$DC',[OS Version]='$OS',IP='$IPAddress',domain='$domain',
CPUCores='$CPUCores',Memory='$Memory',ReportID='$CurrentDate' 
where HostName='$($instance.HostName)';"

Invoke-Sqlcmd -Query $IPDomainCPUUpdateQuery -ServerInstance $SQLInstanceName 
-Database $inventoryDB -ErrorAction SilentlyContinue
}

$inputInstanceCount=$instances.Count

$ConnectedInstancesCountQuery="select count(distinct SQLInstanceName) 
from SQLInventoryDB.[dbo].[tblDatabaseInventory] where 
ReportID= (select distinct(reportid) from SQLInventoryDB.[dbo].[tblDatabaseInventory] 
where reportdate=(select max(reportdate) 
from SQLInventoryDB.[dbo].[tblDatabaseInventory]))"

$ConnectedInstancesQuery="select distinct SQLInstanceName 
from SQLInventoryDB.[dbo].[tblDatabaseInventory] 
where ReportID= (select distinct(reportid) 
from SQLInventoryDB.[dbo].[tblDatabaseInventory] 
where reportdate=(select max(reportdate) 
from SQLInventoryDB.[dbo].[tblDatabaseInventory])) order by SQLInstanceName desc "

$ConnectedInstancesCount= Invoke-Sqlcmd -Query $ConnectedInstancesCountQuery 
-ServerInstance $SQLInstanceName -Database $inventoryDB #-ErrorAction SilentlyContinue
 
$ConnectedInstances= Invoke-Sqlcmd -Query $ConnectedInstancesQuery 
-ServerInstance $SQLInstanceName -Database $inventoryDB #-ErrorAction SilentlyContinue

$FinalConnectedInstances=$inputInstanceCount-$ConnectedInstancesCount.Column1

Write-Host "Total no.of instances requested for an inventory:" $inputInstanceCount
Write-Host "Instances connected:" $ConnectedInstancesCount.Column1
Write-Host "Instances unconnected:" $FinalConnectedInstances

Invoke-Sqlcmd -Query "Select * from SQLInventoryDB.dbo.tblDatabaseInventory" 
-ServerInstance $SQLInstanceName | Export-Csv 
-Path "D:\SQLHealthCheck\InventoryScript\Reports\SQLInventoryReport_$($CurrentDate).csv"

# End of Step 2 ================= # End of Step 2

//

Important Notes

Read and understand the instructions from the article and in PS script.

Most of the commands were executed only on Windows Server 2008 and above.

Make sure you copy (open) the script properly, set the word wrap from Format (UNCHECKED) to make sure the single command is not split into multiple lines and leads an error.

To troubleshoot an issue, comment out parameter “-ErrorAction SilentlyContinue” for which an error is generated.

For any assistance and issues, please leave a note in the comments section below.

History

6^th May, 2023: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)