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

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    
    
}

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()

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()

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()