Day: February 1, 2013

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