Skip to content

Log Shipping via PowerShell Invoke-LSRestore

by Dan Thompson on May 13th, 2013

A while ago at work now we were given a requirement that an external company were going to log ship a database over to us. I was asked to design a robust PowerShell function which we could use to regularly check and apply transaction log backups to a database backup which they had supplied. In essence Log Shipping via PowerShell, or at least the LSRestore portion of it.

Knowing how many problems I’ve had over the years with Log Shipping, I wanted to make the function as robust as possible within the time I had, as well as allowing flexibility so the function could be reused if needs be.

I looked into how the current LSRestore works – specifically: How does it check the file to ensure it can be applied to the backup?

My findings led me to use the stored proc [master].[sys].[sp_can_tlog_be_applied] within the function to check if the file was applicable. Then, if the file is applicable it is restored with the various options passed from the parameters. Finally, a check is done on the restorehistory tables to ensure the transaction log backup was applied succesfully. If there are any problems, the function will throw an error, which will cause any job step that is executing it to fail also.

The end product was Invoke-LSRestore below which we have had running now for a few months. Of course this will come nowhere near to the SQL Server LSRestore, but it is a PowerShell alternative if you are in a similar situation where you need to use it.

We have had it running regularly via a SQL Agent job on one of our production environments for the last few months, but as with any post I make – always test, test and test again on your own environment.

I created another function “Handle-Output” which was just a nicety really for running it in the console.

Handle-Output :

function Handle-Output($Object,$ForegroundColor="white") {
		if ($host.Name -eq "ConsoleHost") {
			Write-Host -ForegroundColor $ForegroundColor -Object $Object
		} else {
			Write-Output $Object
		}
}

Invoke-LSRestore :

function Invoke-LSRestore($ServerInstance, $DatabaseName,$BackupLocation,[switch]$Debug,[switch]$Standby,[switch]$NoRecovery)
{
	$now = Get-Date -Format u
	Handle-Output -Object "$now - Restore Started" -ForegroundColor "Green"
	
	$now = Get-Date -Format u
	Handle-Output -Object "$now - Settings:"	-ForegoundColor DarkYellow
	Handle-Output "`tServer: $ServerInstance"	-ForegoundColor DarkYellow
	Handle-Output "`tDatabase: $DatabaseName"	-ForegoundColor DarkYellow
	Handle-Output "`tBackup Source: $BackupLocation"	-ForegoundColor DarkYellow

	$FilesRestored = 0

	$BackupLocSQL = "
		DECLARE @backuploc as TABLE (Value varchar(255), Data varchar(255))

		INSERT INTO @backuploc(Value,Data)
		EXEC master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory'

		SELECT TOP 1 Data
		FROM @backuploc"

	$DefaultBackupLocation = Invoke-SqlCmd -ServerInstance $ServerInstance -Query $BackupLocSQL | ForEach-Object {$_.Data}
	if ($debug -eq $TRUE) {Handle-Output -Object $DefaultBackupLocation -ForegroundColor "DarkYellow"}

	if ($debug) { get-childitem -path $BackupLocation\$DatabaseName*.trn | Select-Object Name | Sort-Object Name }
	
	get-childitem -path $BackupLocation\$DatabaseName*.trn | Sort-Object Name | ForEach-Object {
		$trn_path = $_
		$sql = "
			declare @p3 bit
			set @p3=1

			exec master.sys.sp_can_tlog_be_applied 
				 @backup_file_name=N'$trn_path'
				,@database_name=N'$DatabaseName'
				,@result=@p3 output

			select @p3 [Result]"
			
		if ($debug -eq $TRUE) {Handle-Output -Object $sql -ForegroundColor "DarkYellow"}
		
		$ret = Invoke-SqlCmd -ServerInstance $ServerInstance -Query $sql | ForEach-Object {$_.Result}
		
		if ($debug -eq $TRUE) {Handle-Output -Object $ret -ForegroundColor "DarkYellow"}
		
		if ($ret -eq $TRUE)
		{
			$RestoreSQL = "
				RESTORE LOG [$DatabaseName] 
				FROM  DISK = N'$trn_path'
				WITH  NOUNLOAD,  STATS = 10"
				
				if($Standby) {
					$RestoreSQL = $RestoreSQL + ", STANDBY =N'$DefaultBackupLocation\ROLLBACK_UNDO_$DatabaseName.TUF'";
				}
				if($NoRecovery) {
					$RestoreSQL = $RestoreSQL + ", NORECOVERY";
				}
			
			if ($debug) {$RestoreSQL}
			
			$now = Get-Date -Format u
			Handle-Output -Object "$now - Attempting to restore: `'$trn_path`'" -ForeGroundColor "Yellow"
			
			$RestoreResult = Invoke-SqlCmd -ServerInstance $ServerInstance -Query $RestoreSQL -Database Master -ErrorAction Stop -QueryTimeout 600
			
			if ($debug -eq $TRUE) {Handle-Output -Object $RestoreResult -ForegroundColor "DarkYellow"}
			
			$CheckRestoreSQL = "
				SELECT CAST(1 as bit) [IsRestored]
				FROM msdb.dbo.restorehistory h
				INNER JOIN msdb.dbo.backupset bs ON h.backup_set_id = bs.backup_set_id
				INNER JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
				WHERE 
					h.destination_database_name = '$DatabaseName' 
				AND bmf.physical_device_name = '$trn_path'
				AND bs.[type]='L'
			"
			
			if ($debug) {$checkRestoreSQL}
			
			$RestoreResult = $FALSE
			$RestoreResult = Invoke-SqlCmd -ServerInstance $ServerInstance -Query $CheckRestoreSQL | ForEach-Object {$_.IsRestored}
			
			if ($RestoreResult -eq $TRUE)
			{
				Handle-Output -Object "Restored Successfully`n" -ForegroundColor "Green"
			} else {
				Throw "Restore Failed!"
			}
			
			$FilesRestored ++;
		} else {
			Handle-Output -Object "`'$trn_path`' skipped." -ForegroundColor yellow
		}
	}
	
	Handle-Output -Object "Number of files restored $FilesRestored." -ForegroundColor "Green"
}

# Invoke-LSRestore -ServerInstance "UATDBS01" -DatabaseName "Accounts" -BackupLocation "Y:\MSSQL\Backups" -NoRecovery
# Invoke-LSRestore -ServerInstance "INTDBS01" -DatabaseName "Sales" -BackupLocation "Y:\MSSQL\Backups" -NoRecovery

From → SQL Server