Restore SQL Server backups from wildcard path

RestoreLastBak.ps1

param ($bakpath, $dbname, $instance, $datapath, $logpath, [switch]$norecovery, [switch]$replace, [switch]$rollback, [switch]$standby, [switch]$test)

# Restore latest backup from wildcard path, optionally forcing out open connections
# Used to restore backups copied to a standby or test server
""

# SQL 2008 PS module imports
#[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')  | out-null
#if ((get-pssnapin sqlserverprovidersnapin100 -ErrorAction "SilentlyContinue") -eq $NULL) { add-pssnapin sqlserverprovidersnapin110 }
#if ((get-pssnapin sqlservercmdletsnapin100 -ErrorAction "SilentlyContinue") -eq $NULL) { add-pssnapin sqlservercmdletsnapin110 }

# SQL 2012 PS module imports
Import-Module sqlps

$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $instance

if (($bakpath -eq $null) -or ($dbname -eq $null)) {
    Write-Error "Usage: RestoreLastBak.ps1 -bakpath(file/wildcard) -dbname [-instance] [-datapath] [-logpath] [-norecovery] [-replace] [-rollback] [-test]"
    exit
}

if ($datapath -eq $null) {
    $datapath = $srv.Information.MasterDBPath
    $logpath = $srv.Information.MasterDBLogPath
}
elseif ($logpath -eq $null) { $logpath = $datapath }

if (($datapath.Length -eq 0) -or ($logpath.Length -eq 0)) {
    Write-Error "Unable to determine restore paths. Must specify parameters -datapath, -logpath"
    exit
}


if ($instance -eq $null) { $instance = "." }

#$sqlver = Invoke-Sqlcmd -Query "SELECT @@VERSION" -ServerInstance $instance
#$sqlver.Column1.ToString()

$bak = dir $bakpath | sort -prop LastWriteTime | select -last 1
#"Last backup: $bak"

$go = [Environment]::NewLine + "GO" + [Environment]::NewLine

$sql = "RESTORE FILELISTONLY FROM DISK = '$bak'"
$sql

$files = Invoke-Sqlcmd -Query $sql -ServerInstance $instance

$sql = ""
if ($rollback) { $sql += "IF EXISTS(SELECT * FROM sys.databases WHERE [name] = '$dbname' AND [state_desc] = 'ONLINE') ALTER DATABASE [$dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE" + $go }
$sql += "RESTORE DATABASE [$dbname] FROM DISK = '$bak' WITH "

foreach ($file in $files) {
    $lname = $file.LogicalName
    $pname = $file.PhysicalName.Split("\")[-1]
    $ext = $pname.Split(".")[-1]
    $ftype = $file.Type
    " -> {0} ({1}: {2})" -f $lname, $ftype, $pname

    if ($ftype -eq "D") { $sql += "MOVE N'$lname' TO N'$datapath\$dbname.$ext', " }
    elseif ($ftype -eq "L") { $sql += "MOVE N'$lname' TO N'$logpath\$dbname.$ext'" }
}
""
if ($norecovery) { $sql += ", NORECOVERY" }
if ($replace) { $sql += ", REPLACE" }
if ($standby) { $sql += ", STANDBY" }
$sql += $go

if (($rollback) -and (!$norecovery)) { $sql += "ALTER DATABASE [$dbname] SET MULTI_USER" + $go }
$sql

if (!$test) { Invoke-Sqlcmd -Query $sql -Verbose -ServerInstance $instance -QueryTimeout 65535 } # Timeout added to work around PS1 bug that does not respect unlimited query runtime