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

Restore RedGate Backups using Powershell

This actually iterates a list of folders in a directory, grabs the latest backup from each folder, restores it and then does an integrity check on said restored database.  Unfortuantely, RedGate has no cmdlets for doing this, so it’s strictly passing sql strings into Invoke-sqlcmd based.  This also checks the filelist in the backups and will move the data/log files to a location that you specify.  Use at your own risk.

function RestoreDatabase([string]$servername, [string]$databaseName, [string]$backupFile, [string]$mdfFile, [string]$ldfFile)
{
    try
    {
        $restoreCmd = "
            EXECUTE master..sqlbackup 
            '-SQL `"RESTORE DATABASE [$($databaseName)] 
            FROM DISK = ''$($backupFile)'' 
            WITH RECOVERY, 
            DISCONNECT_EXISTING,  
            REPLACE,
        "
    
        $cmd = "EXECUTE master..sqlbackup '-SQL `"RESTORE FILELISTONLY FROM DISK = [$backupFile]`" '"
        $dataFiles = Invoke-Sqlcmd -ServerInstance $srv -Database 'master' -Query $cmd
        
        foreach($dataFile in $dataFiles)
        {
            if($dataFile.Type -eq "D")
            {
                $move = "MOVE ''$($dataFile.LogicalName)'' TO ''$($mdfFile)" + $dataFile.PhysicalName.Substring($dataFile.PhysicalName.LastIndexOf("\")+1) + "'',"
            }
            if($dataFile.Type -eq "L")
            {
                $move = "MOVE ''$($dataFile.LogicalName)'' TO ''$($ldfFile)" + $dataFile.PhysicalName.Substring($dataFile.PhysicalName.LastIndexOf("\")+1) + "'',"
            }
            $restoreCmd += $move
            $move = ""
        }
        
        $restoreCmd = $restoreCmd.substring(0, $restoreCmd.length-1) + "`"'" 
        Invoke-sqlcmd -serverinstance $srv -database 'master' -query $restoreCmd -QueryTimeout 0 
        
        $sqlout = sqlcmd -E -S $srv -d $databaseName -Q "dbcc checkdb WITH NO_INFOMSGS"
        
        if($sqlout -ne $null)
        {
            Send-MailMessage -SmtpServer $mailServer -To $to -Subject "Database Consistancy Check Failure for $($databaseName)" -Body $sqlout.ToString() -From $from
        }
    }
    catch
    {
        $err = $error[0]|format-list -force  
        send-mailmessage -smtpserver $mailServer -to $to -from $from -subject 'Error in database consistancy checks on $srv' -body $err
        write-host $err
    }

	
}

function Main()
{
    foreach($dir in (gci -path $backupsDir | where{$_.PSIsContainer}))
    {
        $file = gci -path $dir.fullName | where{$_.Name -like "FULL_*" -and $_.Extension -eq ".sqb"} | sort CreationTime -descending | SELECT -first 1
        
        write-host "Restoring database $($dir.name)  --  " (Get-Date -Format "MM/dd/yyyy HH:mm:ss").ToString()
        RestoreDatabase $srv $dir.Name $file.FullName "c:\SQLData\" "c:\SQLLogs\"
        write-host "Finsihed restoring database $($dir.Name)" (Get-Date -Format "MM/dd/yyyy HH:mm:ss").ToString()
    }
}

import-module sqlps -DisableNameChecking

$mailServer = 'mail.yourdomain.com'
$to = 'you@yourdomain.com'
$from = 'donotreply@yourdomain.com'
$backupsDir = "c:\SQLData\Backups"
$srv = "ServerInstance"

main

Remove Sql Login using Powershell

Quick script to remove a login from sql server using powershell.  This will iterate through the databases and the users in said databases to remove the users from them before dropping the login itself.  This uses sqlpsx.  Use at your own risk.

Import-Module SqlServer
$srv = "ServerInstance"
$loginName = "LoginName"

$login = Get-SqlLogin -sqlserver $srv | where{$_.Name -eq $loginName}
if($login -eq $null)
{
	return;
}

$dbs = Get-SqlDatabase -sqlserver $srv -force
foreach($db in $dbs)
{
	$logins = $db.EnumLoginMappings();
	foreach($dbLogin in $logins)
	{	
		#Write-Host $dbLogin.LoginName
		if($dbLogin.LoginName -eq $loginName)
		{
			Remove-SqlUser -dbname $db.Name -sqlserver $srv -name $dbLogin.LoginName
		}
	}
}
Remove-SqlLogin -sqlserver $srv -name $loginName

Set Database Recovery Model using Powershell

Another quick script to set all database recovery models using powershell.  This ignores system databases.  This uses sqlpsx as well.

Import-Module SqlServer

$dataBases = Get-SqlDatabase -sqlserver "ServerName" | where{$_.IsSystemObject -ne $true}
foreach($db in $dataBases)
{
	if($db.RecoveryModel -eq [Microsoft.SqlServer.Management.Smo.RecoveryModel]::BulkLogged)
	{
		$db.RecoveryModel = [Microsoft.SqlServer.Management.Smo.RecoveryModel]::Simple;
		$db.Alter();
	}
}

Powershell script to get Size of all Tables in Sql Server

Quick script to get the size of all tables in all user databases in sql server.  This reports the size in MB and truncates the decimal points.  This uses SQLPSX, but could be easily converted to use SMO directly.

cls
Import-Module SqlServer
$outputs = @();
$dbs = Get-SqlDatabase -sqlserver "ServerName" | where{$_.IsSystemObject -ne $true}
foreach($db in $dbs)
{
	foreach($table in $db.Tables)
	{
		$output = New-Object -TypeName PSObject -Property @{
			Database = $db.Name
			Schema = $table.Schema
			Table = $table.Name
			Size = [Math]::Truncate(($table.DataSpaceUsed * 1024) / 1MB)
		}
		$outputs += $output
	}
}

$outputs | Sort Size -Descending  | Format-Table -AutoSize