SQL Log shipping restore¶
After last post we copy all backup files from the source to the destination. If all files is on the destination server we can now restore them into a database to complete the log shipping. I will not write about the functions that is the same in both files such as Import-MyModule. See last blog post if you have not read it.
Configuration¶
The same configuration file is the same as from the previous post. It is a good habit to copy the configuration file everytime a change is done, even if the change was only applied to either source or destination.
The script¶
The logshipping_restore.ps1 script is the one that is getting the data from the source server. The script is explained below.
The Stuff¶
This part is the script itself. We connect to the database server to have a database to execute the database backup file queries. We lookup the latest full backup and all backup files that is later. If there is log backups or differential backups we take that in consideration when creation the backup file chain. If the chain is broken the script copies everything. The scenario is that it could be a SQL cluster which is running the script locally on each node and copies to a single node. In that case it might be that we do not find the full backup but on the destination sever we can compile a complete backup chain.
Write-MyLogFile "### ########################## ###"
Write-MyLogFile "### New restore under progress ###"
Write-MyLogFile "### ########################## ###"
# Import SQLPS-module
Import-MyModule "SQLPS"
# Load configuration file and store it as configurations
[array]$configurations = Get-SQLConfiguration -configurationFile $configurationFilePath
$backupFileSourceFiles = New-Object System.Collections.ArrayList
# Iterate through the configurations array
foreach ($configuration in $configurations)
{
# Create a SQL connection and a restore object to work with
# File information is read via SQL API (SQL SMO)
$backupFileSQLSvr = New-Object -TypeName Microsoft.SQLServer.Management.Smo.Server($($configuration.secondaryDatabaseInstance))
$Restore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore
$DeviceType = [Microsoft.SqlServer.Management.Smo.DeviceType]::File
# Test if destination backup path exists
if (-not (Test-Path ("filesystem::$($configuration.secondaryBackupPath)")))
{
Write-MyLogFile -message "Cannot find secondaryBackupPath: $($configuration.secondaryBackupPath)."
Write-host "Cannot find secondaryBackupPath: $($configuration.secondaryBackupPath)." -ForegroundColor Yellow
}
# Read all files (recursivly) in the destination path
$backupFiles = get-childitem -Recurse ("filesystem::$($configuration.secondaryBackupPath)") -File
Write-MyLogFile -message "Loading files for: $($configuration.secondaryDatabaseName), total of $($backupFiles.Count)"
Write-Host "Loading files for: $($configuration.secondaryDatabaseName), total of $($backupFiles.Count)"
# Iterate through all files found under destination path
foreach ($backupFile in $backupFiles)
{
# Set a flag that everything is ok
$allGood = $true
# Test if powershell script can open the file (the file is not locked by other)
try
{
set-location $PSScriptRoot
[IO.File]::OpenWrite($(resolve-path $backupFile.fullname).providerpath).close()
}
catch
{
# The file was locked by other, set flag to no good
$allGood = $false
Write-MyLogFile -message "File $($backupfile.name) is in use. This file will NOT be included in the Restore."
Write-host "File $($backupfile.name) is in use. This file will NOT be included in the Restore." -ForegroundColor Yellow
}
# If file is ok to open
if ($allGood)
{
# Load file with SQL into an object
$RestoreDevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($backupFile.Fullname, $devicetype)
$Restore.Devices.Add($RestoreDevice)
# Get all header and metadata from file (File information is read via SQL API (SQL SMO))
$RestoreReadBackupHeader = $Restore.ReadBackupHeader($backupFileSQLSvr)
$restoreFileInformation = $restore.ReadFileList($backupFileSQLSvr)
# Get source names of the databasefiles (log and data)
$logicalDataNames = ($restoreFileInformation | where { $_.Type -eq "D" }).logicalname
$logicalLogNames = ($restoreFileInformation | where { $_.Type -eq "L" }).logicalname
write-host "Reading file: $($backupFile.fullname)"
# Create a backup file object with data from above and add it to an array
[void]$backupFileSourceFiles.add([pscustomobject]@{
Name = $backupFile.Name;
SourceFullname = $backupFile.FullName;
SourceComparison = "";
DestinationFullname = $backupFile.FullName;
DestinationComparison = "";
BackupType = $RestoreReadBackupHeader.BackupType; #1 = Database, 2 = Transaction Log, 4 = File, 5 = Differential Database
BackupStartDate = $RestoreReadBackupHeader.BackupStartDate;
ServerName = $RestoreReadBackupHeader.ServerName;
DatabaseName = $RestoreReadBackupHeader.DatabaseName;
DatabaseVersion = $RestoreReadBackupHeader.DatabaseVersion;
DatabaseCreationDate = $RestoreReadBackupHeader.DatabaseCreationDate;
FirstLSN = $RestoreReadBackupHeader.FirstLSN;
LastLSN = $RestoreReadBackupHeader.LastLSN;
CheckpointLSN = $RestoreReadBackupHeader.CheckpointLSN;
DatabaseBackupLSN = $RestoreReadBackupHeader.DatabaseBackupLSN;
Collation = $RestoreReadBackupHeader.Collation;
IsCopyOnly = $RestoreReadBackupHeader.IsCopyOnly;
DifferentialBaseLSN = $RestoreReadBackupHeader.DifferentialBaseLSN;
DifferentialBaseGUID = $RestoreReadBackupHeader.DifferentialBaseGUID;
BackupTypeDescription = $RestoreReadBackupHeader.BackupTypeDescription;
BackupSetGUID = $RestoreReadBackupHeader.BackupSetGUID;
IsRead = $false;
LogicalNameData = $logicalDataNames;
LogicalNameLog = $logicalLogNames;
})
# unload the file from restore object
[void]$Restore.Devices.Remove($restoredevice)
}
}
}
# Iterate through all database configurations again
foreach ($configuration in $configurations)
{
# Creates an array for all files to be restored for the current database
Write-MyLogFile -message "Start restoring database: $($configuration.secondaryDatabaseName)"
$restoreFiles = New-Object System.Collections.ArrayList
$restoreFiles.Clear()
Write-host ($configuration.secondaryDatabaseInstance + ", " + $configuration.secondaryDatabaseName) -ForegroundColor White
# Creates an arraylist for all files that will be relocated
$rfl = New-Object System.Collections.ArrayList
$i = 0
$backupFileSourceFiles = $backupFileSourceFiles | Sort-Object lastlsn
# Gets the last file for the current database and retrieves all data files and uses them for reloaction
foreach ($logicDataFileItem in (($backupFileSourceFiles | where { ($_.Databasename -eq $configuration.primarydatabasename) }) | select -First 1).LogicalNameData)
{
$smoRestoreDBFile = new-object('Microsoft.SqlServer.Management.Smo.RelocateFile')
$smoRestoreDBFile.LogicalFileName = $logicDataFileItem
$smoRestoreDBFile.PhysicalFileName = ($configuration.secondaryPathData + "\" + $configuration.secondaryDatabaseName + "_" + $i + "_Data.mdf")
$i = $i + 1
[void]$rfl.add($smoRestoreDBFile)
}
$i = 0
# Gets the last file for the current database and retrieves all log files and uses them for reloaction
foreach ($logicLogFileItem in (($backupFileSourceFiles | where { ($_.Databasename -eq $configuration.primarydatabasename) }) | select -First 1).LogicalNameLog)
{
$smoRestoreLogFile = new-object('Microsoft.SqlServer.Management.Smo.RelocateFile')
$smoRestoreLogFile.LogicalFileName = $logicLogFileItem
$smoRestoreLogFile.PhysicalFileName = ($configuration.secondaryPathLog + "\" + $configuration.secondaryDatabaseName + "_" + $i + "_Log.ldf")
$i = $i + 1
[void]$rfl.add($smoRestoreLogFile)
}
# Check if a current database already exists
if ((Get-SqlDatabase -ServerInstance $configuration.secondaryDatabaseInstance).name -contains $configuration.secondaryDatabaseName)
{
Write-Host "Checking if database $($configuration.secondaryDatabaseName) already exists" -ForegroundColor White
# If database existst retrieve last LSN that has been restored
$currentdatabase = Get-SqlDatabase -ServerInstance $configuration.secondaryDatabaseInstance -Name $configuration.secondaryDatabaseName
$query = "SELECT TOP 1 b.type, b.first_lsn, b.last_lsn, b.checkpoint_lsn, b.database_backup_lsn
FROM msdb..restorehistory a
INNER JOIN msdb..backupset b ON a.backup_set_id = b.backup_set_id
WHERE a.destination_database_name = '$($currentdatabase.Name)'
ORDER BY restore_date DESC"
$currentDatabaseLSN = Invoke-Sqlcmd -ServerInstance $configuration.secondaryDatabaseInstance -Database Master -Query $query
#Drop Connections to database if active connections found
if ($currentdatabase.ActiveConnections -gt 0)
{
Write-MyLogFile -message "Active connection found, dropping all active connections on database: $($configuration.secondaryDatabaseName)"
Write-Host "Active connection found, dropping all active connections!" -ForegroundColor Yellow
$backupFileSQLSvr.KillAllProcesses($configuration.secondaryDatabaseName)
}
#If database is in Standby mode restore only backupfiles from "latest" restore time
if ($currentdatabase.status -eq "Normal, Standby" -or $currentdatabase.status -eq "Restoring")
{
# If database has a lower "last_lsn" than the last fullbackup
if ($currentDatabaseLSN.last_lsn -lt ($backupFileSourceFiles | where { $_.Databasename -eq $configuration.primarydatabasename -and $_.Backuptype -eq 1 } | Sort-Object BackupStartDate -Descending)[0].lastlsn)
{
# Do a Full restore because the last restore is to old.
Write-MyLogFile -message "Database $($currentdatabase.name) is in $($currentdatabase.RecoveryModel) and will be fully restored due to the previous restore was to old for the restore-files.)"
# Get the newest full backup found for database
[void]$restoreFiles.add(($backupFileSourceFiles | where { $_.Databasename -eq $configuration.primarydatabasename -and $_.Backuptype -eq 1 } | Sort-Object BackupStartDate -Descending)[0])
if (($backupFileSourceFiles | where { $_.Databasename -eq $configuration.primarydatabasename -and $_.Backuptype -eq 5 -and $_.DifferentialBaseLSN -eq $restorefiles[-1].CheckpointLSN }).count -gt 0)
{
# Get all DIFF files that is newer than the FULL backup
[void]$restoreFiles.add(($backupFileSourceFiles | where { $_.Databasename -eq $configuration.primarydatabasename -and $_.Backuptype -eq 5 -and $_.DifferentialBaseLSN -eq $restorefiles[-1].CheckpointLSN } | Sort-Object lastlsn -Descending)[0])
}
# Get all LOG files that is newer than the FULL and DIFF backup
$FilesToBeRestored = @()
$FilesToBeRestored += ($backupFileSourceFiles | where { $_.Databasename -eq $configuration.primarydatabasename -and $_.Backuptype -eq 2 -and $_.lastlsn -gt $restorefiles[-1].lastlsn } | Sort-Object lastlsn)
foreach ($restorefile in $FilesToBeRestored)
{
if ($restorefile.firstlsn -ne $restorefile.lastlsn)
{
[void]$restoreFiles.add($restorefile)
}
}
}
else
{
write-host "Database $($currentdatabase.name) is currently, before restore, in status: [$($currentdatabase.status)]." -ForegroundColor Yellow
Write-MyLogFile -message "Database $($currentdatabase.name) is currently, before restore, in status: [$($currentdatabase.status)]."
# Get all log files that is newer than the last restore of the database
$FilesToBeRestored = @()
$FilesToBeRestored += ($backupFileSourceFiles | where { $_.Databasename -eq $configuration.primarydatabasename -and $_.Backuptype -eq 2 -and $_.lastlsn -gt $currentDatabaseLSN.last_lsn } | Sort-Object lastlsn)
foreach ($restorefile in $FilesToBeRestored)
{
if ($restorefile.firstlsn -ne $restorefile.lastlsn)
{
[void]$restoreFiles.add($restorefile)
}
}
}
}
else
{
write-host "Database $($currentdatabase.name) is in $($currentdatabase.RecoveryModel) and will be fully restored."
Write-MyLogFile -message "Database $($currentdatabase.name) is in $($currentdatabase.RecoveryModel) and will be fully restored."
# Get the latest FULL backup found for database
[void]$restoreFiles.add(($backupFileSourceFiles | where { $_.Databasename -eq $configuration.primarydatabasename -and $_.Backuptype -eq 1 } | Sort-Object BackupStartDate -Descending)[0])
# Get all diff files that is newer than the full backup
if (($backupFileSourceFiles | where { $_.Databasename -eq $configuration.primarydatabasename -and $_.Backuptype -eq 5 -and $_.DifferentialBaseLSN -eq $restorefiles[-1].CheckpointLSN }).count -gt 0)
{
[void]$restoreFiles.add(($backupFileSourceFiles | where { $_.Databasename -eq $configuration.primarydatabasename -and $_.Backuptype -eq 5 -and $_.DifferentialBaseLSN -eq $restorefiles[-1].CheckpointLSN } | Sort-Object lastlsn -Descending)[0])
}
# Get all LOG files that is newer than the FULL and DIFF backup
$FilesToBeRestored = @()
$FilesToBeRestored += ($backupFileSourceFiles | where { $_.Databasename -eq $configuration.primarydatabasename -and $_.Backuptype -eq 2 -and $_.lastlsn -gt $restorefiles[-1].lastlsn } | Sort-Object lastlsn)
foreach ($restorefile in $FilesToBeRestored)
{
if ($restorefile.firstlsn -ne $restorefile.lastlsn)
{
[void]$restoreFiles.add($restorefile)
}
}
}
}
else
{
# No database found, restoring complete backup
write-host "Database $($configuration.secondaryDatabaseName) not found! Doing a complete restore with starting from last FULL backup."
Write-MyLogFile -message "Database $($configuration.secondaryDatabaseName) not found! Doing a complete restore with starting from last FULL backup."
# Get the newest full backup found for database
[void]$restoreFiles.add(($backupFileSourceFiles | where { $_.Databasename -eq $configuration.primarydatabasename -and $_.Backuptype -eq 1 } | Sort-Object BackupStartDate -Descending)[0])
# Get all diff files that is newer than the full backup
if (($backupFileSourceFiles | where { $_.Databasename -eq $configuration.primarydatabasename -and $_.Backuptype -eq 5 -and $_.DifferentialBaseLSN -eq $restorefiles[-1].CheckpointLSN }).count -gt 0)
{
[void]$restoreFiles.add(($backupFileSourceFiles | where { $_.Databasename -eq $configuration.primarydatabasename -and $_.Backuptype -eq 5 -and $_.DifferentialBaseLSN -eq $restorefiles[-1].CheckpointLSN } | Sort-Object lastlsn -Descending)[0])
}
# Get all log files that is newer than the full and diff backup
$FilesToBeRestored = @()
$FilesToBeRestored += ($backupFileSourceFiles | where { $_.Databasename -eq $configuration.primarydatabasename -and $_.Backuptype -eq 2 -and $_.lastlsn -gt $restorefiles[-1].lastlsn } | Sort-Object lastlsn)
foreach ($restorefile in $FilesToBeRestored)
{
if ($restorefile.firstlsn -ne $restorefile.lastlsn)
{
[void]$restoreFiles.add($restorefile)
}
}
}
# All files that will be restored for the database is now collected in the restoreFiles array.
try
{
# Count if a restore is needed
if ($restoreFiles.Count -gt 0)
{
foreach ($file in $restoreFiles)
{
# Verify that the loop is not managing the last file, the last file will be handled lower in the script
if ($file -ne $restoreFiles[-1])
{
# Do a restore
Write-host "Restoring file $($file.DestinationFullname) with Restore-SqlDatabase -ReplaceDatabase -NoRecovery to database $($configuration.secondaryDatabaseName)" -ForegroundColor White
Write-MyLogFile -message "Restoring file $($file.DestinationFullname) to database $($configuration.secondaryDatabaseName)"
Restore-SqlDatabase -ServerInstance $configuration.secondaryDatabaseInstance -Database $configuration.secondaryDatabaseName -BackupFile $file.DestinationFullname -ReplaceDatabase -NoRecovery -RelocateFile $rfl
}
}
# Last file will be restored
# do different settings with different restoreoptions
if ($configuration.restoreOptions -eq "norecovery")
{
write-host "RestoreOption = norecovery. Restoring file $($file.DestinationFullname) with Restore-SqlDatabase -ReplaceDatabase -NoRecovery to database $($configuration.secondaryDatabaseName)" -ForegroundColor Green
Write-MyLogFile -message "RestoreOption = norecovery. Restoring file $($file.DestinationFullname) with Restore-SqlDatabase -ReplaceDatabase -NoRecovery"
Restore-SqlDatabase -ServerInstance $configuration.secondaryDatabaseInstance -Database $configuration.secondaryDatabaseName -BackupFile $file.DestinationFullname -ReplaceDatabase -NoRecovery -RelocateFile $rfl
}
elseif ($configuration.restoreOptions -eq "standby")
{
write-host "RestoreOption = standby. Restoring file $($file.DestinationFullname) with in standby with Restore-SqlDatabase -ReplaceDatabase -standbyfile to database $($configuration.secondaryDatabaseName)" -ForegroundColor Green
Write-MyLogFile -message "RestoreOption = standby. Restoring file $($file.DestinationFullname) with in standby with Restore-SqlDatabase -ReplaceDatabase -standbyfile"
$backupFileQLLogPath = $backupFileSQLSvr.DefaultLog
Restore-SqlDatabase -ServerInstance $configuration.secondaryDatabaseInstance -Database $configuration.secondaryDatabaseName -BackupFile $file.DestinationFullname -ReplaceDatabase -StandbyFile ($backupFileQLLogPath + "\" + $configuration.secondaryDatabaseName + ".standby") -RelocateFile $rfl
}
elseif ($configuration.restoreOptions -eq "recovery")
{
write-host "RestoreOption = recovery. Restoring file $($file.DestinationFullname) with Restore-SqlDatabase -ReplaceDatabase With recovery to database $($configuration.secondaryDatabaseName)" -ForegroundColor Green
Write-MyLogFile -message "RestoreOption = recovery.Restoring file $($file.DestinationFullname) with Restore-SqlDatabase -ReplaceDatabase With recovery"
Restore-SqlDatabase -ServerInstance $configuration.secondaryDatabaseInstance -Database $configuration.secondaryDatabaseName -BackupFile $file.DestinationFullname -ReplaceDatabase -RelocateFile $rfl
}
else
{
write-host "Wrong setting in the Configration file. Restore option [$($configuration.restoreOptions)] not supported." -ForegroundColor Red
Write-MyLogFile -message "Wrong setting in the Configration file. Restore option [$($configuration.restoreOptions)] not supported."
}
Write-MyLogFile -message "Restore of $($configuration.secondaryDatabaseName) is done"
}
else
{
Write-host "No new files found for $($configuration.secondaryDatabaseName), Skipping Restore" -ForegroundColor Yellow
Write-MyLogFile -message "No new files found for $($configuration.secondaryDatabaseName), Skipping Restore"
}
}
catch [Exception]
{
write-host -ForegroundColor Red $_.Exception.message
Write-MyLogFile -message $_.Exception.message
}
# if the setting deleteFiles is set, delete all files older than the latest full backup
if ($configuration.deleteFiles -eq "true" -and ([array]($backupFileSourceFiles | where { $_.Databasename -eq $configuration.primaryDatabaseName -and $_.Backuptype -eq 1 })).count -gt 1)
{
$lastlsn = (($backupFileSourceFiles | where { $_.Databasename -eq $configuration.primaryDatabaseName -and $_.Backuptype -eq 1 } | Sort-Object BackupStartDate -Descending)[0]).lastlsn
$eraseFiles = $backupFileSourceFiles | where { $_.Databasename -eq $configuration.primaryDatabaseName -and $_.lastlsn -lt $lastlsn }
foreach ($file in $eraseFiles)
{
Write-MyLogFile -message "Delete file: $($file.name)"
write-host -ForegroundColor White "Delete file: $($file.name)"
Remove-Item ("filesystem::$($file.DestinationFullname)")
}
}
}
set-location $PSScriptRoot
Remove-Module sqlps #sqlps Will make PowerShell behave differently so therefore it must be unloaded!
foreach ($logfile in Get-ChildItem "$PSScriptRoot\logs\" -Recurse | Where-Object { -not $_.PSIsContainer -and (Get-Date).Subtract($_.CreationTime).Days -gt $logfilesAge })
{
Write-MyLogFile -message "Delete file: $($logfile.fullname)"
write-host -ForegroundColor White "Delete file: $($logfile.fullname)"
$logfile | Remove-Item
}
Thats it! We have restored the backup into the destination server. Schedule this and the log shipping feature is done. It can also be used to migrate databases between database servers.