Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
Add Comment
Please, Sign In to add comment