Powershell log shipping

configGile.csv

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

logshipping_copy.ps1

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

logshipping_restore.ps1

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