Day: February 11, 2013

Get Last DBCC CheckDB Date for all Databases

This will check for the last known checkdb date for all user databases.  This uses SqlPSX.

Import-Module SqlServer

$outputs = @();
$srv = Get-SqlServer -sqlserver "ServerName"
foreach($database in $srv.Databases | where{$_.IsSystemObject -eq $false})
{
	$query = "DBCC DBINFO ('$($database.Name)') WITH TABLERESULTS"
	$results = Get-SqlData -sqlserver $srv -dbname $database -qry $query
	foreach($datarow in $results)
	{
	#dbi_dbccLastKnownGood
	if($datarow.Field.ToString() -eq "dbi_dbccLastKnownGood")
	{
		$output = New-Object -TypeName PSObject -Property @{
			Name = $database.Name
			DBCCDate = $datarow.VALUE.ToString()
		}
		$outputs += $output
	}
		
	}
}
$outputs | Format-Table -AutoSize

Resolve Sql Server Windows Login Groups using Powershell

Quick script to resolve sql server windows groups to show who is in said groups.  This uses SQLPSX and Quests’ QAD cmdlets (which is apparently owned by dell now?).

param(
	[string]$serverInstance = {throw "You must enter a sql server instance name"}
)

Import-Module SqlServer

if ( (Get-PSSnapin -Name quest.activeroles.admanagement -ErrorAction SilentlyContinue) -eq $null )
{
    Add-PsSnapin quest.activeroles.admanagement
}

$outputs = @();
$srv = Get-SqlServer -sqlserver $serverInstance
$logins = $srv.Logins | where{$_.LoginType -eq [Microsoft.SqlServer.Management.Smo.LoginType]::WindowsGroup}

foreach($login in $logins)
{
	$qadUsers = Get-QADGroupMember $login.Name | select Name, NTAccountName # | sort Name | Format-Table -AutoSize
	foreach($user in $qadUsers)
	{
		$output = New-Object -TypeName PSObject -Property @{
			ServerName = $srv
			GroupName = $login.Name
			Name = $user.Name
			AccountName = $user.NTAccountName
		}
		$outputs += $output	
	}
	
}
$outputs | SELECT ServerName, GroupName, Name, AccountName #| Format-Table -AutoSize