Last active
December 6, 2017 21:47
-
-
Save danielscholl/052670c089a83bc451ff43b99111e537 to your computer and use it in GitHub Desktop.
Restore Database
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
function Invoke-DatabaseRestore { | |
param ([String]$SQLServer="(local)", $BackupPath, [String]$BackupFileFilter = "*.bak") | |
#load assemblies | |
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null | |
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null | |
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null | |
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null | |
gci $BackupPath -Filter $BackupFileFilter | select fullname | % { | |
$backupFile = $_.FullName | |
#we will query the database name from the backup header later | |
$server = New-Object ( "Microsoft.SqlServer.Management.Smo.Server" ) $SQLServer | |
$backupDevice = New-Object( "Microsoft.SqlServer.Management.Smo.BackupDeviceItem" ) ($backupFile, "File") | |
$smoRestore = new-object( "Microsoft.SqlServer.Management.Smo.Restore" ) | |
$backupDevice| FL * | |
#Get default log and data file locations http://sqlblog.com/blogs/allen_white/archive/2009/02/19/finding-your-default-file-locations-in-smo.aspx | |
$DataPath = if ($server.Settings.DefaultFile.Length -gt 0 ) { $server.Settings.DefaultFile } else { $server.Information.MasterDBLogPath } | |
$LogPath = if ($server.Settings.DefaultLog.Length -gt 0 ) { $server.Settings.DefaultLog } else { $server.Information.MasterDBLogPath } | |
#restore settings | |
$smoRestore.NoRecovery = $false; | |
$smoRestore.ReplaceDatabase = $true; | |
$smoRestore.Action = "Database" | |
$smoRestore.PercentCompleteNotification = 10; | |
$smoRestore.Devices.Add($backupDevice) | |
#get database name from backup file | |
$smoRestoreDetails = $smoRestore.ReadBackupHeader($server) | |
#display database name | |
"Database Name from Backup Header : " +$smoRestoreDetails.Rows[0]["DatabaseName"] | |
#give a new database name | |
$smoRestore.Database = $smoRestoreDetails.Rows[0]["DatabaseName"] | |
#Relocate each file in the restore to the default directory | |
$smoRestoreFiles = $smoRestore.ReadFileList($server) | |
foreach ($File in $smoRestoreFiles) { | |
#Create relocate file object so that we can restore the database to a different path | |
$smoRestoreFile = New-Object( "Microsoft.SqlServer.Management.Smo.RelocateFile" ) | |
#the logical file names should be the logical filename stored in the backup media | |
$smoRestoreFile.LogicalFileName = $File.LogicalName | |
$smoRestoreFile.PhysicalFileName = $( if($File.Type -eq "L") {$LogPath} else {$DataPath} ) + "\" + [System.IO.Path]::GetFileName($File.PhysicalName) | |
$smoRestore.RelocateFiles.Add($smoRestoreFile) | |
} | |
#restore database | |
$smoRestore.SqlRestore($server) | |
} | |
} | |
Invoke-DatabaseRestore | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment