Powershell Script to Copy Sql Backup files from one server to another server/ remote server

PowerShell Script for Copying the Latest Backup File:

This script copies local sql backup files to remote folder and also it deletes old backup files (.bak files). We will provide the source folder , destination folder and then it test the destination folder, if folder has all bak files copied to destination folder and then it will delete old backup files..

An overview of Script process looks like below:

  • It will search the backup path for files which are matching a pattern.
  • It Finds the most recent one from the backup path, based on date.
  • It will Copy that most recent file to target location.

Below is the script to copy Latest .bak file from source to destination:

$backuppath = "\\Node1.yourdomainname.com\source" # source path
$destpath = "E:\dest" # Destination path

Get-ChildItem $destpath -recurse -include *.bak -force | remove-item # Remove existing .bak files in destination path

Get-ChildItem -path $backuppath -Filter "*.bak" |
where-object { -not $_.PSIsContainer } |
sort-object -Property $_.CreationTime |
select-object -last 1 | # last 1 means latest backup file
copy-item -Destination (join-path $destpath $_.name)

If you want to copy 10 or 20 latest or recent .bak files from source folder to destination folder then use below script :

$backuppath = "\\Node1.yourdomainname.com\source" # source path
$destpath = "E:\dest" # Destination path
Get-ChildItem $destpath -recurse -include *.bak -force | remove-item # Remove existing .bak files in destination path
Get-ChildItem -path $backuppath -Filter "*.bak" |
where-object { -not $_.PSIsContainer } |
sort-object -Property $_.CreationTime |
select-object -last 20 | # last 1 means latest backup file
copy-item -Destination (join-path $destpath $_.name)

Hope this script helps you to copy the backup files .

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.