Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #-------------------------------------------------------------------------------
- # 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"
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement