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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: