Skip to content

Instantly share code, notes, and snippets.

@danielscholl
Last active December 6, 2017 21:47
Show Gist options
  • Save danielscholl/052670c089a83bc451ff43b99111e537 to your computer and use it in GitHub Desktop.
Save danielscholl/052670c089a83bc451ff43b99111e537 to your computer and use it in GitHub Desktop.
Restore Database
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