This might take you 10 minutes to read.

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.