This is a two blog post divided into two posts. The second will be published within a week from this one. The script was created a while ago and has some minor updates.
Problem
The need was found to have two database servers pretty similar, and we did not have license for SQL Log shipping. And either way we could not use it as the system setup was. The idea came that we could use existing backups, since we did a log backup each hour, and copy them to another server and restore them there. It seemed pretty easy at first, later when going further into this project the discovery was done, at least by me, that SQL backups can be quite difficulty. And also if we take in the game locally run scripts on SQL-clusters.
Pre requirements
The SQL servers the script was setup to work with was SQL 2014 although some tweaks it seems to work with other SQL versions as well. The setup of the SQL servers I am not qualified to give any input in. We need a source server and a destination server. The account running the backup script needs to have executing access in the servers (it does not have to be the same account on the servers).
The backup that we use is Ola Hallengrens backup scripts that will store the backup files onto disk. One full backup each night and then on log backup each hour. All files is stored on a separate disk. Full backups in one folder and log and diff in another. Three versions of full and all their log backup files.
The solution
The solution is based on two scripts. One that is triggered on the source server and one that is triggered on the destination server. The script solves many issues that can occur with som nice touches. It can rename the database during the move, change instances, duplicate and also move between different SQL versions.
The copy script does a bit more than just copies the backup files. It open up all backup files and verifies that it is correct database that is copied. It does also verify the backup chain. If the backup chain verification failed all backups is copied to the destination server, this can be handy if an failover SQL cluster is setup and the backup files and tasks in on each local server. The result should be in that case the destination has all files since the backup is done completely but on two different source servers.
This post will explain the first one, the source server script.
Configuration
The configuration of the copy script is done via csv-file that holds configuration items for both copy script and restore script.
sourceDatabaseInstance The name of the source server and if needed the instance name of which the database resides. destinationDatabaseInstance The name of the destination server and if needed the instance name of which the database will be installed on. sourceBackupPath The path on the source server where the backup files is stored. destinationBackupPath The path on the destination server where the backup files is stored. replicationPath From the source server, the path the copy script will copied to. (From the source server, the destinationBackupPath.) sourceDatabaseName The name of the database on the source server, that will be copied. destinationDatabaseName The name of the database on the destination server that should be used. This is not necessary the same name as sourceDatabaseName. restoreOptions In what state shall the restore script leave the restored database be left in. The options are "Recover", "noRecovery" and "standby". sourceDatabaseLog Where on the source server will the log files be placed. destinationDatabaseLog Where on the destination server will the log files be placed. FileCopyAges How many version of backups will be copied to the destination server. deleteFiles Should the restoreScript delete old backup files.
The configuration file is to be used on both destination and source server.
The script
The logshipping_copy.ps1 script is the one that is getting the data from the source server. The script is explained below.
Import-MyModule
This is a generic module to import a Powershell module. The difference is that it will produce input to a log file using my log function.
function Import-MyModule
{
param ([Parameter(Mandatory = $true)]
[string]$name)
Write-MyLogFile "Starting to import module: $name"
Write-Host "Starting to import module: $name" -ForegroundColor White
if (-not (Get-Module -name $name)) {
if (Get-Module -ListAvailable | Where-Object {
$_.name -eq $name
})
{
Import-Module -Name $name -DisableNameChecking
}
else
{
Write-Host "Cannot find Powershell Module: $name" -ForegroundColor Red
Write-Host "Prerequisites: .Net 3.5 & PowerShell Extensions, you might also install the Shared Management Objects (SMO) which in turn requires the System CLR Types. These all are found in: MS SQL Server 2012 Features Pack" -ForegroundColor Yellow
Write-MyLogFile -message "Cannot find Powershell Module: $name"
break
}
}
}
Write-MyLogFile
This is a function that will take the input and then output it to a log file with todays date on.
function Write-MyLogFile
{
param ([Parameter(Mandatory = $true)]
[string]$message,
[string]$path = "$PSScriptRoot\logs\logshipping.log")
$path = $path.Replace(".log", ".$(get-date -Format "yyyyMMdd").log")
if (! (Test-Path $path))
{
New-Item -Path $path -ItemType file -Force
}
$message = ((Get-Date -Format "yyyy-MM-dd HH:mm:ss") + ";" + $message)
Add-Content -Path $path -Value $message
}
Get-ConfigurationFile
This on reads the configuration file and it verifies that all needed values is set. it does not verify that all variables is valid. If it does not work it outputs to prompt and to the log file.
function Get-ConfigurationFile
{
param ([Parameter(Mandatory = $true)]
[ValidateScript({
test-path $_
})]
[System.IO.FileSystemInfo]$configurationFile)
$configurations = New-Object System.Collections.ArrayList
try
{
$configFile = Import-Csv $configurationFile -Delimiter ";" -erroraction Stop
}
catch
{
Write-Host "Could not find configuration File: $($configurationFile)." -ForegroundColor Red
Write-MyLogFile -message "Could not find configuration File: $($configurationFile)."
break
} # Validation that all parameters exists in configurationFile
foreach ($databaseRow in $configFile)
{
if ($databaseRow.primaryDatabaseInstance -and `
$databaseRow.secondaryDatabaseInstance -and `
$databaseRow.primaryBackupPath -and `
$databaseRow.secondaryBackupPath -and `
$databaseRow.replicationPath -and `
$databaseRow.primaryDatabaseName -and `
$databaseRow.secondaryDatabaseName -and `
$databaseRow.restoreOptions -and `
$databaseRow.servername -and `
$databaseRow.fileComparison -and `
$databaseRow.deleteFiles -and `
$databaseRow.secondaryPathData -and `
$databaseRow.secondaryPathLog)
{
if (-not ($databaseRow.deletefiles))
{
$databaseRow.deletefiles = $false
}
[void]$configurations.add($databaserow)
}
else
{
write-host "Could not load configuration row" -Foregroundcolor Red
write-mylogfile -message "Could not load configuration row."
}
}
return $configurations
}
Copy-BackupFile
The copy part of the file does what it says. It takes the source file and copies it if there is no file on the destination file either does not exist or does not compare.
function copy-backupfile
{
param ([Parameter(Mandatory = $true)]
$File) # 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 $File.SourceFullname).providerpath).close()
}
catch
{
# The file was locked by other, set flag to no good
$allGood = $false
Write-MyLogFile -message "File $($File.SourceFullname) is in use. This file will NOT be copied."
Write-host "File $($File.SourceFullname) is in use. This file will NOT be copied." -ForegroundColor Yellow
}
if ($File.DestinationComparison -eq "" -and $allGood)
{
$destinationFolderPath = $File.DestinationFullname.TrimEnd($File.name)
if (!(test-path $destinationFolderPath))
{
Write-MyLogFile -message "Folder structure non existing, creating it: $($file.DestinationFullname)"
Write-Host "Folder structure non existing, creating it: $($file.DestinationFullname)"
$aParent = ($destinationFolderPath | Split-Path -Parent)
$aLeaf = ($destinationFolderPath | Split-Path -Leaf)
New-Item -Path $aParent -name $aLeaf -ItemType Directory -Force
}
copy-item -Path $File.SourceFullname -Destination $File.DestinationFullname
Write-MyLogFile -message "File is copied to: $($file.DestinationFullname)"
write-host "File is copied to: $($file.DestinationFullname)"
}
elseif ($File.DestinationComparison -ne $File.SourceComparison -and $allGood)
{
copy-item -path $file.SourceFullname -Destination $file.DestinationFullname
Write-MyLogFile -message "File is re-copied due to not comparible: $($file.DestinationFullname)"
Write-Host "File is re-copied due to not comparible: $($file.DestinationFullname)"
}
else
{
Write-Host "$($File.SourceFullname) + " is Not Copied, Already exists.""
}
}
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 copy under progress ###"
Write-MyLogFile "### ####################### ###"
# Imports the SQLPS module
Import-MyModule "SQLPS"
# Creates array for all backupfiles
$backupFileSourceFiles = New-Object System.Collections.ArrayList
# Gets all configuration data from configuration file
[array]$configurations = Get-ConfigurationFile -configurationFile $configurationFilePath
# Connects to the SQL Server to crunch SQL restore data
# File information is read via SQL API (SQL SMO)
$SQLSvr = New-Object -TypeName Microsoft.SQLServer.Management.Smo.Server($($configurations[0].primaryDatabaseInstance))
$Restore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore
$DeviceType = [Microsoft.SqlServer.Management.Smo.DeviceType]::File
# Iterates through the configurations for all databases
foreach ($configuration in $configurations)
{
Write-Host "Reading Files for Database : $($configuration.primaryDatabaseName)"
Write-MyLogFile "Reading Files for Database : $($configuration.primaryDatabaseName)"
# Get all source files (recursive) for the database
$backupFiles = get-childitem -Recurse ("filesystem::$($configuration.primaryBackupPath)") -File
Write-Host "Found $($backupFiles.Count) files for database $($configuration.primaryDatabaseName)."
Write-MyLogFile "Found $($backupFiles.Count) files for database $($configuration.primaryDatabaseName)."
foreach ($backupFile in $backupFiles)
{
# reads all headers and metadata from the backup files (File information is read via SQL API (SQL SMO))
$RestoreDevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($backupFile.FullName, $devicetype)
$Restore.Devices.Add($RestoreDevice)
$RestoreReadBackupHeader = $Restore.ReadBackupHeader($sqlsvr)
# generates a destinationpath
$dPath = ($configuration.replicationPath + ($backupFile.FullName).substring($configuration.primaryBackupPath.length, $backupFile.FullName.length - $configuration.primaryBackupPath.length))
# testst if file exists
if (test-path filesystem::$dPath)
{
# uses only name to compare. Using real MD5 took to long time
$destinationComparison = (get-item filesystem::$dpath).name
}
else
{
$destinationComparison = ""
}
# Creates a Backup file object and stores it in an array
[void]$backupFileSourceFiles.add([pscustomobject]@{
Name = $backupFile.Name; SourceFullname = $backupFile.FullName; SourceComparison = $backupFile.Name; DestinationFullname = $dPath; DestinationComparison = $destinationComparison; BackupType = $RestoreReadBackupHeader.BackupType; 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
})
# unloads the file
[void]$Restore.Devices.Remove($restoredevice)
}
}
set-location $PSScriptRoot
Remove-Module sqlps
#sqlps Will make PowerShell behave differently so therefore it must be unloaded!
# Iterates through the configurations for all databases
foreach ($configuration in $configurations)
{
$lastFullBackup = ($backupFileSourceFiles | Where-Object {
$_.backuptype -eq 1 -and $_.DatabaseName -eq $configuration.primaryDatabaseName
} | sort-object lastlsn -Descending) | Select-Object -first 1
# compare if source and destionation file is equal.
if ($lastFullBackup.SourceComparison -ne $lastFullBackup.DestinationComparison)
{
write-host "Handling FULL Backup $($lastFullBackup.name)"
# Copying file
copy-backupfile $lastFullBackup
}
else
{
write-host "No FULL backup file(s) was found for database $($configuration.primaryDatabaseName) or all files exists at destination, no action needed!"
Write-MyLogFile "No FULL backup file(s) was found for database $($configuration.primaryDatabaseName) or all files exists at destination, no action needed!"
}
if ($lastFullBackup)
{
# Hämtar ut alla LOG backuper sedan förra FULL backupen (om den hittas)
foreach ($LogBackup in ($backupFileSourceFiles | Where-Object {
($_.backuptype -eq 2 -or $_.backuptype -eq 5) -and $_.DatabaseName -eq $configuration.primaryDatabaseName -and $_.SourceComparison -ne $_.DestinationComparison -and $_.LastLSN -gt $lastFullBackup.LastLSN
} | sort-object lastlsn))
{
write-host "Handling $($LogBackup.name)"
# Copying file
copy-backupfile $LogBackup
}
$LogFilesFound = ($backupFileSourceFiles | Where-Object {
($_.backuptype -eq 2 -or $_.backuptype -eq 5) -and $_.DatabaseName -eq $configuration.primaryDatabaseName -and $_.SourceComparison -ne $_.DestinationComparison -and $_.LastLSN -gt $lastFullBackup.LastLSN
}).count
Write-Host "There was $LogFilesFound Nr of files found that needed to be copied for database $($configuration.primaryDatabaseName)."
Write-MyLogFile "There was $LogFilesFound Nr of files found that needed to be copied for database $($configuration.primaryDatabaseName)."
}
else
{
# Hämtar ut alla LOG backuper då FULL backup inte hittades
write-host "No FULL backup file found, copying all LOG files."
foreach ($LogBackup in ($backupFileSourceFiles | Where-Object {
$_.backuptype -eq 2 -and $_.DatabaseName -eq $configuration.primaryDatabaseName -and $_.SourceComparison -ne $_.DestinationComparison
} | sort-object lastlsn)) {
write-host "Handling $($LogBackup.name)"
# Copying file
copy-backupfile $LogBackup
}
}
}
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.name)"
write-host -ForegroundColor White "Delete file: $($logfile.name)"
$logfile | Remove-Item
}
The script has some limitation with the amount of servers the script copies from due to the connection is only done once.
The script can be seen in whole at gist.