#-------------------------------------------------------------------------------
# Desc: Copy last 24 hours of SQL log files to a disaster recovery (DR) server.  Run this on the DR server.
#       Prunes files older than 24 hours on DR server to prevent bloat.
# License: ShoutOutWare - give me a shout out on Twitter @bdill if this script helped you. :)
# Script home: https://pastebin.com/QSf2WG1F
# Other useful files: https://pastebin.com/u/bdill (scripts, data files, etc.) 
# Auth: Brian Dill 
# Created: 2020-04-14
# Updated: 2020-12-03 added some comments for clarity
#-------------------------------------------------------------------------------

cls
$script:error.clear()
#-------------------------------------------------------------------------------
# CONFIG PARAMS
#-------------------------------------------------------------------------------
$CurrentDate  = Get-Date -Format yyyy-MM-dd_hhmm
$ScriptName   = "ps_copy_sql_log_files.ps1"
$LocalPath  = "W:\LogShip\"    # Base path on the DR server where the log files will be copied.  Create a sub folder for each DB.
$LogFile      = [System.IO.Path]::Combine($LocalPath, "ps_copy_sql_log_files.log")  # Logfile of *this script* not SQL log files
$SessionLogContainer = ""  #Holding tank for all session LogLine data
#-------------------------------------------------------------------------------
function LogLine 
{ param ( $s )
	$d2 = Get-Date -format "yyyy-MM-dd HH:mm:ss";
	$s = "$d2 - $s";
	Write-Host "$s";
	Echo "$s" | Out-File $LogFile -append;
    $script:SessionLogContainer += $s + "`n"
}
#-------------------------------------------------------------------------------
function CopyLatestLogFiles {
    param([string]$DBName, [string]$SourceFolder)

    $HoursBack = 24
    $Destination = [System.IO.Path]::Combine($LocalPath, $DBName, "log")
    LogLine("Copying $SourceFolder")

    $i = 0
    Get-ChildItem $SourceFolder | 
        Where-Object {$_.LastWriteTime -gt (get-date).AddHours(-1*($HoursBack))} | 
        ForEach {
            $ModifiedDestination = $($_.FullName).Replace("$SourceFolder","$Destination")
            If ((Test-Path $ModifiedDestination) -eq $False) {  # if the file has not already been copied
                Copy-Item $_.FullName $ModifiedDestination
                $i = $i + 1
            }
        }

    LogLine("Copied $i files from $SourceFolder")
    DelFilesOlderThan $HoursBack $Destination

}
#-------------------------------------------------------------------------------
function DelFilesOlderThan {
    Param( [int]$HoursBack, [string]$Path )
    LogLine("Deleting files older than $HoursBack hours from $Path")
    $DatetoDelete = (Get-Date).AddHours(-1*($HoursBack))
    Get-ChildItem -File $Path | Where-Object { $_.LastWriteTime -lt $DatetoDelete } | Remove-Item
}
#-------------------------------------------------------------------------------

#-------------------------------------------------------------------------------

LogLine "--------------------------------------------------------------------------------"
LogLine "(Starting) $ScriptName"
CopyLatestLogFiles "DB1" "\\server1\D$\Backups\DB1\LOG"
CopyLatestLogFiles "DB2" "\\server1\D$\Backups\DB1\LOG"
CopyLatestLogFiles "DB3" "\\server1\D$\Backups\DB3\LOG"
LogLine "(complete) $ScriptName"