SQL Log shipping copy

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

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
}

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
}

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)
{
	$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
}