Using robocopy to move SQL Server files
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:
1robocopy 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"
2
3robocopy 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.
1- /MIR - Mirror source to destination, and delete files and directories on the destination, if they are no longer present on the source
2- /COPYALL - Copy all file info: data, attributes, and timestamps, NTFS Security ACLs, Owner info, Auditing info (not all included by default)
3- /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)
4- /Z - Use restartable mode
5- /J - Copy using unbuffered I/O (faster copy of large multi-gig files)
6- /SL - Copy symbolic links rather than the target
7- /MT - Use maximum CPU threads (better use of 10 gb Eethernet and many CPU cores)
8- /R:1 - If file access error, retry 1 time
9- /W:10 - If file access error, wait 10 seconds before retry
10- /LOG+ - Log the output to text file, append if log file already exists
11- /TEE - Print results to screen and to log file
12- /XD - Exclude directories, and everything within them. Names with spaces in them need be enclosed in quotes: "Recycler" "Recycled" '$Recycle.bin' "System Volume Information"
13- /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.