Skip to content

Instantly share code, notes, and snippets.

@dvlin

dvlin/configGile.csv

Last active Mar 12, 2020
Embed
What would you like to do?
Powershell log shipping
We can make this file beautiful and searchable if this error is corrected: No commas found in this CSV file in line 0.
configurationID;sourceDatabaseInstance;destinationDatabaseInstance;sourceBackupPath;destinationBackupPath;replicationPath;sourceDatabaseName;destinationDatabaseName;restoreOptions;sourceDatabaseLog;destinationDatabaseLog;FileCopyAges;parameterdatabas;servername;FileComparison
123;sourceSQL;destinationSQL;H:\MSSQL\BACKUP\sourceSQL\DB1;H:\MSSQL\BACKUP\sourceSQL\DB1;\\destinationsql\MSSQL$\BACKUP\sourceSQL\DB1;DB1;DB1;recovery;;none;none;none;sourceSQL;File
# Powershell SQL Logshipping-Copy
#
# Created by David Lindin
# Modified by by Christopher Serre, Axians.se
# Version 1.0.5
# Last updated 2017-02-26
# LogShipping consists of TWO parts Logshipping-Copy.ps1 & Logshipping-Restore.ps1.
# Logshipping-Copy.ps1 copies SQL backup files from existing paths to a secondary server and remains the folder structure accordingly to the local configfile.csv.
# Logshipping-Restore.ps1 will then Restore SQL databases accordingly to the local configfile.csv.
#
#Requires -Version 3.0
$logfilesAge = 30 # days
set-location $PSScriptRoot
try
{
$configurationFilePath = Get-Item "$PSScriptRoot\configfile.csv" -erroraction Stop
}
catch
{
Write-Host "ERROR! Could not find configuration FilePath: $($configurationFilePath)." -ForegroundColor Red
Write-MyLogFile -message "ERROR! Could not find configuration FilePath: $($configurationFilePath)."
break
}
<#
.SYNOPSIS
Imports powershellmodule if not loaded
.DESCRIPTION
A detailed description of the Import-MyModule function.
.PARAMETER name
A description of the name parameter.
.NOTES
Additional information about the 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
}
}
}
<#
.SYNOPSIS
Writes message to a log file
.DESCRIPTION
A detailed description of the Write-MyLogFile2 function.
.PARAMETER message
Message that will be stored i log file.
.PARAMETER path
Path to log file.
.NOTES
Additional information about the function.
#>
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
}
<#
.SYNOPSIS
Retrieves configuration from CSV-file
.DESCRIPTION
Retirives configuration for the Log shipping from a CSV-file.
.PARAMETER configurationFile
Path to CSV-file
.NOTES
Additional information about the function.
#>
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)
}
}
return $configurations
}
<#
.SYNOPSIS
Copies files that are missing on destination location
.DESCRIPTION
If needed files are missing then they will be copied.
If all files are equal on source & destinaton - No action is needed!
.PARAMETER file
$File = Custum FileObject
.NOTES
Additional information about the function.
#>
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.""
}
}
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)
{
# Hämtar ut senaste FULL backupen (om den hittas)
$lastFullBackup = ($backupFileSourceFiles | where { $_.backuptype -eq 1 -and $_.DatabaseName -eq $configuration.primaryDatabaseName } | sort-object lastlsn -Descending) | select -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 { ($_.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 { ($_.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 { $_.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
}
# Powershell SQL Logshipping - Restore
#
# Created by David Lindin
# Modified by by Christopher Serre, Axians.se
# Version 1.0.8
# Last updated 2017-02-26
# LogShipping consists of TWO parts Logshipping-Copy.ps1 & Logshipping-Restore.ps1.
# Logshipping-Copy.ps1 copies SQL backup files from existing paths to a secondary server and remains the folder structure accordingly to the local configfile.csv.
# Logshipping-Restore.ps1 will then Restore SQL databases accordingly to the local configfile.csv.
#
#Requires -Version 3.0
$logfilesAge = 30 # days
set-location $PSScriptRoot
try
{
$configurationFilePath = Get-Item "$PSScriptRoot\configfile.csv" -erroraction Stop
}
catch
{
Write-Host "ERROR! Could not find configuration FilePath: $($configurationFilePath)." -ForegroundColor Red
Write-MyLogFile -message "ERROR! Could not find configuration FilePath: $($configurationFilePath)."
break
}
<#
.SYNOPSIS
Imports powershellmodule if not loaded
.DESCRIPTION
The function verifies that the module is not already loaded.
If the function is to be loaded, it loads the module without checking names.
.PARAMETER name
Name of the powershell module to load
.NOTES
Additional information about the 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
}
}
}
<#
.SYNOPSIS
Writes message to a log file
.DESCRIPTION
The function writes data to a log file. If the log file is not found a new log file is created.
the date format of log files is logfile and date.
.PARAMETER message
Message that will be stored i log file.
.PARAMETER path
Path to log file.
.NOTES
Additional information about the function.
#>
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
}
<#
.SYNOPSIS
Retrieves configuration from CSV-file
.DESCRIPTION
Retirives configuration for the Log shipping from a CSV-file.
The file is delimited with ";".
The function also checks for important values are set.
The return of the function is an array with Custom configuration objects.
.PARAMETER configurationFile
Path to CSV-file
.NOTES
Additional information about the function.
#>
function Get-SQLConfiguration
{
param
(
[Parameter(Mandatory = $true)]
[ValidateScript({ test-path $_ })]
[string]$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-MyLogFile -message "Missing vital configuration values in configuration file."
Write-Host "Missing vital configuration values in configuration file." -ForegroundColor Red
}
}
return $configurations
}
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
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.