none
SQL inventory Questions One server, multiple instances RRS feed

  • Question

  • Good afternoon scripting experts!  

    I am a new DBA for SQL and one of my first projects is getting a hand on the inventory of all the servers/instances that we administer.  I have a script that I run that is a hodgepodge of a few different scripts I have found here and it works great with one exception.  (I am about to show my inexperience with the next part, please forgive me)

    If I go into management studio and type in connect to "GLDBA03102" it connects fine I have been calling that the "Server" If I go to a server that has "Servername\Instancename" (I hope thats correct) my program is not able to connect and it just gives me the generic error message that it is unable to connect - Is there a Way I can roll through all "Instances" on a specfiic server?  Currently I'm using a serverlist.txt file that just shows all server names....   The problem is some of these servers are "virtual" and contain many instances, and I dont trust another DBA will provide me with all the instances, which is why I would prefer to scan them instead of "hardcoding" them into my serverlist.txt....

    I hope this makes sense, I'm sorry for the incorrect verbage.. Include are a few samples to hopefully help!

    My main Script (I am sorry for not having the original authors name, I have hacked this thing a bit and did not retain it!)

    #region Variables
    $TextFileLocation = "C:\serverlist.txt"
    $intRow = 1
    $BackgroundColor = 36
    $FontColor = 25
    #endregion
    
    #Region Open Excel
    $Excel = New-Object -ComObject Excel.Application
    $Excel.visible = $True
    $Excel = $Excel.Workbooks.Add()
    $Sheet = $Excel.Worksheets.Item(1)
    #endregion
    
    
    #Go through text file one at a time
    foreach($instance in Get-Content $TextFileLocation)
    {
         $Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
         $Sheet.Cells.Item($intRow,2) = $instance
         $Sheet.Cells.Item($intRow,1).Font.Bold = $True
         $Sheet.Cells.Item($intRow,2).Font.Bold = $True
    	 
    	 for ($column =1; $column -le 2; $column++)
    	 {
    	 $Sheet.Cells.Item($intRow, $column).Interior.Colorindex = 44
    	 $Sheet.Cells.Item($intRow, $column).Font.ColorIndex = $FontColor
    	 }
    	 
     	 #Increase Row count by 1
         $intRow++
    	 
    	 #Create sub-headers
          $Sheet.Cells.Item($intRow,1) = "Name"
          $Sheet.Cells.Item($intRow,2) = "LAST FULL BACKUP"
          $Sheet.Cells.Item($intRow,3) = "LAST LOG BACKUP"
    
    	#Format the column headers
    	for ($col = 1; $col -le 3; $col++)
        {
              $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
              $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = $BackgroundColor
              $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = $FontColor
        }
    	 
    	 
    	 #Finished with Headers, now move to the data
         $intRow++
    	################################################
    	
    	[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
    
    	 # Create an SMO connection to the instance in servers.txt
         $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
    
    	$dbs = $s.Databases
    	
    	foreach ($db in $dbs)
    	{
    	 if (($db.Name -ne "tempdb") -and ($db.Name -ne "model") -and ($db.Name -ne "msdb") -and ($db.Name -ne "master"))
    	 {
    	 
    	 	 if($db.LastBackupDate -eq "1/1/0001 12:00 AM")
    		{
    		 	$fullbackupdate = "Never Backed Up"
    			$fgColor = "red"
    		}
    		else
    		{
    		#$fullBackupDate= "{0:g2}" -f $db.LastBackupDate 
    		$fullBackupDate = $db.LastBackupDate
    		}
    		
    		$Sheet.Cells.Item($intRow, 1) = $db.Name 
        	$Sheet.Cells.Item($intRow, 2) = $db.LastBackupDate #$fullBackupDate 
    		
    		if ($db.RecoveryModel.Tostring() -eq "SIMPLE")
            { 
               $logBackupDate="Simple"
    		}
    		else
    		{ 
    	       #See Date Above..-eq is same as =
               if($db.LastLogBackupDate -eq "1/1/0001 12:00 AM")  
               { 
                   $logBackupDate="Never" 
               } 
               else 
               { 
                   #$logBackupDate= "{0:g2}" -f $db.LastLogBackupDate 
    			   $logBackupDate = $db.LastLogBackupDate			   
               } 
                
        	  } 
        	$Sheet.Cells.Item($intRow, 3) = $logBackupdate
    	    $intRow ++ 
        
           } 
       } 
       $intRow ++ 
    
    
    }
    
    $Sheet.UsedRange.EntireColumn.AutoFit()
    cls

    in my example on the excel spreadsheet it will not list any details for "Poly1", even though there are multiple instances on that server name

    Thursday, May 8, 2014 9:30 PM

Answers

  • Here is a way to enumerate the instances on a server by only server name.

    add-type -AssemblyName "Microsoft.SqlServer.SqlWmiManagement, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
    $wmi=new-object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer('devws2')
    $wmi.ServerInstances | select Name


    ¯\_(ツ)_/¯

    • Marked as answer by Scjohnson243 Friday, May 9, 2014 1:28 PM
    Thursday, May 8, 2014 10:18 PM

All replies

  • There is no easy way to do that.  That is why we have the SQL tools.  Just install the SQL tools and use SQLPS to enumerate your servers.  No need to build custom scripts.

    Here is a simple way to test servers and get information quickly:

    PS C:\scripts> $srv = New-Object Microsoft.SqlServer.Management.Smo.Server
    PS C:\scripts> $srv.PingSqlServerVersion('devws2\sqlexpress')
    
                                      Major                                   Minor                             BuildNumber
                                      -----                                   -----                             -----------
                                         10                                       0                                    5500
    PS C:\scripts> $srv.PingSqlServerVersion('(local)\sqlexpress')
    
                                      Major                                   Minor                             BuildNumber
                                      -----                                   -----                             -----------
                                         10                                      50                                    4000
    


    ¯\_(ツ)_/¯

    Thursday, May 8, 2014 10:00 PM
  • Here is a way to enumerate the instances on a server by only server name.

    add-type -AssemblyName "Microsoft.SqlServer.SqlWmiManagement, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
    $wmi=new-object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer('devws2')
    $wmi.ServerInstances | select Name


    ¯\_(ツ)_/¯

    • Marked as answer by Scjohnson243 Friday, May 9, 2014 1:28 PM
    Thursday, May 8, 2014 10:18 PM