Last Wednesday I got very excited by Microsoft’s announcements about the addition of features to standard edition and the public preview of SQL Server on Linux. I bored everyone in the office with the features we could now use in standard edition and the possibilities that running SQL Server on Linux would open to us. After a while a colleague asked me if it was possible to restore a backup from a SQL Server instance on Windows to a Linux instance. I said it must be, but I wasn’t 100% sure. Let’s give it a test.

I used a Hyper-V Ubuntu 16 VM. First I had to connect to my VM and transfer the backup file. I used the SSH client Bitvise as it makes transferring files between Windows and Linux a breeze. First I installed openssh-server on my Ubuntu VM and allowed SSH through the firewall. I then connected with Bitvise, accepted the new key and was then presented with a terminal and file transfer window.

Once connected, I navigated to the backup file on my Windows machine with the left side of the file transfer window. I then dragged it to the right side which transferred the file to my home directory in Ubuntu.

The Restore

I can now treat my instance on Ubuntu like any other by connecting with SSMS. The restore wizard is able to navigate the directories on Linux the same as it can on Windows, but I realised I wouldn’t be able to access the backup in my home directory. This is because mssql is running as a different user. I jumped onto my terminal and moved my backup to a location that mssql would be able to access with the copy command.

sudo cp Test_JAnderson.bak /var/opt/mssql/data/

I was then able to see my backup in the wizard and attempt a restore.

The first problem I hit was that SSMS didn’t correct the file paths like it normally does. I expect this will be fixed in the coming weeks or months.

With that correction the restore succeed!

I said that I thought it would succeed earlier and that’s because I knew a little more than I was letting on. The SQL Server instance running on Linux is the same as it’s Windows counterpart. This is possible due to a very low-level layer of emulation named Drawbridge. This piece of software brings critical Windows files to Linux allowing it to run Windows programs. It turns out this has been about for a while, read more here.

SQL New Blogger

This post came about from a discussion with a colleague. I wanted to test the theory out of genuine interest. If this happens to you, why not make screen shots as you go and write up a post at a later date?  I was able to test the backup, take the screen shots and type up the blog post in about an hour.

The post Restoring a SQL Server backup on Linux appeared first on The Database Avenger.