Skip to content

SQL Log Shipping Copy

This is a two-part blog post. The second part will be published within a week. The script was created a while ago and has some minor updates.

Problem

The need was found to have two database servers very similar, without a license for SQL Log shipping. We could use existing backups, as we did a log backup each hour, and copy them to another server for restoration. The process turned out to be more complex than anticipated, especially with SQL clusters.

Pre-requirements

The script was set up for SQL 2014 but can work with other SQL versions with tweaks. We need a source server and a destination server. The account running the backup script must have execution access on both servers.

The backup uses Ola Hallengren's backup scripts, storing backup files on disk—one full backup each night and a log backup each hour. Files are stored on a separate disk, with full backups in one folder and log and diff backups in another.

Solution

The solution involves two scripts: one for the source server and one for the destination server. The source script handles more than just copying backup files. It verifies the backup chain and can handle different SQL versions.

Configuration

The configuration is managed via a CSV file with items for both copy and restore scripts.

  • sourceDatabaseInstance: The name of the source server and instance.
  • destinationDatabaseInstance: The name of the destination server and instance.
  • sourceBackupPath: Path on the source server for backup files.
  • destinationBackupPath: Path on the destination server for backup files.
  • replicationPath: Path the copy script will copy to from the source server.
  • sourceDatabaseName: Name of the source database.
  • destinationDatabaseName: Name of the destination database.
  • restoreOptions: State of the restored database (Recover, noRecovery, standby).
  • sourceDatabaseLog: Log files path on the source server.
  • destinationDatabaseLog: Log files path on the destination server.
  • FileCopyAges: Number of backup versions to copy.
  • deleteFiles: Whether the restore script should delete old backup files.

Script Explanation

Import-MyModule

Imports a PowerShell module and logs the action.

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

Logs a message to a log file with today's date.

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

Reads the configuration file and verifies necessary values are set.

```powershell
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
    }
    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

Copies the backup file if it doesn't exist or doesn't compare with the destination file.

function copy-backupfile {
    param ([Parameter(Mandatory = $true)] $File)
    $allGood = $true
    try {
        set-location $PSScriptRoot
        [IO.File]::OpenWrite($(resolve-path $File.SourceFullname).providerpath).close()
    } catch {
        $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

The script itself connects to the database server, retrieves backup files, and handles copying them.

Write-MyLogFile "### ####################### ###"
Write-MyLogFile "### New copy under progress ###"
Write-MyLogFile "### ####################### ###"

Import-MyModule "SQLPS"

$backupFileSourceFiles = New-Object System.Collections.ArrayList
[array]$configurations = Get-ConfigurationFile -configurationFile $configurationFilePath

$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

foreach ($configuration in $configurations) {
    Write-Host "Reading Files for Database : $($configuration.primaryDatabaseName)"
    Write-MyLogFile "Reading Files for Database : $($configuration.primaryDatabaseName)"
    $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) {
        $RestoreDevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($backupFile.FullName, $devicetype)
        $Restore.Devices.Add($RestoreDevice)
        $RestoreReadBackupHeader = $Restore.ReadBackupHeader($sqlsv

Here is a markdown version of the blog post:

```markdown
# SQL Log Shipping Copy

This is a two-part blog post. The second part will be published within a week. The script was created a while ago and has some minor updates.

## Problem

The need was found to have two database servers very similar, without a license for SQL Log shipping. We could use existing backups, as we did a log backup each hour, and copy them to another server for restoration. The process turned out to be more complex than anticipated, especially with SQL clusters.

## Pre-requirements

The script was set up for SQL 2014 but can work with other SQL versions with tweaks. We need a source server and a destination server. The account running the backup script must have execution access on both servers.

The backup uses [Ola Hallengren's backup scripts](https://ola.hallengren.com), storing backup files on disk—one full backup each night and a log backup each hour. Files are stored on a separate disk, with full backups in one folder and log and diff backups in another.

## Solution

The solution involves two scripts: one for the source server and one for the destination server. The source script handles more than just copying backup files. It verifies the backup chain and can handle different SQL versions.

## Configuration

The configuration is managed via a CSV file with items for both copy and restore scripts.

- `sourceDatabaseInstance`: The name of the source server and instance.
- `destinationDatabaseInstance`: The name of the destination server and instance.
- `sourceBackupPath`: Path on the source server for backup files.
- `destinationBackupPath`: Path on the destination server for backup files.
- `replicationPath`: Path the copy script will copy to from the source server.
- `sourceDatabaseName`: Name of the source database.
- `destinationDatabaseName`: Name of the destination database.
- `restoreOptions`: State of the restored database (`Recover`, `noRecovery`, `standby`).
- `sourceDatabaseLog`: Log files path on the source server.
- `destinationDatabaseLog`: Log files path on the destination server.
- `FileCopyAges`: Number of backup versions to copy.
- `deleteFiles`: Whether the restore script should delete old backup files.

## Script Explanation

### Import-MyModule

Imports a PowerShell module and logs the action.

```powershell
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

Logs a message to a log file with today's date.

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

Reads the configuration file and verifies necessary values are set.

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
    }
    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

Copies the backup file if it doesn't exist or doesn't compare with the destination file.

function copy-backupfile {
    param ([Parameter(Mandatory = $true)] $File)
    $allGood = $true
    try {
        set-location $PSScriptRoot
        [IO.File]::OpenWrite($(resolve-path $File.SourceFullname).providerpath).close()
    } catch {
        $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 Script

The script connects to the database server, retrieves backup files, and manages copying them. It begins by logging the start of the process, importing the necessary module, and getting the configuration file.

Write-MyLogFile "### ####################### ###"
Write-MyLogFile "### New copy under progress ###"
Write-MyLogFile "### ####################### ###"

Import-MyModule "SQLPS"

$backupFileSourceFiles = New-Object System.Collections.ArrayList
[array]$configurations = Get-ConfigurationFile -configurationFile $configurationFilePath

$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

foreach ($configuration in $configurations) {
    Write-Host "Reading Files for Database : $($configuration.primaryDatabaseName)"
    Write-MyLogFile "Reading Files for Database : $($configuration.primaryDatabaseName)"
    $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) {
        $RestoreDevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($backupFile.FullName, $devicetype)
        $Restore.Devices.Add($RestoreDevice)
        $RestoreReadBackupHeader = $Restore.ReadBackupHeader($SQLSvr)
        foreach ($BackupSet in $RestoreReadBackupHeader) {
            if ($BackupSet.BackupSetType -eq "Log") {
                $backupFileSourceFiles.Add([PSCustomObject]@{
                    DatabaseName = $configuration.primaryDatabaseName
                    SourceFullname = $backupFile.FullName
                    DestinationFullname = ($configuration.replicationPath + "\" + $backupFile.Name)
                    SourceComparison = ($BackupSet.FirstLsn.ToString() + $BackupSet.LastLsn.ToString())
                    DestinationComparison = ""
                })
            }
        }
    }
}

foreach ($File in $backupFileSourceFiles) {
    Copy-BackupFile -File $File
}

This script ensures that backup files are copied from the source to the destination server, comparing file versions and handling any discrepancies. Each backup file's header is read to identify log backups specifically, which are then processed for copying. The Copy-BackupFile function is used to manage the actual file transfer, ensuring that the destination is updated correctly without duplicating existing files.