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:

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.

Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools and holds a master's degree in Systems Engineering. Chrissy is also certified in SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in PowerShell, SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *

*