When performing file migrations, PowerShell’s
Copy-Item is not ideal. I’ve since forgotten the original reasons I concluded this with the rest of the Internet, but this quote from reddit covers it pretty well.
The Copy-Item and Move-Item cmdlets are general purpose commands. They have the ability to handle many different types of structure.
Robocopy, on the other hand, is highly optimized for copy/move/delete on the filesystem. Only on the filesystem.
That specialization is why it is called Robust Copy.
There are a number of options available in robocopy, and for SQL Server’s purpose, you can probably get away with just
/MIR /COPYALL /DCOPY:DAT, I ended up using a few more, as suggested by serverfault:
robocopy C:\oldmount\Data C:\Mount\Data /MIR /COPYALL /DCOPY:DAT /Z /J /SL /MT:"$([int]$env:NUMBER_OF_PROCESSORS+1)" /R:1 /W:10 /LOG+:C:\temp\robocopy-log.txt /TEE /XD "Recycler" "Recycled" '$Recycle.bin' "System Volume Information" /XF "pagefile.sys" "swapfile.sys" "hiberfil.sys" robocopy C:\oldmount\Logs C:\Mount\Logs /MIR /COPYALL /DCOPY:DAT /Z /J /SL /MT:"$([int]$env:NUMBER_OF_PROCESSORS+1)" /R:1 /W:10 /LOG+:C:\temp\robocopy-log.txt /TEE /XD "Recycler" "Recycled" '$Recycle.bin' "System Volume Information" /XF "pagefile.sys" "swapfile.sys" "hiberfil.sys"
Here are the descriptions for each of the parameters, as described by the serverfault post.
- /MIR – Mirror source to destination, and delete files and directories on the destination, if they are no longer present on the source
- /COPYALL – Copy all file info: data, attributes, and timestamps, NTFS Security ACLs, Owner info, Auditing info (not all included by default)
- /DCOPY:DAT – Copy all directory info – data, attributes, timestamps (original creation timestamp is not copied by default; normally this changes to the date that it was copied by Robocopy)
- /Z – Use restartable mode
- /J – Copy using unbuffered I/O (faster copy of large multi-gig files)
- /SL – Copy symbolic links rather than the target
- /MT – Use maximum CPU threads (better use of 10 gb Eethernet and many CPU cores)
- /R:1 – If file access error, retry 1 time
- /W:10 – If file access error, wait 10 seconds before retry
- /LOG+ – Log the output to text file, append if log file already exists
- /TEE – Print results to screen and to log file
- /XD – Exclude directories, and everything within them. Names with spaces in them need be enclosed in quotes: “Recycler” “Recycled” ‘$Recycle.bin’ “System Volume Information”
- /XF – Exclude files: virtual memory and hibernation files if they happen to be present on the source: “pagefile.sys” “swapfile.sys” “hiberfil.sys”
I changed a couple things from the original post, like the value for maximum CPU threads, and the quotes around $Recycle.bin because I ran this from PowerShell and I needed the value to be literal. I also removed a couple options (like preserving audit history) that I didn’t have permissions to run.
One huge huge warning about this command: /MIR mirrors directories which can result in deleted files in the destination. In the past, I’ve accidentally destroyed data in a lab, so make super duper sure of your source and destination are right and make sure you want the directories mirrored and not just appended. I am actually unsure of which parameters would just append 🤔
Hope that helped! I’m glad to finally get around to documenting this.