Copy SQL and Batch Files from Source to Destination using PowerShell

Spread the love

Recently as part of Server Migration, I had to move nested folders and files from Source server to Destination. This task becomes hard when directory has child folders and files with no restriction on nesting level. And the subfolders may contain variety of files like full backup, diff backups, tlogs, sql files, batch files, log files, etc.

In this situation, PowerShell comes to rescue. Below is the script that I created for myself to move all folders with *.sql and *.bat files from Source Server to Destination Server.

# Set Source and Destination Servers
$sServer = 'ServerSource';
$tServer = 'ServerDestination';

$basePath = 'f$\mssqldata'

# Find source folders
$folders = Get-ChildItem "\\$sServer\$basePath" -Recurse | Where-Object {$_.PsIsContainer};

# Create same folders on destination
foreach($fldr in $folders)
{
    $newPath = $fldr.FullName -replace "\\\\$sServer\\", "\\\\$tServer\\";
    $exists = ([System.IO.Directory]::Exists($newPath));

    if($exists) {
        Write-Host "Exists=> $newPath" -ForegroundColor Green;
    } else {
        Write-Host "NotExists=> $newPath" -ForegroundColor Yellow;
        #[System.IO.Directory]::CreateDirectory($newPath);
    }
}

# Find source files, and filters for specific extension
$fileExtensions = @('.sql','.bat');
$sqlfiles = Get-ChildItem "\\$sServer\$basePath" -Recurse | 
                Where-Object {$_.PsIsContainer -eq $false -and $fileExtensions -contains $_.Extension};                

# Create same folders on destination
foreach($file in $sqlfiles)
{
    $newPath = $file.FullName -replace "\\\\$sServer\\", "\\\\$tServer\\";
    $exists = ([System.IO.File]::Exists($newPath));

    if($exists) {
        Write-Host "Exists=> $newPath" -ForegroundColor Green;
    } else {
        Write-Host "NotExists=> $newPath" -ForegroundColor Yellow;
        #Copy-Item "$($file.FullName)" -Destination "$newPath"
    }
}

Friends! I hope this will be helpful to you as well. Feel free to connect with me for any query. If you find the post useful, please do like and share. Happy Coding 🙂

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

Leave a Reply

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