I would like to share with you a twoScripts I have design to copy MSSQL dumps and store them to a remote Linux Samba Server as a Cold Storage Solution, as you can understand all the Servers were Windows Servers and that why I have decided to create this small automation scripts in Powershell.
Transfer the Files to the Cold Storage File Server.
Part1: The Launcher
$str_smb_mapp=(Get-SmbMapping | select LocalPath,RemotePath)
if($str_smb_mapp -like '*db-backups*') {
Write-Host 'Close Stucked SMB Share on the \\<hostname/ip>\db-backups'
Remove-SmbMapping -RemotePath "\\<hostname/ip>\db-backups" -Force
}
#- Verify Last 16 files
$global:SourceDir = "D:\MovedBackups\"
$files=(Get-ChildItem -Filter "*.bak" -Path $global:SourceDir | sort LastWriteTime | select -last 16 | select -first 16 name, @{Name="Gigabytes";Expression={[Math]::round($_.length / 1GB, 2)}},@{Name="LastWriteTime";Expression={$_.LastWriteTime}})
pwsh.exe -File C:\Scripts\SERVER\<launch-script1>.ps1
Part2: *Main Body *
$global:Logfile = "C:\Scripts\SERVER\MovedBackupLog\" + $(Get-Date).ToString('yyyy-MM-dd-hh-mm-ss-tt') + ".log"
$global:DestDir = "W:"
Function LogWrite
{
Param ([string]$logstring)
Add-content -path $global:Logfile -value ((Get-Date).ToString('yyyy-MM-dd-hh-mm-ss-tt') + " - " + $logstring)
}
LogWrite "================="
LogWrite "Open Share Folder Before Starting Copy Op"
LogWrite "================="
# Open Share Folder
$net = new-object -ComObject WScript.Network
$net.MapNetworkDrive($global:DestDir, "\\<hostname/ip>\db-backups", $false, "backup-user", "<password>")
sleep 1s
ls $global:DestDir
LogWrite "================="
LogWrite "================="
LogWrite "REMOVING OLDEST BACKUP JOB STARTED"
LogWrite "================="
$limit = (Get-Date).AddDays(-3)
$theOldestFiles = $(dir $global:DestDir | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $limit } | select -First 16 )
foreach ($OldestFile in $theOldestFiles) {
LogWrite "Started: $OldestFile"
Remove-Item -Path $OldestFile -Force
LogWrite "Completed: $OldestFile"
}
LogWrite "================="
LogWrite "================="
LogWrite "REMOVING OLDEST BACKUP JOB FINISHED"
LogWrite "================="
sleep 1s
LogWrite "================="
LogWrite "COPY JOB Started"
LogWrite "================="
powershell -windowstyle hidden C:\Scripts\SERVER\<scriptname>.ps1 | Add-content -path $global:Logfile
LogWrite "================="
LogWrite "================="
LogWrite "COPY JOB FINISHED"
LogWrite "================="
LogWrite "DESTINATION IS:$global:DestDir"
#$filesCount= $(Get-ChildItem -Filter "*.bak" -Path $global:DestDir | sort LastWriteTime | Measure-Object -property length -sum)
#LogWrite "Files Count: $($filesCount.Count) "
# Close Share
LogWrite "================="
LogWrite " Closing Share Connection"
$net.RemoveNetworkDrive($global:DestDir)
#Log the end
LogWrite "Script ended successfully"
Part3: The Actual Function
$global:Logfile = "C:\Scripts\SERVER\MovedBackupLog\" + $(Get-Date).ToString('yyyy-MM-dd-hh-mm-ss-tt') + ".log"
$global:SourceDir= "D:\MovedBackups\"
$global:DestDir = "W:\"
Function LogWrite
{
Param ([string]$logstring)
Add-content -path $global:Logfile -value ((Get-Date).ToString('yyyy-MM-dd-hh-mm-ss-tt') + " - " + $logstring)
}
workflow Copy-Files {
param($files)
echo "files: $files"
$SourceDir = "D:\MovedBackups\"
$DestDir = "W:\"
foreach -parallel -throttlelimit 6 ($file in $files) {
echo "Started: $SourceDir\$file"
#Copy-Item -Path "Microsoft.PowerShell.Core\FileSystem::D:\TestBackup_temp\$file" -Destination "Microsoft.PowerShell.Core\FileSystem::Z:\" -Force
robocopy $SourceDir $DestDir /ZB /B /R:3 /W:5 /V /NP /copy:DT /J /log:"C:\Scripts\SERVER\MovedBackupLog\$file.log" $file
echo "Completed: $SourceDir\$file"
}
}
#Execute Workflow
#$files=(Get-ChildItem -Filter "*.bak" -Path "D:\MovedBackups\" | sort LastWriteTime | select -last 1)
$files=(Get-ChildItem -Filter "*.bak" -Path $global:SourceDir | sort LastWriteTime | select -last 16 | select -first 16 name, @{Name="Gigabytes";Expression={[Math]::round($_.length / 1GB, 2)}},@{Name="LastWriteTime";Expression={$_.LastWriteTime}})
echo $files
Copy-Files $files.Name
Remove the Old Backups from the Cold Storage Server.
$global:Logfile = "C:\tools\tvs-scripts\PROD\SERVER-MovedBackupLog\" + $(Get-Date).ToString('yyyy-MM-dd-hh-mm-ss-tt') + "-clean.log"
$global:DestDir = "z:"
Function LogWrite
{
Param ([string]$logstring)
Add-content -path $global:Logfile -value ((Get-Date).ToString('yyyy-MM-dd-hh-mm-ss-tt') + " - " + $logstring)
}
LogWrite "================="
LogWrite "Open Share Folder Before Starting CleanUp Op"
LogWrite "================="
# Open Share Folder
$net = new-object -ComObject WScript.Network
$net.MapNetworkDrive($global:DestDir, "\\<server/ip>\db-backups", $false, "backup-user", "backup-user!")
sleep 1s
ls z:\
LogWrite "================="
LogWrite "================="
LogWrite "REMOVING OLDEST BACKUP JOB STARTED"
LogWrite "================="
$theOldestFiles = $(dir $global:DestDir | sort lastwritetime | select -First 16)
foreach ($OldestFile in $theOldestFiles) {
LogWrite "Started: $OldestFile"
Remove-Item -Path $OldestFile -Force
LogWrite "Completed: $OldestFile"
}
LogWrite "================="
LogWrite "================="
LogWrite "REMOVING OLDEST BACKUP JOB FINISHED"
LogWrite "================="
# Close Share
LogWrite "================="
LogWrite " Closing Share Connection"
$net.RemoveNetworkDrive($global:DestDir)
#Log the end
LogWrite "Script ended successfully"
I hope you like the tutorial, if you do give a thumps up! and follow me in Twitter, also you can subscribe to my Newsletter in order to avoid missing any of the upcoming tutorials.
Media Attribution
I would like to thank Clark Tibbs for designing the awesome photo I am using in my posts.
Top comments (0)