Month: December 2011

Sql Server Recovery for Litespeed with Powershell

Kind of a misnomer, there are no cmdLets for powershell for litespeed.  This strictly generates the strings required for doing recovery with Litespeed and either writes them out to the host or executes them by passing the commands to the sql server.

param(
	[string]$ServerName = "ServerName",
	[string]$DatabaseName = "DatabaseName", #{throw "You must enter a database name"}
	[string]$FullBakFile = "PathToBackupFile", #{throw "You must enter the path to a full backup file"},
	[string]$TRNDirectory = "PathToTransactionLogBackupDirectory", # {throw "The directory must be specified"},
	[string]$RecoveryEndDate = "12/27/2011 9:30AM",
	[System.Collections.Hashtable]$RelocateFiles = @{"File1" = "c:\NewLocation\DBFile.mdf" ; "File2" = "C:\NewLocation\DBLogFileOrOtherDataFile.ldf"},
	[switch]$PrintOnly = $false
)

function AppendRelocatedFiles([string]$cmd)
{
	if($RelocateFiles.Count -eq 0){return $cmd + "`n";}
	
	$cmd += ",`n"
	[int]$i = 0;
	
	foreach($moveFile in $RelocateFiles.GetEnumerator())
	{
		if($i -eq ($RelocateFiles.Count -1))
		{
			$cmd += "@with = N'MOVE N''$($moveFile.Name)'' TO N''$($moveFile.Value)'''`n"
		}
		else
		{
			$cmd += "@with = N'MOVE N''$($moveFile.Name)'' TO N''$($moveFile.Value)''',`n"
		}
		$i += 1;
	}
	return $cmd
}

function Restore([string]$fileName, [string]$dbName, [Microsoft.SqlServer.Management.Smo.RestoreActionType]$action, [boolean]$Recover)
{
	if($Recover)
	{
		$cmd = "RESTORE DATABASE $dbName WITH RECOVERY"
		Write-Host $cmd
		if(!($PrintOnly))
		{
			Set-SqlData -sqlserver $ServerName -dbname master -qry $cmd
		}
		return
	}
	
	switch($action)
	{
		([Microsoft.SqlServer.Management.Smo.RestoreActionType]::Database)
		{
			$cmd = "exec master.dbo.xp_restore_database 
				@database = N'$DatabaseName' ,
				@filename = N'$fileName',
				@filenumber = 1,
				@with = N'STATS = 10',
				@with = N'NORECOVERY',
				@with = N'REPLACE',
				@affinity = 0,
				@logging = 0"

			$cmd = AppendRelocatedFiles $cmd
		        
			Write-Host $cmd
			if(!($PrintOnly))
			{
				Write-Host "Restoring file:  $fileName"
				#Invoke-Sql -server $ServerName -database master -sql $cmd
				Set-SqlData -sqlserver $ServerName -dbname master -qry $cmd
			}
		}
		([Microsoft.SqlServer.Management.Smo.RestoreActionType]::Log)
		{
			$cmd = "exec master.dbo.xp_restore_log 
			@database = N'$dbName' ,
			@filename = N'$($fileName)',
			@filenumber = 1,
			@with = N'STATS = 10',
			@with = N'NORECOVERY',
			@affinity = 0,
			@logging = 0"
	        
			$cmd = AppendRelocatedFiles $cmd
			
			Write-Host $cmd
			if(!($PrintOnly))
			{
				Write-Host "Restoring file:  $fileName"
				#Invoke-Sql -server $ServerName -database master -sql $cmd 
				Set-SqlData -sqlserver $ServerName -dbname master -qry $cmd
			}
		}
	}
}


function Main
{
	if(!(Test-Path($FullBakFile)))
	{
		throw "Invalid full backup file location`n $FullBackFile."
		return
	}
	
	if(!(Test-Path($TRNDirectory)))
	{
		throw "Invalid path to transaction log directory ($TRNDirectory)."
		return
	}
	
	#start time of transaction log backup is the last write time of the full backup file
	$file = Get-Item -Path $FullBakFile
	[string]$StartDate = $file.LastWriteTime
	
	#TODO:  Add error handling for bad date formats
	$sDate = [datetime]::Parse($StartDate)
	$eDate = [datetime]::Parse($RecoveryEndDate)
	$eDate = $eDate.AddMinutes(1)  #just to include the last file.

	#TODO:  test to make sure $StartDate is not > LastFullBackup file date

	#TODO:  Change the .LastWriteTime to .CreationTime.  Needed to use .LastWriteTime because when I copied the 
	#files to my local all the .CreationTime was set to right now.
	$files = Get-ChildItem -Path $TRNDirectory -Recurse -Include *.trn | Where-Object {$_.LastWriteTime -ge $sDate -and $_.LastWriteTime -le $eDate} | Sort-Object -Property LastWriteTime 

	#restore full backup
	Restore $FullBakFile $DatabaseName ([Microsoft.SqlServer.Management.Smo.RestoreActionType]::Database) $false
	
	#restore log backups
	foreach($file in $files)
	{
		Restore $file.FullName $DatabaseName ([Microsoft.SqlServer.Management.Smo.RestoreActionType]::Log) $false
	}
	
	#set recovery (filename or action does not matter, only dbname and -norecovery matter)
	Restore "" $DatabaseName ([Microsoft.SqlServer.Management.Smo.RestoreActionType]::Log) $true
	
	Write-Host "Completed" -ForegroundColor Green
}

cls
Main

There’s no error handling to speak of.  Use at your own risk.

Backup Sharepoint Sites with Powershell

This will backup your sharepoint sites to a given path and delete any backups older than 3 days old.

param(
    $backupPath = $("You must enter a location to place the backup files")
)

$ver = $host | select version
if ($ver.Version.Major -gt 1)  {$Host.Runspace.ThreadOptions = "ReuseThread"}
Add-PsSnapin Microsoft.SharePoint.PowerShell

$fileDate = get-date -f yyyyMMdd
get-spwebapplication | get-spsite | %{$filePath = $backupPath + $_.URL.Replace("http://", "").Replace("/", "-") + "_" + $fileDate + ".bak" ; backup-spsite -identity $_.URL -path $filePath}

$files = get-childitem -path $backupPath | where{$_.Extension -eq ".bak"}
foreach($file in $files)
{
    if($file.CreationTime -lt (get-date).addDays(-3))
    {
        remove-item $file.FullName -force
    }
}

Finding Untrusted Foreign Key Constraints

To find untrusted foreign key constraints, use this:

SELECT 
name AS ForeignKey,
OBJECT_NAME(parent_object_id) AS TableName,
OBJECT_NAME(referenced_object_id) AS ReferencedTable,
is_not_trusted
FROM sys.foreign_keys

In order to make these constraints be re-trusted, you have to re-check the constraint, so don’t run this on a production system during the day on large tables.

ALTER TABLE TableName WITH CHECK CHECK CONSTRAINT ConstraintName;

The key here is the WITH CHECK.  If you omit that the query will still execute successfully, but your constraint still will not be trusted afterwards.

Set Data and Log file default growth

Here’s a quick script to set the model database grown & autogrowth settings.  These settings are not a one-size fits all.  They’re just better defaults for my environment than the defaults (which are terrible for any environment, IMHO).  You must have SQLPSX installed for this to work.

$servers = @('Server1', 'Server2')

foreach($server in $servers)
{
	$logFiles = Get-SqlDatabase $server "model" | Get-SqlLogFile
	foreach($logFile in $logFiles)
	{
		$logFile.GrowthType = [Microsoft.SqlServer.Management.Smo.FileGrowthType]::KB
		$logFile.Growth = 51200;  #50mb
		$logFile.Alter();
	}
	
	$dataFiles = Get-SqlDatabase $server "model" | Get-SqlDataFile
	foreach($dataFile in $dataFiles)
	{
		$dataFile.GrowthType = [Microsoft.SqlServer.Management.Smo.FileGrowthType]::KB
		$dataFile.Growth = 102400;  #100mb  
		$dataFile.Alter();
	}
}

Find duplicate indexes in Sql Server with Powershell

Here is a quick script to find duplicate indexes on tables in sql server using powershell.  You’ll need SqlPSX installed to run this.  The output is not pretty, as it just writes to the host, but it works.  I’ll leave it to you to write it to excel or whatnot.  The code to write to excel can be found in my previous post.

function CheckIndexColumns($table, $index)
{
	$columns = $index.IndexedColumns# | Sort-Object -Property Name
	foreach($idx in $table.Indexes | where{$_.Name -ne $index.Name})
	{
		#if($idx.IndexedColumns.Count -ne $index.IndexedColumns.Count){continue;}
		#$sourceCols = $index.IndexedColumns | Sort-Object -Property Name
		$targetCols = $idx.IndexedColumns #| Sort-Object -Property Name
		
		$diff = Compare-Object -ReferenceObject $columns -DifferenceObject $targetCols -SyncWindow 1000
		if($diff -eq $null)
		{
			Write-Host "Possible duplicate index found on table Table: $($table.Name)"  -ForegroundColor Red
			write-Host "Index [$($index.Name)] and index [$($idx.Name)] have the same columns:  $($idx.IndexedColumns)" -ForegroundColor DarkCyan
		}
	}
}

cls
$db = Get-SqlDatabase -sqlserver ServerName -dbname DatabaseName

foreach($table in $db.Tables)
{
	Write-Host "Checking table $($table.Name)..." -ForegroundColor Gray
	foreach($idx in $table.Indexes)
	{
		CheckIndexColumns $table $idx
	}
}

Export Index Sizes to Excel in Powershell

Here’s a quick script to write your index sizes in KB to Excel in powershell.  The code to write to Excel was pilfered from here.  Obviously, this isn’t 100% accurate, as you’d have to execute the sys.dm_db_index_physical_stats function in detailed mode to get detailed information, but this is a good rough estimate.

$filePath = "c:\IndexSizes.xlsx"

if((Test-Path $filePath))
{
	Remove-Item $filePath -Force
}

$objExcel = New-Object -ComObject Excel.Application
 
$wb = $objExcel.Workbooks.Add()
$item = $wb.Worksheets.Item(1)
 
$objExcel.Visible = $True
$i = 1 


foreach($table in $db.Tables)
{
	$item.Cells.Item($i,1) = $table.Name
	foreach($idx in $table.Indexes)
	{
		$item.Cells.Item($i,2) = $idx.Name
		$item.Cells.Item($i,3) = $idx.SpaceUsed
		$i++
	}
}
 
$wb.SaveAs($filePath)
 
# Close Excel and clean up
$objExcel.Quit()
 
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($item) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
 
Remove-Variable objExcel
Remove-Variable wb

Replicating the SQL ‘IN’ Statement in Powershell

I’ve been taking the sqlskills class this week (mind-numbingly good class btw, will post more later when my brain cools down) and lucky for me Aaron Nelson (blog | twitter) is taking the class as well.  So, this gave me a chance to ask something which I’ve been trying to figure out how to replicate in powershell for a few months now.  Naturally, it took him mere seconds to figure it out.

I’ve been wanting to replicate the functionality of the sql ‘in’ statement on the pipeline, but I could never get it to work correctly.  Here is how you can get this to work.  You’ll need SQLPSX installed to get this to work.

cls
$findTables = 'Servers','Jobs','Location'
Get-SqlDatabase -sqlserver ServerName -dbname DatabaseName | Get-SqlTable |
where{$findTables -contains $_.Name} |
select schema, name

At first I was a bit perplexed at how this was working, but after a few cups of coffee it made more sense.  What I had been confused about was the order of the statements in the where{} cmdlet.  I had been of the mindset that the where{} was returning a set of objects.  Obviously, this is not the case.  All it is doing is filtering table arraylist.