PowerShell and BITS

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.

Don’t ask me why hes wearing horn-rims. I don’t know.

Preamble

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 – xcopy or 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).

But Why?

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.

Because we can.

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 FULLs, daily DIFFs, and obviously TLogs in some every-X-minutes fashion, you’ll usually want the latest set of FULLs and 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.

1. Get the list of files we want to copy, from “source”
2. For each file:
   1. Make sure it doesn’t exist in the “destination”
   2. If not, start a BITS transfer job (saving said job to a variable for checking/finishing later)
   3. While said BITS job is pending, print a progress message and sleep for some seconds
   4. Finish (“complete”) said job and move on to the next file
3. Conclude with a message about how much work we just did!
4. Repeat steps 1-3 for another “set of files” (list) if desired

And without further ado, the code.

	# AUTHOR: Nate Johnson, @njohnson9402/@natethedba, natethedba.wordpress.com
	# LICENSE: https://choosealicense.com/licenses/unlicense/
	# TYPE: PowerShell script
	# DESCRIPTION/USAGE: example of 2 file copying loops using BITS Transfer cmdlets, targeted at SQL backups
	# See corresponding blog post at https://natethedba.wordpress.com/powershell-and-bits/
	# ASSUMPTION: your SQL backup files are named with a date component in the form "yyyyMMdd", and use extension ".bak"
	$source = "\\BigSQL\Backup\BigSQLInstance\" #network share or local directory
	$destiny = "X:\Restore\BigSQLInstance\" #LOCAL drive destination
	$testonly = 1 #set to 0 to actually do the copying!
	$sleepSeconds = 3 #how many seconds to sleep between each progress-check during each file transfer
	# use some date-math to get last Sunday's FULL backups
	$datePattern = $(Get-Date).AddDays(-$((Get-Date).DayOfWeek.value__)).ToString("yyyyMMdd") #e.g. "20170910"
	$files = Get-ChildItem -Path $source -Recurse -Filter ("*_FULL_" + $datePattern + "*.bak")
	Write-Host ("About to copy " + $files.Count + " files to " + $destiny + " …")
	for ($i = 0; $i -lt $files.Count; $i++)
	{
	$srcfile = $files[$i].FullName
	$dstfile = $destiny + "\" + $files[$i].Name
	Write-Host (" Copying " + $srcfile)
	if (Test-Path $dstfile)
	{
	Write-Host (" File already exists: " + $dstfile)
	}
	elseif ($testonly -eq 0)
	{
	$bitsJob = Start-BitsTransfer -Source $srcFile -Destination $destiny -Asynchronous -Priority Low
	while (($bitsJob.JobState.ToString() -eq "Transferring") -or ($bitsJob.JobState.ToString() -eq "Connecting"))
	{
	#only display progress when transferring
	if ($bitsJob.JobState.ToString() -eq "Transferring")
	{
	$pctCmplt = ($bitsJob.BytesTransferred / $bitsJob.BytesTotal)
	#see https://technet.microsoft.com/en-us/library/ee692795.aspx for number formatting trick
	Write-host ($bitsJob.JobState.ToString() + " – " + $("{0:P2}" -f $pctCmplt))
	}
	Sleep $sleepSeconds
	}
	Complete-BitsTransfer -BitsJob $bitsJob
	}
	Write-Host (" Finished transferring " + $srcfile + " to " + $destiny)
	}
	# today's DIFF backups
	$datePattern = $(Get-Date).ToString("yyyyMMdd") #e.g. "20170912"
	$files = Get-ChildItem -Path $source -Recurse -Filter ("*_DIFF_" + $datePattern + "*.bak")
	Write-Host ("About to copy " + $files.Count + " files to " + $destiny + " …")
	for ($i = 0; $i -lt $files.Count; $i++)
	{
	$srcfile = $files[$i].FullName
	$dstfile = $destiny + "\" + $files[$i].Name
	Write-Host (" Copying " + $srcfile)
	if (Test-Path $dstfile)
	{
	Write-Host (" File already exists: " + $dstfile)
	}
	elseif ($testonly -eq 0)
	{
	$bitsJob = Start-BitsTransfer -Source $srcFile -Destination $destiny -Asynchronous -Priority Low
	while (($bitsJob.JobState.ToString() -eq "Transferring") -or ($bitsJob.JobState.ToString() -eq "Connecting"))
	{
	#only display progress when transferring
	if ($bitsJob.JobState.ToString() -eq "Transferring")
	{
	$pctCmplt = ($bitsJob.BytesTransferred / $bitsJob.BytesTotal)
	#see https://technet.microsoft.com/en-us/library/ee692795.aspx for number formatting trick
	Write-host ($bitsJob.JobState.ToString() + " – " + $("{0:P2}" -f $pctCmplt))
	}
	Sleep $sleepSeconds
	}
	Complete-BitsTransfer -BitsJob $bitsJob
	}
	Write-Host (" Finished transferring " + $srcfile + " to " + $destiny)
	}
	Write-Host ("All done!")

view raw

BitsTransferLoopSample.ps1

hosted with ❤ by GitHub

The Catch

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.

Obviously, serial won out in the end (specifically, his superstar protege, USB), but you gotta hand it to parallel, he had a good run.

Conclusion

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.