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's 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.