Skip to content

Instantly share code, notes, and snippets.

@kmatt
Created June 21, 2012 17:07
Show Gist options
  • Save kmatt/2967052 to your computer and use it in GitHub Desktop.
Save kmatt/2967052 to your computer and use it in GitHub Desktop.
Restore SQL Server backups from wildcard path
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment