Welcome to Day 22 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at //blog.waynesheffield.com/wayne/a-month-of-powershell/. Please refer to this page to see all of the posts in this series, and to quickly go to them.
Yesterday we backed up all of our databases. However, what good is a backup if it can’t be restored? Now we’ll restore all those backups into a test database. This script will restore the most recent full backup for each database (into a database named “Verify”), moving all files to a separate directory to avoid trying to restore on top of the existing files. It will then restore the most recent differential backup, and then (if the database is not in the simple recovery model) all transaction log backups since the later of the full / differential backups. This script does assume that if the latest differential backup is more recent that the latest full, that it belongs to that full backup, and the script uses the paths where the databases were backed up to in yesterday’s backup script.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 |
#clear variables $Server = $null #Assign variables $Instance = "localhost\SQL2008" $VerifyDB = 'Verify' #Assign the SMO class to a variable $SMO = "Microsoft.SqlServer.Management.Smo" # get the server $Server = New-Object ("$SMO.Server") "$Instance" # Restoring to a new database changes the $Server.Databases collection, raising an error. # So, create an empty array to hold the current list of databases and loop through that. $Databases = @() # Add each database to this array ForEach ($db in $Server.Databases) { IF ($db.Status -eq 'Normal' -and $db.IsMirroringEnabled -eq $false -and ` $db.Name -ne 'tempdb' -and $db.Name -ne $VerifyDB) { $Databases = $Databases + $db } } ForEach ($db in $Databases) { # Get the location to store the data files at for this verify database $Verify = $Server.Settings.DefaultFile IF (!($Verify.EndsWith('\'))) {$Verify = $Verify + '\'} $Verify = $Verify + "$($VerifyDB)_DBFiles\" # Ensure that the directory exists IF (!(Test-Path -Path $Verify)) {New-Item $Verify -Type Directory} $dir = $Server.Settings.BackupDirectory + "\" + $db.Name + "\Full\" # Get the most recent full backup $Full = $Null IF (Test-Path -Path $dir) { $Full = Get-ChildItem $dir |` Sort-Object LastWriteTime -Descending |` Select-Object -First 1 |` Select-Object Name, FullName, LastWriteTime } IF ($Full) { # Restore the full backup $BackupDevice = New-Object ("$SMO.BackupDeviceItem") ($Full.FullName, 'File') $Restore = New-Object ("$SMO.Restore") $Restore.Checksum = $True $Restore.Devices.Add($BackupDevice) $Restore.Database = "$VerifyDB" $Restore.NoRecovery = $True $Restore.ReplaceDatabase = $True #Relocate the files so that they don't stomp on the existing db files ForEach ($FileGroup in $DB.FileGroups) { ForEach ($File in $FileGroup.Files) { $FileName = Split-Path $File.FileName -Leaf #Get just the filename $FileName = $Verify + $FileName $NewFileLoc = New-Object ("$SMO.RelocateFile") ($File.Name, $FileName) $Restore.RelocateFiles.Add($NewFileLoc) | Out-Null } } ForEach ($File in $DB.LogFiles) { $FileName = Split-Path $File.FileName -Leaf #Get just the filename $FileName = $Verify + $FileName $NewFileLoc = New-Object ("$SMO.RelocateFile") ($File.Name, $FileName) $Restore.RelocateFiles.Add($NewFileLoc) | Out-Null } $RelocateFiles = $Restore.RelocateFiles Write-Host "Restoring Full backup file $($Full.FullName) to $VerifyDB database" $Restore.SqlRestore($Server) $LastWriteTime = $Full.LastWriteTime # Get the most recent differential backup $Diff = $null $TestPath = $Server.Settings.BackupDirectory + "\" + $db.Name + "\Diff\" IF (Test-Path -Path $TestPath) { $Diff = Get-ChildItem $TestPath |` Sort-Object LastWriteTime -Descending |` Select-Object -First 1 |` Select-Object Name, FullName, LastWriteTime # if the latest diff was before the latest full, don't do anything with it. IF ($Diff.LastWriteTime -LT $Full.LastWriteTime) {$Diff = $null} ELSE {$LastWriteTime = $Diff.LastWriteTime} } # If there is one, IF ($Diff) { $BackupDevice = New-Object ("$SMO.BackupDeviceItem") ($Diff.FullName, 'File') $Restore = New-Object ("$SMO.Restore") $Restore.Checksum = $True $Restore.Database = "$VerifyDB" $Restore.Devices.Add($BackupDevice) $Restore.NoRecovery = $True ForEach ($RelocateFile in $RelocateFiles) {$Restore.RelocateFiles.Add($RelocateFile) | Out-Null} Write-Host "Restoring Differential backup file $($Diff.FullName) to $VerifyDB database" $Restore.SqlRestore($Server) } IF ($DB.RecoveryModel -ne "Simple") { # Restore all transaction logs written since the last full/diff backup $TestPath = $Server.Settings.BackupDirectory + "\" + $db.Name + "\Log\" IF (Test-Path -Path $TestPath) { ForEach ($File in Get-ChildItem $TestPath |` Where {$_.LastWriteTime -ge $LastWriteTime} |` Sort-Object LastWriteTime |` Select-Object Name, FullName ) { $BackupDevice = New-Object ("$SMO.BackupDeviceItem") ($File.FullName, 'File') $Restore = New-Object ("$SMO.Restore") $Restore.Checksum = $True $Restore.Database = "$VerifyDB" $Restore.Devices.Add($BackupDevice) $Restore.NoRecovery = $True ForEach ($RelocateFile in $RelocateFiles) {$Restore.RelocateFiles.Add($RelocateFile) | Out-Null} Write-Host "Restoring Transaction Log backup file $($File.FullName) to $VerifyDB database" $Restore.SqlRestore($Server) } } } # Bring the database online $Restore.NoRecovery = $False Write-Host "Bringing $VerifyDB database online" $Restore.SqlRestore($Server) Write-Host "Test Restore of the [$($db.name)] database passed" # This would be a good time to do a checkdb! } } |
If you are utilizing SMO from SQL Server 2012, a new method has been added: Restore-SqlDatabase. This cmdlet has parameters to control all aspects of the restore.
