I’m sure that the most “senior” readers will remember the possibilities available in old SQL Server versions to do backups using named pipes. And by older versions, I mean “really old”, since this functionality was marked as obsolete in SQL Server 7 and, although it remained in SQL 2000, it was completely removed from SQL Server 2005 and later versions.

Today, the alternative is to use the same technique used by many backup applications: the VDI interface. However, the issue is that there is no simple way to do this from SSMS in order to create these types of backup. Fortunately, the community has created and published a small VDI based tool (https://github.com/adzm/mssqlPipe) that allows us to simulate this lost functionality. Basically, this tool will enable us to use the OS stdin and stdout in order to create backups and restore them. We will start with a simple example where will use this tool to “clone” an existing database into another instance by creating and restoring a backup but without using any intermediate .bak file.

This will allow us to not only save space, but also to speed up the processes, since when creating a traditional backup, we will normally have to wait until it has been completed before launching the restore. In the command line, we would need to state that we want to do a backup operation in an instance followed by a restore where they will be linked via a pipe:

mssqlPipe sql2017 backup Backup_test | mssqlPipe sql2017 restore Backup_test2

The command output will start displaying all the operations performed while it runs:

In order to ensure that everything works as intended, we have added a monitoring step using sysinternals’ Process Monitor tool in order to check exactly what operations are being performed by SQL Server on the hard disk during the process:

We can see that the system is doing simultaneous, interleaved read and writes. As the original data file is being read, the write is also being done and no backup “content” is being created at any point. It only sits in a stream format inside the pipe during the running period. Also, if we look what is being launched against SQL Server, we will see that a pair of virtual devices are being created and they are used to perform the copy_only backup, followed by a restore with move to the default path:

If what we intend to do is a “remote” operation, where the destination server is located at a different computer, we will need to use a tool that enables us to connect two processes running in different computers via pipes. Within SysInternals’ same package, there is also a tool that allows us to run the process remotely, in addition to this functionality to redirect the standard input/ output between remote processes (PSExec). However, I did find that in some cases this tool was “corrupting” the stdout, and it also requires checking the firewalls, permissions, remote execution, etc., between the two computers. We can check to see how this tool works by launching, for example, a remote ipconfig:

psexec.exe \\DESKTOP-FLFONP9 ipconfig /all

However, as we have obtained mixed results (it works for some backups only, and not others, etc.), I have decided to use a much simpler, basic tool called Netcat. Despite its bad reputation, mainly due to its port-scanning functionality and for being used by different types of malware, it is a very useful utility to configure a simple transfer between ports. In our case, all we need is to transfer our backup’s output and to send it to a different server in order to be restored.

We will start by testing the local operation using an arbitrary listening port 12345 opened in the local loopback address 127.0.0.1. Whatever we read from this port will be sent to a restore command via the mssqlPipe:

nc.exe -l 127.0.0.1 -p 12345 | mssqlPipe.exe sql2017 restore Backup_test2

On the other hand, we will launch the backup and send it to that port using Netcat in the “send” mode:

mssqlPipe.exe sql2017 backup Backup_test  | nc.exe 127.0.0.1 12345

As expected, the results were a restore and backup via the network without creating an intermediate file:

Our next step is to repeat this process between two different computers. We shall double check that our port 12345 is open before using an external IP address (10.40.200.85) instead of the local loopback. In this case, the “listen” command would be as follows:

nc.exe -l -p 12345 | mssqlPipe.exe restore backup_test2

And the “send” command would be as follows:

mssqlPipe.exe sql2017 backup Backup_test  | nc.exe 10.40.200.85 12345

On the left, we can see the progress while the backup is being received, while on the right we can see it as it is being sent:

Unfortunately, the Netcat port does not seem to be performing very well in Windows, with a relatively low output of only 5 MB/s. So this would not be a very good option for larger transfers. Furthermore, there is an additional issue since a lot of antivirus software can cause a lot of hassle with this utility for the above-mentioned reasons. And although there are some alternatives available in PowerShell, these have not worked correctly for me. I had several issues, including not performing the streaming correctly, blocking it on the pipe level until the backup had completed (and saving it to a local temporary file before sending it) or corrupting the file ends in some cases. Summing up, there are cases when something that looks simple can get more complicated than expected…

Luckily, there are other Netcat alternatives, such as Socat, that provides a similar, albeit more powerful functionality in case we also needed to process the stream data. In our specific case, we will only require the listening function in a port and to redirect the standard input and output correctly. Therefore, we have used a Cygwin.dll supported port from Socat 1.7.3 version (https://cygwin.com/cgi-bin2/package-grep.cgi?grep=socat&arch=x86_64). In order to listen into port 12345, we would use the following command:

socat TCP-LISTEN:12345 - | mssqlPipe.exe sql2017 restore Backup_test2

And to send the backup, we would use the following command:

mssqlPipe.exe sql2017 backup Backup_test | socat - TCP:127.0.0.1:12345

We can see how in this case the performance is up to 6-7 times higher than with the Netcat port when using the loopback address. If we used an external IP and transferred it to the network, the performance would be similar, approximately 30 MB/s backup&restore speed. And the commands would be the same, only having to replace the IP address:

socat TCP-LISTEN:12345 - | mssqlPipe.exe sql2017 restore Backup_test2

And to send the backup, we would use the following:

mssqlPipe.exe sql2017 backup Backup_test | socat - TCP:10.40.200.85:12345

If we use the backup & restore functionality offered by mssqPipe, we would be slightly limited in regards to the operations that we can perform. However, it is possible to only use the functionality to create a VDI device in order to launch our backup/restore commands. For example, if we wanted to do a restore “with no recovery” at destination, we could do that by modifying the command at destination. And the process would be to firstly open the “channel + device” where the backup will be received at the destination using socat y mssqlPipe:

socat TCP-LISTEN:12346 - | mssqlPipe.exe pipe to MiPipe

Then, we would launch the backup, which would be blocked since there would not be any “listening” on the other side filling up the backup buffers. This command would not change to that from the previous example:

mssqlPipe.exe sql2017 backup Backup_test | socat - TCP:10.40.200.85:12346

In the destination, we would have to launch a restore command to read from the “MiPipe” device and undertake the corresponding restore:

When the restore is run, we will see how the backup is being transferred via the pipe and will end up restoring the database in the “no recovery” status as indicated:

We could do the same for the source and launch our customized backup commands against the device in order to, for example, back up the logs and apply them to the destination. In this case, we would create another device that will redirect our writes in the backup command and send it to “the other side” where the listening will be done in the corresponding restore command that we would launch later. Firstly, we would open the destination pipe:

socat TCP-LISTEN:12346 - | mssqlPipe.exe pipe to MiPipe

Then, we would open the source pipe:

mssqlPipe.exe sql2017 pipe from MiPipe | socat - TCP:10.40.200.85:12346

And at this point, we would launch the backup log against the pipe which, due to being so small, it will not even have to wait to be consumed at the destination, as it would fit in a single buffer:

At the destination, we would launch the corresponding restore reading from the pipe:

With the extension to Linux OS, these SQL Server operations would be even simpler to implement, since “everything” is a file in a POSIX OS. Therefore, it would be a lot easier to send the backup directly against a device that is mapped to a file that will, in turn, carry out this piping against another device at the destination, from where we would read the backup as if it was another file.

Summing up, although this backup functionality via pipes is not available by default to modern versions, it is possible to emulate it by using external tools. And although we don’t believe that these tools are ideal to be used on a regular basis as backup tools, they can certainly be useful in specific circumstances where it may not be possible to create a backup or if we intended, for performance reasons, to avoid having to wait until the backup is completed before starting the restore. It will also allow us to “emulate” the automatic seeding functionality available in the availability groups but without some of its limitations, since the backup and restore commands can be fully customized. We could use this technique to initialize log shipping, mirrors, availability groups, etc., or to carry out “on the fly” compressions/ decompressions with 7zip in order to apply some sort of customized encryption/ decryption to the backups, etc.

Rubén Garrigós

Mentor at SolidQ
I am an expert in high-availability enterprise solutions based on SQL Server design, tuning, and troubleshooting. Over the past fifteen years, I have worked with Microsoft data access technologies in leading companies around the world.

Nowadays, I am a Microsoft SQL Server and .NET applications architect with SolidQ. I am certified by Microsoft as a Solution Expert on the Microsoft Data Platform (MSCE: Data Platform) and as a Solution Expert on the Microsoft Private Cloud (MSCE: Private Cloud). As a Microsoft Certified Trainer (MCT), I have taught multiple official Microsoft courses as well as other courses specializing in SQL Server. I have also presented sessions at official events for various Microsoft technologies user groups.
Rubén Garrigós