Copy SQL and Batch Files from Source to Destination using PowerShell
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
# 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 🙂
Related Posts
About The Author
Ajay Dwivedi
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.