Restore All SQL Transaction Logs using Powershell

I came across another opportunity for using Powershell when setting up mirroring for a large database at our DR site. The database was over 73Gig. The problem arose when it took a very long time to copy the backup and transaction logs and run the database restore. By the time the copy had finished another several transaction logs had been produced. While applying the *.bak  restore, yet another several transaction logs had been produced.

I don’t know about you, but using the SSMS GUI tool to restore a bunch of transaction logs is very tedious. I’ve got another number of large databases which will require mirroring also. With this in mind, I decided it would be beneficial to have a script to automate the process. What was of interest to me was being able to iterate through the process several times while copying new transaction logs. The script will process all the transaction logs in a directory at one time. If you happen to generate more transaction logs to the directory and run the script again, you will get error messages for the logs already restored but the new logs will still be restored.

The script also takes into account using UNC paths. So, you can run the script from any machine against an SQL Server on a remote machine.

 Restore-TransLogs.ps1 -server "MYSERVER\MYSQL" -database "MY_Database" 
	.NOTES
		AUTHOR:    John P. Wood
		CREATED:   September, 2011
		VERSION:   1.0.0
#>
Param(
	[Parameter(Mandatory=$true)]
	[string]$Server,
	[Parameter(Mandatory=$true)]
	[string]$database,
	[Parameter(Mandatory=$true)]
	[string]$FilePath
	)
Set-StrictMode -Version Latest
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")

## SQL Server Backup\Restore uses local file paths.
Function Get-LocalPath($File) {
	$dir = $File.DirectoryName
	$rp = $dir.Remove(0,($dir.IndexOf('$')+2))
	$root = (((Get-Item $dir).Root).Name).Replace('$',':')
	$local = $root + '\' + $rp + '\' + $File
	$local	
}
Function New-SMOconnection {
    Param (
		[string]$server
	)
	$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($server)
	$conn.applicationName = "PowerShell SMO"
	$conn.StatementTimeout = 0
	$conn.Connect()
	if ($conn.IsOpen -eq $false) {
		Throw "Could not connect to server $($server)"
	}
	$smo = New-Object Microsoft.SqlServer.Management.Smo.Server($conn)
	$smo
}
Function Invoke-SqlRestore {
	Param(
		[string]$filename
	)
	# Get a new connection to the server
    $backupDevice = New-Object("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($filename, "File")

	# Load up the Restore object settings
	$Restore = new-object Microsoft.SqlServer.Management.Smo.Restore
	$Restore.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Log
	$Restore.Database = $database
    $Restore.NoRecovery = $true
	$Restore.Devices.Add($backupDevice)

	$Restore.SqlRestore($smo)
}
Clear-Host
$smo = New-SMOconnection -server $Server
If(!(Test-Path -LiteralPath $FilePath)){Throw "FilePath not found: $FilePath"}
Get-ChildItem $FilePath -Filter "*.trn" | Sort-Object -Property LastWriteTime | 
	ForEach-Object {
		If(($_.Fullname).StartsWith('\\')) {$file = Get-LocalPath $_ }
		Else {$file = $_.FullName}
		Try {Invoke-SqlRestore -filename $file}
		Catch {
			$ex = $Error[0].Exception
			Write-Output $ex.message
			while ($ex.InnerException)
			{
				$ex = $ex.InnerException
				Write-Output $ex.message
			}
		}
}
If ($smo.ConnectionContext.IsOpen -eq $true) {$smo.ConnectionContext.Disconnect()}

 

Share

2 thoughts on “Restore All SQL Transaction Logs using Powershell

  1. Clive Richardson

    I’m in the process of doing the same kind of things as you’ve presented here using PowerShell. One major snag I’ve got is the time it takes to do SMO backups of SQL Server databases. SMO backup via Powershell seems to work fine on smaller databases but takes way too long on the larger databases compared to standard SQL Backup. We don’t have huge databases but a 50Gb database backed up to a network drive that normally takes around 10 minutes using standard backup will take over an hour. The entire backup duration on one of our production servers is around 20 minutes so just one database taking an hour plus using Powershell/SMO is totally impractical. I wondered if you have noticed any backup duration issues using Powershell/SMO?

    cheers,
    Clive

  2. John Wood Post author

    Clive,
    I now use POSH exclusively for all my backups and have NOT found the durations to be any different. I support over 250 databases which range from a few Meg to over 100G. I backup to local drives and then use ROBOCOPY to copy the backups to our “warehouse”. I believe the longest backup takes no more than 15 minutes!

    Sorry for the late reply, but I just now saw your post. Were you able to discover what may have caused such a long duration?

Leave a Reply