Category Archives: Powershell

New DBA Inventory – Server Disk Info

This is a multi-part series on my newly created DBA Inventory system. The main page with a table of contents to link to all the posts is located here

The previous post discussed Server Details, things like Memory, CPU’s, Cores, BIOS, Operating System details using WMI Calls. This next post is another poll of Server level (Operating System) type detail… Disk Info.

This Powershell “poller” grabs disk info such as the Drive Letter, the Disk Model, the Partition, a Description, if it’s a Primary Partition, the Volume Name, if it’s SAN attached, if it’s a Mount Point, Disk Size, Disk Free Space, and a Serial Number. It stores all of this in a history table by an “Audit Date”. That table is named “DiskInfo”!

CREATE TABLE [dbo].[DiskInfo](
	[ServerName] [varchar](256) NOT NULL,
	[DiskName] [varchar](256) NOT NULL,
	[AuditDt] [datetime] NOT NULL,
	[Model] [varchar](256) NULL,
	[DiskPartition] [varchar](256) NULL,
	[Description] [varchar](256) NULL,
	[PrimaryPartition] [bit] NULL,
	[VolumeName] [varchar](256) NULL,
	[Drive] [varchar](128) NULL,
	[SanAttached] [bit] NULL,
	[MountPoint] [bit] NULL,
	[DiskSizeMB] [bigint] NULL,
	[DIskFreeMB] [bigint] NULL,
	[SerialNumber] [varchar](128) NULL,
 CONSTRAINT [PK_DiskInfo] PRIMARY KEY CLUSTERED 
(
	[ServerName] ASC,
	[DiskName] ASC,
	[AuditDt] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

The powershell itself actually only queries my Helper Database installed on every instance – named DBATools. Each instance has a job that runs nightly to get the local disk information. I had originally worked on a centralized script that polled via WMI but it was slow and cumbersome to run in my environment. This is much faster. So the central polling script is here, and it’s pretty simple.

[void] [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")

if (Get-PSSnapin -Registered -Name SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue )
{
	Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue
	Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinue
	import-module "C:\DBATools\dbatoolsPost2008.psm1"  -ErrorAction SilentlyContinue
}
elseif (Get-Module sqlps -ListAvailable -ErrorAction SilentlyContinue)
{
	Import-Module sqlps #-ErrorAction SilentlyContinue
	import-module "C:\DBATools\dbatoolsPost2008.psm1"  -ErrorAction SilentlyContinue
}	
else
{
	import-module "C:\DBATools\dbatoolsPre2008.psm1"  -ErrorAction SilentlyContinue
}

Import-Module "C:\DBATools\DBATOOLSCommon.psm1"

$dbaToolsServer = "CS-SQL-INFRAMONITORING-US-DEV-001"
$inventoryDB = "Inventory"

Function GetDiskInfo ($serverName, $sourceDB, $altCredential, $runDt)
{

	$DiskInfo = New-Object System.Data.DataTable
	[void]$DiskInfo.Columns.Add("ServerName", "System.String")
	[void]$DiskInfo.Columns.Add("DiskName", "System.String")
	[void]$DiskInfo.Columns.Add("AuditDt", "System.DateTime")
	[void]$DiskInfo.Columns.Add("Model", "System.String")
	[void]$DiskInfo.Columns.Add("DiskPartition", "System.String")
	[void]$DiskInfo.Columns.Add("Description", "System.String")
	[void]$DiskInfo.Columns.Add("PrimaryPartition", "System.Int32")
	[void]$DiskInfo.Columns.Add("VolumeName", "System.String")
	[void]$DiskInfo.Columns.Add("Drive", "System.String")
	[void]$DiskInfo.Columns.Add("SanAttached", "System.Int32")
	[void]$DiskInfo.Columns.Add("MountPoint", "System.Int32")
	[void]$DiskInfo.Columns.Add("DiskSizeMB", "System.String")
	[void]$DiskInfo.Columns.Add("DiskFreeMB", "System.String")
	[void]$DiskInfo.Columns.Add("SerialNumber", "System.String")

	$sqlQuery = "
		SELECT [ServerName]
			  ,[DiskName]
			  ,[AuditDt]
			  ,[Model]
			  ,[DiskPartition]
			  ,[Description]
			  ,[PrimaryPartition]
			  ,[VolumeName]
			  ,[Drive]
			  ,[SanAttached]
			  ,[MountPoint]
			  ,[DiskSizeMB]
			  ,[DIskFreeMB]
			  ,[SerialNumber]
		  FROM  [DiskInfo]
	"

	if ($altCredential -ne $null) {
		write-host "Using Alt Credentials!"

		$user=$altCredential.UserName
		$pass = $altCredential.GetNetworkCredential().Password
		$splat = @{
			UserName = $user
			Password = $pass
			ServerInstance = $serverName
			Database = $sourceDB
			Query = $sqlQuery
		}
		$diskQuery = Invoke-SqlCmd @splat 
	} else {
		$diskQuery = QuerySql $servername $sourceDB $sqlQuery 60000
	}
	foreach ($row in $diskQuery)
	{
			$newRow = $DiskInfo.NewRow()
			$newRow["ServerName"] = $row.Servername
			$newRow["DiskName"] = $row.DiskName
			$newRow["AuditDt"] = [DateTime]$runDt
			$newRow["Model"] = $row.Model
			$newRow["DiskPartition"] = $row.DiskPartition
			$newRow["Description"] = $row.Description
			$newRow["PrimaryPartition"] = $row.PrimaryPartition
			$newRow["VolumeName"] = $row.VolumeName
			$newRow["Drive"] = $row.Drive
			$newRow["SanAttached"] = $row.SanAttached
			$newRow["MountPoint"] = $row.MountPoint
			$newRow["DiskSizeMB"] = $row.DiskSizeMB
			$newRow["DiskFreeMB"] = $row.DiskFreeMB
			$newRow["SerialNumber"] = $row.SerialNumber
			$DiskInfo.Rows.Add($newRow)
	}

	#$diskInfo	

	try
	{
		if ($DiskInfo.Rows.Count -gt 0) {
	
			$cn = new-object System.Data.SqlClient.SqlConnection("Data Source=$dbaToolsServer;Integrated Security=SSPI;Initial Catalog=$inventoryDB");
			$cn.Open()
			$bc = new-object ("System.Data.SqlClient.SqlBulkCopy") $cn
			$bc.DestinationTableName = "DiskInfo"
			$bc.WriteToServer($DiskInfo)
			$cn.Close()
		}
	}
	catch [System.Exception]
	{
		$errVal = $_.Exception.GetType().FullName + " - " + $_.FullyQualifiedErrorID
		Write-Host "Insert Error Error $serverName"
		Write-Host $errVal
		$errCount ++
	}
}


###########################################################################################
#
#		MAIN
#
###########################################################################################

$startDate = get-date
$runDt = Get-Date -format "yyyy-MM-dd HH:00:00"

#Get-ClusteredDiskInfo "NAT317DBS" $runDt
#EXIT

$sqlQuery = @"
		SELECT [InstanceName] = ID.InstanceName, 
			[DBAToolsDB] = DD.DatabaseName,
			[UName] = AC.Uname,
			[PWord] = AC.PWord
		FROM InstanceDetails ID
		LEFT OUTER JOIN DatabaseDetails DD
			ON ID.InstanceName = DD.InstanceName
		LEFT OUTER JOIN [AltSqlCredentials] AC
			ON ID.InstanceName = AC.instancename
			and AC.[SourceServerNetBIOS] = (SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))
		WHERE ID.Retired = 0
		AND ID.LicenseOnly = 0
		AND DD.DatabaseName like 'DBATools%'
"@

$clusteredServers = QuerySql $dbaToolsServer $inventoryDB $sqlQuery 60000

foreach ($instance in $clusteredServers)
{
	$instanceName = $instance[0]
	$sourceDB = $instance[1]
	$user = $instance[2]
	$pass = $instance[3]

	$altCredential = $null
	if (-not(([DBNull]::Value).Equals($user)) ) {
		write-host "Found alt credential for $instanceName"
		$pass = $pass|ConvertTo-SecureString 
		$altCredential = New-Object -TypeName System.Management.Automation.PsCredential `
			-ArgumentList $user, $pass
	}

	$instanceName
	GetDiskInfo $instanceName $sourceDB $altCredential $runDt
}

$endDate = get-date
$sqlQuery = "INSERT INTO MonitoringJobStatus 
		(JOBNAME, STATUS, STARTDT, ENDDT, RANGESTARTDT, RANGEENDDT, ERROR)
		VALUES
		('DISKINFO','C','$startDate', '$endDate', '', '', '')"



QuerySql $dbaToolsServer $inventoryDB $sqlQuery 60000

exit

This query gets the list of instances to poll and also introduces the concept of a SQL Login credential that’s stored as a Secure String to use a SQL Login to connect rather than the AD Credential (for servers not joined to the domain).

$sqlQuery = @"
		SELECT [InstanceName] = ID.InstanceName, 
			[DBAToolsDB] = DD.DatabaseName,
			[UName] = AC.Uname,
			[PWord] = AC.PWord
		FROM InstanceDetails ID
		LEFT OUTER JOIN DatabaseDetails DD
			ON ID.InstanceName = DD.InstanceName
		LEFT OUTER JOIN [AltSqlCredentials] AC
			ON ID.InstanceName = AC.instancename
			and AC.[SourceServerNetBIOS] = (SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))
		WHERE ID.Retired = 0
		AND ID.LicenseOnly = 0
		AND DD.DatabaseName like 'DBATools%'
"@

The function GetDiskInfo just creates a new data table, queries the local DBATools..DiskInfo table and uses the credentials if they exist. It then bulk uploads that data into the Inventory table.

The local DiskInfo table is identical, its just in the DBATools database on every instance. So let’s look at the Powershell script that’s on each local server as part of the DBATools installation.

#
#	GetDiskSanInfo.ps1
#	
#	This script determines for SAN that is attached whether 
#	it is a mount point and whether it is replicated.
#
#	DiskExt.exe is used to determine which physical disk a mount point resides on.
#	inq.wnt.exe is used to determine whether a given SAN volume is replicated.
#
#	The dbo.DiskInfo table in DBATools is updated with this information. From
#	there the DB Inventory service will read it and update the DBInv database.
#

if (Get-PSSnapin -Registered -Name SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue )
{
	Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue
	Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinue
	import-module "C:\DBATools\dbatoolsPost2008.psm1"  -ErrorAction SilentlyContinue
}
elseif (Get-Module sqlps -ListAvailable -ErrorAction SilentlyContinue)
{
	Import-Module sqlps -ErrorAction SilentlyContinue
	import-module "C:\DBATools\dbatoolsPost2008.psm1"  -ErrorAction SilentlyContinue
}	
else
{
	import-module "C:\DBATools\dbatoolsPre2008.psm1"  -ErrorAction SilentlyContinue
}
Import-Module "C:\DBATools\DBATOOLSCommon.psm1"

$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition

$dbaToolsServer = "CS-SQL-INFRAMONITORING-US-DEV-001"
$SQLName = $args[0]
if ($SQLName -ne $null)
{ $dbaToolsServer = $SQLName }

if (Get-Module FailoverClusters -ListAvailable -ErrorAction SilentlyContinue)
{  
	Import-Module FailoverClusters	
	$clusterName = GET-Cluster
	$physicalName = $clusterName.Name.toUpper()
} else {
	$physicalName = (hostname).toUpper()
}


$runDt = Get-Date -format "MM/dd/yyyy HH:00:00"

# this is the table that will hold the data during execution
$serverDisk = New-Object System.Data.DataTable
[void]$serverDisk.Columns.Add("ServerName", "System.String")
[void]$serverDisk.Columns.Add("DiskName", "System.String")
[void]$serverDisk.Columns.Add("AuditDt", "System.DateTime")
[void]$serverDisk.Columns.Add("Model", "System.String")
[void]$serverDisk.Columns.Add("DiskPartition", "System.String")
[void]$serverDisk.Columns.Add("Description", "System.String")
[void]$serverDisk.Columns.Add("PrimaryPartition", "System.Boolean")
[void]$serverDisk.Columns.Add("VolumeName", "System.String")
[void]$serverDisk.Columns.Add("Drive", "System.String")
[void]$serverDisk.Columns.Add("SanAttached", "System.Boolean")
[void]$serverDisk.Columns.Add("MountPoint", "System.Boolean")
[void]$serverDisk.Columns.Add("DiskSizeMB", "System.Int64")
[void]$serverDisk.Columns.Add("DiskFreeMB", "System.Int64")
[void]$serverDisk.Columns.Add("SerialNumber", "System.String")
                
# WMI data
$wmi_diskdrives = Get-WmiObject -Class Win32_DiskDrive
$wmi_mountpoints = Get-WmiObject -Class Win32_Volume -Filter "DriveType=3 AND DriveLetter IS NULL AND NOT Name like '\\\\?\\Volume%'" | Select $WMI_DiskMountProps
                
$AllDisks = @()
$DiskElements = @('ComputerName','Disk','Model','Partition','Description','PrimaryPartition','VolumeName','Drive', 'SanAttached', 'MountPoint', 'DiskSize','FreeSpace','DiskType','SerialNumber')
                foreach ($diskdrive in $wmi_diskdrives) 
                {
                    $partitionquery = "ASSOCIATORS OF {Win32_DiskDrive.DeviceID=`"$($diskdrive.DeviceID.replace('\','\\'))`"} WHERE AssocClass = Win32_DiskDriveToDiskPartition"
                    $partitions = @(Get-WmiObject -Query $partitionquery)
                    foreach ($partition in $partitions)
                    {
                        $logicaldiskquery = "ASSOCIATORS OF {Win32_DiskPartition.DeviceID=`"$($partition.DeviceID)`"} WHERE AssocClass = Win32_LogicalDiskToPartition"
                        $logicaldisks = @(Get-WmiObject -Query $logicaldiskquery)
                        foreach ($logicaldisk in $logicaldisks)
                        {
				$newRow = $serverDisk.NewRow()
				$newRow["ServerName"] = $physicalName
				$newRow["DiskName"] = $logicalDisk.Name
				$newRow["AuditDt"] = [DateTime]$runDt
				$newRow["Model"] = $diskDrive.Model
				$newRow["DiskPartition"] = $partition.Name
				$newRow["Description"] = $partition.Description
				$newRow["PrimaryPartition"] = $partition.PrimaryPartition
				$newRow["VolumeName"] = $logicalDisk.volumeName
				$newRow["Drive"] = $diskDrive.Name
				$newRow["SanAttached"] = if ($diskdrive.Model.StartsWith("NETAPP")) { $TRUE } else { $FALSE }
				$newRow["MountPoint"] = $FALSE
				$newRow["DiskSizeMB"] = [math]::truncate($logicalDisk.Size / 1MB)
				$newRow["DiskFreeMB"] = [math]::truncate($logicalDisk.FreeSpace / 1MB)
				$newRow["SerialNumber"] = $diskDrive.serialNumber
				$serverDisk.Rows.Add($newRow)
                        }
                    }
                }
                # Mountpoints are weird so we do them seperate.
                if ($wmi_mountpoints)
                {
                    foreach ($mountpoint in $wmi_mountpoints)
                    {
				$newRow = $serverDisk.NewRow()
				$newRow["ServerName"] = $physicalName
				$newRow["DiskName"] = $mountpoint.Name
				$newRow["AuditDt"] = [DateTime]$runDt
				$newRow["Model"] = 'Mount Point'
				$newRow["DiskPartition"] = $DBNULL
				$newRow["Description"] = $mountpoint.Caption
				$newRow["PrimaryPartition"] = $FALSE
				$newRow["VolumeName"] = $mountpoint.Caption
				$newRow["Drive"] = [Regex]::Match($mountpoint.Caption, "(^.:)").Value
				$newRow["SanAttached"] = $TRUE
				$newRow["MountPoint"] = $TRUE
				$newRow["DiskSizeMB"] = [math]::truncate($mountpoint.Capacity / 1MB)
				$newRow["DiskFreeMB"] = [math]::truncate($mountpoint.FreeSpace / 1MB)
				$newRow["SerialNumber"] = $DBNULL
				$serverDisk.Rows.Add($newRow)
                    }
                }

$sqlCmd = "Select name from sys.databases where name like 'DBATools%'"
$dbatoolsDBName = QuerySql  $dbaToolsServer "master" $sqlCmd 

$DBAToolsDB = $dbatoolsDBName[0]

$sqlCmd = "TRUNCATE TABLE dbo.DiskInfo"
QuerySql $dbaToolsServer $DBAToolsDB $sqlCmd 

# create the connection for the bulk loads
$connString = "Data Source=$dbaToolsServer; Initial Catalog=$DBAToolsDB; Integrated Security=True; Application Name=GetSanDiskInfo.ps1; "
$sqlConn = New-Object System.Data.SqlClient.SqlConnection $connString
$sqlConn.Open()
try
{
	# upload the table to DBATools
	$bulkCopy = New-Object System.Data.SqlClient.SqlBulkCopy($sqlConn)
	$bulkCopy.DestinationTableName = "dbo.DiskInfo"
	$bulkCopy.WriteToServer($serverDisk)
}
catch [System.Exception]
{
	$errVal = $_.Exception.GetType().FullName + " - " + $_.FullyQualifiedErrorID
	Write-Host "Bulkcopy Error"
	Write-Host $errVal
	$errCount ++
}

Very similar as it creates a data table for each disk row. It also imports FailoverClusters to get the cluster name (in the case of a cluster, it stores all the disks under the Cluster Name) It then queries via WMI out to WIN32_DiskDrive and then to Win32_Volume. The filter on the Win32_Volume limits to just mount points via -Filter “DriveType=3 AND DriveLetter IS NULL AND NOT Name like ‘\\\\?\\Volume%'”.

It then uses some WMI Magic of “ASSOCIATORS OF {Win32_DiskDrive} by device ID” to get partitions, and another “ASSOCIATORS OF {Win32_DiskPartition} by device ID” to get the logical disks. This is the part that really slowed down running this from a centralized location, and forcing me to run it locally on each server.

I do some assumptions here, with the SanAttached value, if the Drive model starts with “NETAPP”, I assume it’s SAN Attached. I only have Netapp arrays in the company, so I can make that assumption. I’m sure there would be additional models, for EMC, 3Par, Dell, etc… but this works for my environment today.

I do a separate loop to load Mountpoints into the data table, and always assume they are SAN Attached.

So, if you’re astute, you’ll notice or think to yourself, if I have multiple instances on the same server, each of those DBATools databases will have a DiskInfo table and the same information in it. And then my centralized polling powershell will pull those and load them into the Inventory database…. but that table has a Primary Key on the ServerName, DiskName, and AuditDt. And you’re right it is the physical server name, or in the case of a cluster, the Virtual Computer Name of the Cluster. What I do is discard the duplicates – who needs them? Why write a bunch of logic to check for it, and not poll it, or whatnot, let the Constraint of the Clustered Primary Key do the work and disallow dupes. For example, I have one two node “active/passive” cluster that has seven instances on it. Each instance has a DBATools database, and each instance runs the local powershell script and loads the disk info for the cluster into each of the seven DBATools databases. The centralized polling script connects to each of those seven instances and attempts to load the same data seven times. It discards six of them – big deal, as long as I get one that’s all that matters…. I now have a list of all disks, volumes, mount points, total space and free space for every server that I have in my Inventory, and I have it every day. I can now trend growth rate at the server / disk layer over time.

Here’s the sample data from a single cluster of mine, with six instances on it (I group instances on a drive letter with three mountpoints under it, one for User Database Datafiles, one for User Database Tran Logs, and one for System DB’s / instance root). Just think of the trending and PowerBI charts you could make with a year’s worth of data from every SQL Server in your environment!

Powershell script to capture OS level properties (CPU, Cores, NIC, Memory, BIOS, OS)

I have a master post which links to a set of powershell scripts all related to this. The Master post which links to all the scripts.

This script is an updated and streamlined version of this original and ugly script

This version actually dumps out to a CSV via the “Export-CSV” command-let. It has some error handling and will check the first WMI call and skip the rest if the first one can’t connect. It has a friendly output to the screen so the user knows which server is currently being queried.

$serverList = "..\SQL Servers.txt"
#$serverList = "test.txt"
[System.Collections.ArrayList]$sysCollection = New-Object System.Collections.ArrayList($null)

foreach ($server in (Get-Content $serverList)) 
{ 
    "Collecting information from $server"
    [System.Collections.Specialized.OrderedDictionary]$si = @{}    # Blank hashtable for each iteration
    $totCores=0

    $si.Server=[string]$server;

    try
    {
        [wmi]$sysInfo = get-wmiobject Win32_ComputerSystem -Namespace "root\CIMV2" -ComputerName $server -ErrorAction Stop
        [wmi]$bios = Get-WmiObject Win32_BIOS -Namespace "root\CIMV2" -computername $server
        [wmi]$os = Get-WmiObject Win32_OperatingSystem -Namespace "root\CIMV2" -Computername $server
        [array]$procs = Get-WmiObject Win32_Processor -Namespace "root\CIMV2" -Computername $server
        [array]$mem = Get-WmiObject Win32_PhysicalMemory -Namespace "root\CIMV2" -ComputerName $server
        [array]$nic = Get-WmiObject Win32_NetworkAdapterConfiguration -Namespace "root\CIMV2" -ComputerName $server | where{$_.IPEnabled -eq "True"}


        $si.Manufacturer=[string]$sysInfo.Manufacturer;
        $si.Model=[string]$sysInfo.Model;
        $si.TotMem=[string]$sysInfo.TotalPhysicalMemory;
        $si.BiosDesc=[string]$bios.Description;
        $si.BiosVer=[string]$bios.SMBIOSBIOSVersion+"."+$bios.SMBIOSMajorVersion+"."+$bios.SMBIOSMinorVersion;
        $si.BiosSerial=[string]$bios.SerialNumber;
        $si.OSName=[string]$os.Name.Substring(0,$os.Name.IndexOf("|") -1);
        $si.Arch=[string]$os.OSArchitecture;
        $si.Processors=[string]@($procs).count;
        $si.Cores=[string]$procs[0].NumberOfCores;
        $iter=0
        while ($iter -lt 12)   #Parse through each stick of memory (arbitrary 12 Memory stick columns)
        {
            if ($mem[$iter] -ne $null) {
                $si."MemBank$iter" = [string]$mem[$iter].Capacity + "," + [string]$mem[$iter].BankLabel 
            } else {
                $si."MemBank$iter" = ""
            }
            $iter++
        }
        $iter=0
        while ($iter -lt 4)    #Parse through each NIC (arbritrary 4 Network Card columns)
        {
            $si."DHCP$iter"=[string]$nic[$iter].DHCPEnabled;
            $si."IPAddress$iter"=[string]$nic[$iter].IPAddress;
            $si."Subnet$iter"=[string]$nic[$iter].IPSubnet;
            $si."Gateway$iter"=[string]$nic[$iter].DefaultIPGateway;
            $si."Mac$iter"=[string]$nic[$iter].MacAddress;
            $iter++
        }
    }
    catch [Exception]
    {
        "Could not contact $server, skipping to next"
        Continue
    }
    finally
    {
        [void]$sysCollection.Add((New-Object PSObject -Property $si))    #Add the dictionary list to the collection
    }
}

$sysCollection | Export-CSV -path ServerInventory.csv -NoTypeInformation    #Output the collection to a CSV file

Powershell scripts to document SQL Servers

Starting at a new company as their first really dedicated SQL Server DBA, I inherited over 20 production SQL Servers. These systems had varying levels of documentation from little to none, so I’ve had a painful time of it just getting up to speed on the current environment. I’ve read a number of articles from the masters, including Brent Ozar. I’ve used his SQL Blitz scripts, and his new sp_blitz procedure to gain knowledge of existing systems. Building on that, since I’m as lazy as any other good DBA, I’ve created – and by created, I really mean that I modified – some existing powershell scripts to gather information about servers or SQL Server and dump that information into Excel. I plan on running these scripts on a monthly basis to keep them current. I’ll add to them, modify them, and create new ones as I see fit. My real thoughts are that I can use this information in the event of a disaster, and rebuild a server from backups without having to memorize information such as logins, data file locations, sql server configuration options, etc.

This is the master post, and here are the links to each of the posts that I have created so far.

The original script which started all my work. Database properties
SQL Logins
SQL Server configuration settings
Database file settings
!!!Updated OS / Hardware level settings!!!
OS / hardware level settings

Powershell to capture OS / hardware level settings

This is part 4 of a multipart posting on some PowerShell scripts that I’ve found and made some modifications to. These scripts I have used to document my environment without having to manually type information in to the spreadsheets. The master which links to all the scripts is here.

— Update, I have updated this powershell script with a much easier to read and spits out a CSV that can be opened right in Excel without any manipulation. —

The new version of this script is now here

This script gathers information about the server that SQL Server is running on. This one is unique from the rest, as it connects not to SQL Server at all, but rather to WMI to run queries to capture things like the number of CPUs and Cores (think SQL Server 2012 licensing!), memory configuration, OS, IP’s and other network info. This script also expects a text file with a list of servers, I have a file called PhysicalServers.txt that has a list of hostnames. I redirect the output of this script to a csv file, and then import that data into a excel spreadsheet. This one has only a single worksheet, one row for each server in the text file.

The base of this script came from Jesse Hamrick and his awesome site of Powershell Knowledge. Update 5/20/2016 – it seems the site I originally referenced is compromised now with malware. I have removed the link to the website.

 #* Connect to computer
 


 $serverList = "PhysicalServers.txt"
 foreach ($server in (Get-Content $serverList)) 
 {   
    $strComputer = $server
 
    #  "SysInfo Information"
    $myOutput = $strComputer + ","
    $colItems = Get-WmiObject Win32_ComputerSystem -Namespace "root\CIMV2" `
     -ComputerName $strComputer
     

     
    foreach($objItem in $colItems) {
      $myOutput += [string]$objItem.Manufacturer + ","
      $myOutput += [string]$objItem.Model + ","
      $myOutput += [string]$objItem.TotalPhysicalMemory + " bytes,"
     }
     
     
    #  "System BIOS Information"
    $colItems = Get-WmiObject Win32_BIOS -Namespace "root\CIMV2" -computername $strComputer
     foreach($objItem in $colItems) {
      $myOutput += [string]$objItem.Description + ","
      $myOutput += [string]$objItem.SMBIOSBIOSVersion + "." + $objItem.SMBIOSMajorVersion + "." + $objItem.SMBIOSMinorVersion + ","
      $myOutput += [string]$objItem.SerialNumber + ","
     }
     
    #  "Operating System Information"
    $colItems = Get-WmiObject Win32_OperatingSystem -Namespace "root\CIMV2"`
     -Computername $strComputer
     
    foreach($objItem in $colItems) {
        $subOSName = $objItem.Name.IndexOf("|") -1
      $myOutput += $objItem.Name.Substring(0,$subOSName) + ","
      $myOutput += [string]$objItem.OSArchitecture + ","
     }
     
     # "Processor Information"
    $colItems = Get-WmiObject Win32_Processor -Namespace "root\CIMV2" `
     -Computername $strComputer
     
     $count=0
     $temp=""
     
    foreach($objItem in $colItems) {
      $count++
      $temp = $objItem.Name + "," + $count + "," + $objItem.NumberOfCores
     }
     
     $myOutput += [string]$temp + ","
    
    # "Memory Information"
    $colItems = Get-WmiObject Win32_PhysicalMemory -Namespace "root\CIMV2" `
     -ComputerName $strComputer 
     $iter=1
    foreach($objItem in $colItems) {
      $iter++
      $myOutput += [string]$objItem.Capacity + ","
      $myOutput += [string]$objItem.BankLabel + ","
     }
     
     # Fill out the remaining memory slots with blanks (in my environment, the biggest server I have has 12 slots, so that's the max)
     while($iter -lt 13)
     {
        $myOutput += [string]",,"
        $iter++
     }
         
    #  "Network Information"
    $colItems = Get-WmiObject Win32_NetworkAdapterConfiguration -Namespace "root\CIMV2" `
     -ComputerName $strComputer | where{$_.IPEnabled -eq "True"}
     
    foreach($objItem in $colItems) {
      $myOutput += [string]$objItem.DHCPEnabled + ","
      $myOutput += [string]$objItem.IPAddress + ","
      $myOutput += [string]$objItem.IPSubnet + ","
      $myOutput += [string]$objItem.DefaultIPGateway + ","
      $myOutput += [string]$objItem.MACAddress
     }
         
$myOutput    
    
}

Powershell to document Database file settings

This is part 3 of a multipart posting on some PowerShell scripts that I’ve found and made some modifications to. These scripts I have used to document my environment without having to manually type information in to the spreadsheets. The master which links to all the scripts is here.

This script gathers the data and log file settings for each database. It also expects a flat file named Servers.txt with a list of SQL Servers to connect to. It will connect via SSPI, so make sure you run it with a Windows account with the appropriate permissions on each SQL Server.

#change these settings to your environment
$Filename='DatabaseFiles'
#these are actually User ODBC DSNs DSNs. Better for Excel.

$serverList = "Servers.txt"

# constants.
$xlCenter=-4108
$xlTop=-4160
$xlOpenXMLWorkbook=[int]51
# and we put the queries in here
$SQL=@"
SELECT DB_NAME(mf.[database_id])AS [Database Name], 
        suser_sname(d.owner_sid) AS [Owner], 
		mf.STATE_DESC AS [Status],
       mf.[file_id] AS [File ID], 
       mf.name AS [File Name], 
       mf.physical_name AS [Physical Location], 
       mf.type_desc AS [File Type],
       CONVERT( bigint, mf.size/128.0) AS [Total Size in MB]
FROM sys.master_files mf
inner join sys.databases d
on d.database_id = mf.database_id
ORDER BY DB_NAME(mf.[database_id]);
"@

# before we do anything else, are we likely to be able to save the file?
# if the directory doesn't exist, then create it
#if (!(Test-Path -path "$DirectoryToSaveTo")) #create it if not existing
#  {
#  New-Item "$DirectoryToSaveTo" -type directory | out-null
#  }
$excel = New-Object -Com Excel.Application #open a new instance of Excel
$excel.Visible = $True #make it visible (for debugging more than anything)
$wb = $Excel.Workbooks.Add() #create a workbook
$currentWorksheet=1 #there are three open worksheets you can fill up
foreach ($server in (Get-Content $serverList)) 
	{ #only create the worksheet if necessary
	if ($currentWorksheet-lt 4) {$ws = $wb.Worksheets.Item($currentWorksheet)}
	else  {$ws = $wb.Worksheets.Add()} #add if it doesn't exist
	$currentWorksheet += 1 #keep a tally
	#if  ($server.version -eq 2000) {$SQL=$SQL2000} #get the right SQL Script
	#if  ($server.version -eq 2005) {$SQL=$SQL2005} 
	#if  ($server.version -eq 2008) {$SQL=$SQL2008}
	$currentName=$server.Replace("\","_") # and name the worksheet
	$ws.name=$currentName # so it appears in the tab 
	# note we create the query so that the user can run it to refresh it
    
    $myconnectionstring = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=master;Data Source=$server"  
    $adOpenStatic = 3 
    $adLockOptimistic = 3  
    
    $objConnection = New-Object -comobject ADODB.Connection 
    $objRecordset = New-Object -comobject ADODB.Recordset  
    
    $objRecordset.CursorLocation = 3
    
    $objConnection.Open($myconnectionstring) 
    $objRecordset.Open($SQL,$objConnection,$adOpenStatic,$adLockOptimistic)  
    
    $objRecordset.MoveFirst()
    
    $qt = $ws.QueryTables.Add($objRecordset,$ws.Range("A1"))
	# and execute it
	if ($qt.Refresh())#if the routine works OK
		{
	              $ws.Activate()
		$ws.Select()
		$excel.Rows.Item(1).HorizontalAlignment = $xlCenter
		$excel.Rows.Item(1).VerticalAlignment = $xlTop
		$excel.Rows.Item(1).Orientation = -90
		#$excel.Columns.Item("F:AH").NumberFormat = "[Red][=0]û;[Blue][=1]ü"
		#$excel.Columns.Item("F:AH").Font.Name = "Wingdings" 
		#$excel.Columns.Item("F:AH").Font.Size = 12
		$excel.Rows.Item("1:1").Font.Name = "Calibri"
		$excel.Rows.Item("1:1").Font.Size = 9
		$excel.Rows.Item("1:1").Font.Bold = $true
		$Excel.Columns.Item(1).Font.Bold = $true
        $Excel.Columns.Item("A:H").Font.Size = 9
        [void] $Excel.Columns.AutoFit()
		}

	}
$filename=$filename -replace  '[\\\/\:\.]',' ' #remove characters that can cause problems
$filename = "$filename.xlsx" #save it according to its title
if (test-path $filename ) { rm $filename } #delete the file if it already exists
$wb.SaveAs($filename,  $xlOpenXMLWorkbook) #save as an XML Workbook (xslx)
$wb.Saved = $True #flag it as being saved
$objRecordset.Close() 
$objConnection.Close() 
$wb.Close() #close the document
$Excel.Quit() #and the instance of Excel
$wb = $Null #set all variables that point to Excel objects to null
$ws = $Null #makes sure Excel deflates
$Excel=$Null #let the air out
# Hristo Deshev's Excel trick 'Pro Windows Powershell' p380
[GC]::Collect()

Powershell to document SQL Server configuration settings

This is part 2 of a multipart posting on some PowerShell scripts that I’ve found and made some modifications to. These scripts I have used to document my environment without having to manually type information in to the spreadsheets. The master which links to all the scripts is here.

This script gathers the information from sys.configurations. It also expects a flat file named Servers.txt with a list of SQL Servers to connect to. It will connect via SSPI, so make sure you run it with a Windows account with the appropriate permissions on each SQL Server.

#change these settings to your environment
$Filename='SQLServerProperties'
#these are actually User ODBC DSNs DSNs. Better for Excel.

$serverList = "Servers.txt"

# constants.
$xlCenter=-4108
$xlTop=-4160
$xlOpenXMLWorkbook=[int]51
# and we put the queries in here
$SQL=@"
SELECT name [Name], 
    value as [Configured Value], 
    value_in_use AS [Running Value], 
    minimum AS [MIN VALUE], 
    maximum AS [MAX VALUE], 
    CASE WHEN CAST(CAST(Value_in_Use as int) - CAST(value as int) as tinyint) = 0 THEN 1
	ELSE 0 
	END
	AS [RUNNING AS CONFIGURED],
    cast(is_dynamic as tinyint) AS [Dynamic], 
    cast(is_advanced as tinyint) AS [Advanced],
    description AS [Description],
    configuration_id
FROM    sys.configurations 
order by name;
"@


# before we do anything else, are we likely to be able to save the file?
# if the directory doesn't exist, then create it
#if (!(Test-Path -path "$DirectoryToSaveTo")) #create it if not existing
#  {
#  New-Item "$DirectoryToSaveTo" -type directory | out-null
#  }
$excel = New-Object -Com Excel.Application #open a new instance of Excel
$excel.Visible = $True #make it visible (for debugging more than anything)
$wb = $Excel.Workbooks.Add() #create a workbook
$currentWorksheet=1 #there are three open worksheets you can fill up
foreach ($server in (Get-Content $serverList)) 
	{ #only create the worksheet if necessary
	if ($currentWorksheet-lt 4) {$ws = $wb.Worksheets.Item($currentWorksheet)}
	else  {$ws = $wb.Worksheets.Add()} #add if it doesn't exist
	$currentWorksheet += 1 
	$currentName=$server.Replace("\","_") # and name the worksheet
	$ws.name=$currentName # so it appears in the tab 
	# note we create the query so that the user can run it to refresh it
    
    $myconnectionstring = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=master;Data Source=$server"  
    $adOpenStatic = 3 
    $adLockOptimistic = 3  
    
    $objConnection = New-Object -comobject ADODB.Connection 
    $objRecordset = New-Object -comobject ADODB.Recordset  
    
    $objRecordset.CursorLocation = 3
    
    $objConnection.Open($myconnectionstring) 
    $objRecordset.Open($SQL,$objConnection,$adOpenStatic,$adLockOptimistic)  
    
    $objRecordset.MoveFirst()
    
    $qt = $ws.QueryTables.Add($objRecordset,$ws.Range("A1"))
	# and execute it
	if ($qt.Refresh())#if the routine works OK
		{
	              $ws.Activate()
		$ws.Select()
		$excel.Rows.Item(1).HorizontalAlignment = $xlCenter
		$excel.Rows.Item(1).VerticalAlignment = $xlTop
		$excel.Rows.Item(1).Orientation = -90
		$excel.Columns.Item("F:H").NumberFormat = "[Red][=0]û;[Blue][=1]ü"
		$excel.Columns.Item("F:H").Font.Name = "Wingdings" 
		$excel.Columns.Item("F:H").Font.Size = 12
		$excel.Rows.Item("1:1").Font.Name = "Calibri"
		$excel.Rows.Item("1:1").Font.Size = 9
		$excel.Rows.Item("1:1").Font.Bold = $true
		$excel.Columns.Item(1).Font.Bold = $true
        $Excel.Columns.Item("A:I").Font.Size = 9
        [void] $Excel.Columns.AutoFit()
		}

	}
$filename=$filename -replace  '[\\\/\:\.]',' ' #remove characters that can cause problems
$filename = "$filename.xlsx" #save it according to its title
if (test-path $filename ) { rm $filename } #delete the file if it already exists
$wb.SaveAs($filename,  $xlOpenXMLWorkbook) #save as an XML Workbook (xslx)
$wb.Saved = $True #flag it as being saved
$objRecordset.Close() 
$objConnection.Close() 
$wb.Close() #close the document
$Excel.Quit() #and the instance of Excel
$wb = $Null #set all variables that point to Excel objects to null
$ws = $Null #makes sure Excel deflates
$Excel=$Null #let the air out
# Hristo Deshev's Excel trick 'Pro Windows Powershell' p380
[GC]::Collect()

Powershell to document SQL Logins

This is part 1 of a multipart posting on some PowerShell scripts that I’ve found and made some modifications to. These scripts I have used to document my environment without having to manually type information in to the spreadsheets. The master which links to all the scripts is here.

This script gathers Login information and membership in the built in roles. It also expects a flat file named Servers.txt with a list of SQL Servers to connect to. It will connect via SSPI, so make sure you run it with a Windows account with the appropriate permissions on each SQL Server.

#change these settings to your environment
$Filename='SQLLoginInformation'
#these are actually User ODBC DSNs DSNs. Better for Excel.

$serverList = "Servers.txt"

# constants.
$xlCenter=-4108
$xlTop=-4160
$xlUp=-4162
$xlSolid=1
$xlAutomatic=-4105
$xlOpenXMLWorkbook=[int]51
# and we put the queries in here
$SQL=@"
select 
	name AS [Login],
	dbname AS [Default Database],
	createdate AS [Create Date],
	updatedate  AS [Update Date],
	cast(hasaccess as tinyint) AS [Access],
	cast(denylogin as tinyint) AS [Deny Access],
	cast(isntname as tinyint) AS [Windows Name],
	cast(isntgroup as tinyint) AS [Windows Group],
	cast(isntuser as tinyint) AS [Windows User],
	cast(sysadmin as tinyint) AS [System Admin],
	cast(securityadmin as tinyint) AS [Security Admin],
	cast(serveradmin as tinyint) AS [Server Admin],
	cast(setupadmin as tinyint) AS [Setup Admin],
	cast(processadmin as tinyint) AS [Process Admin],
	cast(diskadmin as tinyint) AS [Disk Admin],
	cast(dbcreator as tinyint) AS [Database Creator],
	cast(bulkadmin as tinyint) AS [Bulk Admin], 
	totcpu AS [CPU Governed],
	totio AS [IO Governed],
	spacelimit AS [Space Governed],
	timelimit AS [Time Governed],
	resultlimit AS [Resultset Governed]
 from sys.syslogins
 order by name
"@


# before we do anything else, are we likely to be able to save the file?
# if the directory doesn't exist, then create it
#if (!(Test-Path -path "$DirectoryToSaveTo")) #create it if not existing
#  {
#  New-Item "$DirectoryToSaveTo" -type directory | out-null
#  }
$excel = New-Object -Com Excel.Application #open a new instance of Excel
$excel.Visible = $True #make it visible (for debugging more than anything)
$wb = $Excel.Workbooks.Add() #create a workbook
$currentWorksheet=1 #there are three open worksheets you can fill up
foreach ($server in (Get-Content $serverList)) 
	{ #only create the worksheet if necessary
	if ($currentWorksheet-lt 4) {$ws = $wb.Worksheets.Item($currentWorksheet)}
	else  {$ws = $wb.Worksheets.Add()} #add if it doesn't exist
	$currentWorksheet += 1 
	$currentName=$server.Replace("\","_") # and name the worksheet
	$ws.name=$currentName # so it appears in the tab 
	# note we create the query so that the user can run it to refresh it
    
    $myconnectionstring = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=master;Data Source=$server"  
    $adOpenStatic = 3 
    $adLockOptimistic = 3  
    
    $objConnection = New-Object -comobject ADODB.Connection 
    $objRecordset = New-Object -comobject ADODB.Recordset  
    
    $objRecordset.CursorLocation = 3
    
    $objConnection.Open($myconnectionstring) 
    $objRecordset.Open($SQL,$objConnection,$adOpenStatic,$adLockOptimistic)  
    
    $objRecordset.MoveFirst()
    
    $rows = $objRecordset.RecordCount+1
    
    $qt = $ws.QueryTables.Add($objRecordset,$ws.Range("A1"))
	# and execute it
	if ($qt.Refresh())#if the routine works OK
		{
	              $ws.Activate()
		$ws.Select()
		$excel.Rows.Item(1).HorizontalAlignment = $xlCenter
		$excel.Rows.Item(1).VerticalAlignment = $xlTop
		$excel.Rows.Item(1).Orientation = -90
		$excel.Columns.Item("E:I").NumberFormat = "[Red][=0]û;[Blue][=1]ü"
        $excel.Columns.Item("J:M").NumberFormat = "[Green][=0]û;[Red][=1]«"
        $excel.Columns.Item("N:Q").NumberFormat = "[Red][=0]û;[Blue][=1]ü"
        # Color the columns below to highlight the superuser permissions
        $excel.Range("J2","M"+$rows).Interior.Pattern=$xlSolid
        $excel.Range("J2","M"+$rows).Interior.PatternColorIndex=$xlAutomatic
        $excel.Range("J2","M"+$rows).Interior.Color=12320247
        $excel.Range("J2","M"+$rows).Interior.TintAndShade = 0
      
		$excel.Columns.Item("E:Q").Font.Name = "Wingdings" 
		$excel.Columns.Item("E:Q").Font.Size = 12
		$excel.Rows.Item("1:1").Font.Name = "Calibri"
		$excel.Rows.Item("1:1").Font.Size = 9
		$excel.Rows.Item("1:1").Font.Bold = $true
		$excel.Columns.Item(1).Font.Bold = $true
        $Excel.Columns.Item("A:Q").Font.Size = 9
        [void] $Excel.Columns.AutoFit()
		}

	}
$filename=$filename -replace  '[\\\/\:\.]',' ' #remove characters that can cause problems
$filename = "$filename.xlsx" #save it according to its title
if (test-path $filename ) { rm $filename } #delete the file if it already exists
$wb.SaveAs($filename,  $xlOpenXMLWorkbook) #save as an XML Workbook (xslx)
$wb.Saved = $True #flag it as being saved
$objRecordset.Close() 
$objConnection.Close() 
$wb.Close() #close the document
$Excel.Quit() #and the instance of Excel
$wb = $Null #set all variables that point to Excel objects to null
$ws = $Null #makes sure Excel deflates
$Excel=$Null #let the air out
# Hristo Deshev's Excel trick 'Pro Windows Powershell' p380
[GC]::Collect()

Powershell script to gather database level properties into Excel file

I have a master post which links to a set of powershell scripts all related to this. The master which links to all the scripts is here.

While exploring some SQL blogs, I ran across an interesting script from Brad McGehee at Simple-Talk.com. The original source is here.
What I wanted to do was modify it to use a flat file text list of servers, rather than DSN’s. I have over 30 production instances in flat domain with an external domain that trusts the internal domain, so I can use SSPI to connect to each.

Here’s my version using ADO.NET connections. Since I have only a single remaining SQL 2000 instance, and only a single 2005 instance, I took out the logic of the query version, focusing only on the SQL 2008 query without worrying about the Compression as I do not use that at all. I also added some additional information that I thought was relevant such as Recovery model desc, compatibility level, collation, and a couple others.

#change these settings to your environment
$Filename='DatabaseProperties'
#these are actually User ODBC DSNs DSNs. Better for Excel.

$serverList = "Servers.txt"

# constants.
$xlCenter=-4108
$xlTop=-4160
$xlOpenXMLWorkbook=[int]51
# and we put the queries in here
$SQL=@"
SELECT  name AS 'Database_Name' ,
        user_access_desc AS 'Restrict Access' ,
		recovery_model_desc AS 'Recovery Model',
		compatibility_level AS 'Compatibility Level',
        page_verify_option_desc AS 'Page Verify' ,
        snapshot_isolation_state AS 'Allow Snapshot Isolation' ,
        cast(is_ansi_null_default_on as tinyint) AS 'ANSI NULL Default' ,
        cast(is_ansi_nulls_on as tinyint) AS 'ANSI NULLS Enabled' ,
        cast(is_ansi_padding_on as tinyint) AS 'ANSI Paddings Enabled' ,
        cast(is_ansi_warnings_on as tinyint) AS 'ANSI Warnings Enabled' ,
        cast(is_arithabort_on as tinyint) AS 'Arithmetic Abort Enabled' ,
        cast(is_auto_close_on as tinyint) AS 'Auto CLOSE' ,
        cast(is_auto_create_stats_on as tinyint) AS 'Auto Create Statistics' ,
        cast(is_auto_shrink_on as tinyint) AS 'Auto Shrink' ,
        cast(is_auto_update_stats_async_on as tinyint) AS 'Auto Update Statistics Asynchronously' ,
        cast(is_auto_update_stats_on as tinyint) AS 'Auto Update Statistics' ,
        cast(is_cursor_close_on_commit_on as tinyint) AS 'Close Cursor on Commit Enabled' ,
        cast(is_concat_null_yields_null_on as tinyint) AS 'Concatenate Null Yields Null' ,
        cast(is_db_chaining_on as tinyint) AS 'Cross-Database Ownership Chaining Enabled' ,
        cast(is_date_correlation_on as tinyint) AS 'Data Correlation Optimization Enabled' ,
        cast(is_read_only as tinyint) AS 'Database Read-Only' ,
        cast(is_local_cursor_default as tinyint) AS 'Default Cursor' ,
        cast(0 as tinyint) AS 'Encryption Enabled' , --not in 2005
        cast(is_arithabort_on as tinyint) AS 'Numeric Round-Abort' ,
        cast(is_parameterization_forced as tinyint) AS 'Parameterization' ,
        cast(is_quoted_identifier_on as tinyint) AS 'Quoted Identifiers Enabled' ,
        cast(is_read_committed_snapshot_on as tinyint) AS 'Read Committed Snapshot' ,
        cast(is_recursive_triggers_on as tinyint) AS 'Recursive Triggers Enabled' ,
        cast(is_broker_enabled as tinyint) AS 'Service Broker Enabled' ,
        cast(is_trustworthy_on as tinyint) AS 'Trustworthy',
        cast(is_published as tinyint) AS 'Published',
        cast(is_subscribed as tinyint) AS 'Subscribed',
        cast(is_merge_published as tinyint) AS 'Merge Publish',
        cast(is_distributor as tinyint) AS 'Distributor',
        collation_name AS 'Collation Name',
        log_reuse_wait_desc
FROM    sys.databases ;
"@

# before we do anything else, are we likely to be able to save the file?
# if the directory doesn't exist, then create it
#if (!(Test-Path -path "$DirectoryToSaveTo")) #create it if not existing
#  {
#  New-Item "$DirectoryToSaveTo" -type directory | out-null
#  }
$excel = New-Object -Com Excel.Application #open a new instance of Excel
$excel.Visible = $True #make it visible (for debugging more than anything)
$wb = $Excel.Workbooks.Add() #create a workbook
$currentWorksheet=1 #there are three open worksheets you can fill up
foreach ($server in (Get-Content $serverList)) 
	{ #only create the worksheet if necessary
	if ($currentWorksheet-lt 4) {$ws = $wb.Worksheets.Item($currentWorksheet)}
	else  {$ws = $wb.Worksheets.Add()} #add if it doesn't exist
	$currentWorksheet += 1 #keep a tally
	#if  ($server.version -eq 2000) {$SQL=$SQL2000} #get the right SQL Script
	#if  ($server.version -eq 2005) {$SQL=$SQL2005} 
	#if  ($server.version -eq 2008) {$SQL=$SQL2008}
	$currentName=$server.Replace("\","_") # and name the worksheet
	$ws.name=$currentName # so it appears in the tab 
	# note we create the query so that the user can run it to refresh it
    
    $myconnectionstring = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=master;Data Source=$server"  
    $adOpenStatic = 3 
    $adLockOptimistic = 3  
    
    $objConnection = New-Object -comobject ADODB.Connection 
    $objRecordset = New-Object -comobject ADODB.Recordset  
    
    $objRecordset.CursorLocation = 3
    
    $objConnection.Open($myconnectionstring) 
    $objRecordset.Open($SQL,$objConnection,$adOpenStatic,$adLockOptimistic)  
    
    $objRecordset.MoveFirst()
    
    $qt = $ws.QueryTables.Add($objRecordset,$ws.Range("A1"))
	# and execute it
	if ($qt.Refresh())#if the routine works OK
		{
	              $ws.Activate()
		$ws.Select()
		$excel.Rows.Item(1).HorizontalAlignment = $xlCenter
		$excel.Rows.Item(1).VerticalAlignment = $xlTop
		$excel.Rows.Item(1).Orientation = -90
		$excel.Columns.Item("F:AH").NumberFormat = "[Red][=0]û;[Blue][=1]ü"
		$excel.Columns.Item("F:AH").Font.Name = "Wingdings" 
		$excel.Columns.Item("F:AH").Font.Size = 12
		$excel.Rows.Item("1:1").Font.Name = "Calibri"
		$excel.Rows.Item("1:1").Font.Size = 9
		$excel.Rows.Item("1:1").Font.Bold = $true
		$Excel.Columns.Item(1).Font.Bold = $true
        [void] $Excel.Columns.AutoFit()
		}

	}
$filename=$filename -replace  '[\\\/\:\.]',' ' #remove characters that can cause problems
$filename = "$filename.xlsx" #save it according to its title
if (test-path $filename ) { rm $filename } #delete the file if it already exists
$wb.SaveAs($filename,  $xlOpenXMLWorkbook) #save as an XML Workbook (xslx)
$wb.Saved = $True #flag it as being saved
$objRecordset.Close() 
$objConnection.Close() 
$wb.Close() #close the document
$Excel.Quit() #and the instance of Excel
$wb = $Null #set all variables that point to Excel objects to null
$ws = $Null #makes sure Excel deflates
$Excel=$Null #let the air out
# Hristo Deshev's Excel trick 'Pro Windows Powershell' p380
[GC]::Collect()

Here’s a screen shot of the output from one of our Sharepoint database servers.