Welcome back! This month’s topic is PowerShell — thanks to one of our prominently bearded community members. PowerShell is a fantastic tool in the IT professional’s toolbelt. I was first introduced to it somewhere in 2014 or 2015 by a colleague, and started making much heavier use of it when my career took me to a new & bigger environment.
Actually, funny side-story. I remember seeing one of the very early incarnations of PowerShell, or what would eventually evolve into it, in college. A graphics programming course, of all things, had a MS partner come in to show us this “cool new” Windows command-shell thing (different and separate from the DOS-style CMD, obviously), where he demonstrated fetching some data from the filesystem, feeding it into a CSV, and doing some kind of super-basic analysis on it to show in a “report” (which was really just another text file). This was 2005-2006, so I couldn’t say what it was specifically, though I seem to remember something about the word “Longhorn”. Although, reading up on some of the Wiki-history, it seems more likely that it was a Monad beta.
Anyway, back on topic. Today’s post is pretty simplistic in comparison to what most people may be writing about. But I’ve already blogged about doing hands-off SQL installation with PowerShell & CLI, and this was another thing kicking-around the back of my mind. So this is about using PowerShell and the BITS framework (*-BitsTransfer cmdlets) to copy very large files across servers. Specifically, database backups. Because let’s face it, they can be really large. And if you’re faced with fetching them off a PROD box, you want to minimize the impact on that box’s resources.
Now sure, there are other ways –
robocopy with the
/J flag (un-buffered IO), or fancy GUI tools. And in an ideal world your backups would be written to a network share that’s not a local drive on the PROD SQL server, right? Right…
Oh, and one more thing. You need to enable BITS via the Windows Features console — search “features” in your Start menu and it should come up as
Turn Windows features on or off (Control Panel) . On a server, it’s under the Server Role “Web Server (IIS)”, feature “Background Intelligent Transfer Service (BITS)”. Underneath there are 2 sub-feature choices, “IIS Server Extension” and “Compact Server”. Honestly I don’t know which is preferable, but I left it with the default selection, the first (former). It should go without saying, but don’t do this in production (unless you have the blessing of your SysAdmins).
Why BITS? Well, as per the Docs, it has the following 3 key features (emphasis mine):
- Asynchronously transfer files in the foreground or background.
- Preserve the responsiveness of other network applications.
- Automatically resume file transfers after network disconnects and computer restarts.
Wow, nifty! So it doesn’t hog the network, and it’s resumable (resume-able?) in case of connectivity hiccups. Pretty sweet, no? Also, it can run asynchronously in the background, which means it won’t hog your storage bandwidth or compute resources.
Let’s See an Example
Most of the guts and inspiration for this came from this article over on “Windows OS Hub” (woshub, a somewhat unfortunate sounding acronym, but certainly not as bad as some!). The
datePattern nonsense is just to make it “dynamic” in the sense that, if you have a backup scheme like me, with Sunday
DIFFs, and obviously
TLogs in some every-X-minutes fashion, you’ll usually want the latest set of
DIFFs. But you could easily tweak this, make it more point-in-time aware or whatever, as needed.
So, here’s a bit of a talk-thru outline, and then I’ll just link the Gist.
- Get the list of files we want to copy, from “source”
- For each file:
- Make sure it doesn’t exist in the “destination”
- If not, start a BITS transfer job (saving said job to a variable for checking/finishing later)
- While said BITS job is pending, print a progress message and sleep for some seconds
- Finish (“complete”) said job and move on to the next file
- Conclude with a message about how much work we just did!
- Repeat steps 1-3 for another “set of files” (list) if desired
And without further ado, the code.
There are some downsides here. First, you cannot use BITS in a non-interactive mode, i.e. inside a
Scheduled Task as a User that’s not logged-in. This is because it’s a “desktop”-oriented feature, not a “server” one. Second, I’ve never been able to get multiple transfers going at once — or at least, multiple PoSh scripts which use BITS transfers. This could very well be my fault, but it does seem like the BITS jobs are “serial” in nature, i.e. one must finish before the next one can start. Again, not the expert, just observing what I found during my experiments.
BITS transfer is an interesting method for copying extra-large files around your environment with low overhead. PowerShell makes it easily accessible and lets you wrap it up in loops and checks so you can effectively build a progress-indicative, predictable and reproducible method for copying a whole SQL server’s set of backups from one place to another.
What cool little things have you discovered using PowerShell? Let me know! Thanks for reading.