SQL Server on Linux for the Business Intelligence Professional: Getting your Database on the VM

In this edition, we will look at getting the database up on the Virtual Machine as a first step. Then, we will restore it using SSMS. I’m expecting that you will have done the pre-requisites that I laid out yesterday. You will also need to have connected to your database on the Azure Virtual Machine.

For this purpose, we will use the WideWorldImporters sample databases provided by Microsoft.

If you are doing this activity on your own database: Make sure that the database backup type is Full. Also, make sure you are backing up to disk. which is most likely to be found in this location:

C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\

Connecting via SSH to the Azure Virtual Machine

This is when we start to use SCP (Secure File Copy) and SSH (Secure Shell) to copy our BAK file up to the Linux Virtual Machine.

We will use Git for Windows in this example. Here is how the commands look:

I’m going to deal out the commands here because it will help you.

Firstly, you need to copy the Azure connection command from an earlier step. Mine looks like this:

ssh datarelish@13.68.23.71

Type yes to continue, and hit return

Enter the password that you created when you set up the Azure Virtual Machine

Let’s execute the dir command so we can see what is up there.

From the last line, you can see that the directory contains one file: test_data.txt

Copying the file from your local Machine to the Azure Virtual Machine

I find it easier to have another Git window open here. This will point at the local machine, whereas the other will point at the Azure Linux VM.

In this new Git window, we are going to use SCP to copy the file from the local machine up to the remote Azure Virtual Machine.

Change your Directory to the location where you have stored the WideWorldImporters database. My location is D:\773WorkingDirectory

You can use commands such as dir and cd to help you to navigate.

To get to my D drive, I typed cd d:

I then used dir to read out the files and folders on the D drive

I then used cd 773* to get to the Directory I wanted, which is called 773WorkingDirectory

When I got there, I used the dir command to get to the databases I wanted.

To copy the file to the Azure Virtual Machine, I then executed the following command from the D:\773WorkingDirectory folder:

scp WideWorldImporters-DW-Full.bak datarelish@13.68.23.71:./

This landed the data to my home\datarelish directory. I could then use the cp command to copy the file so that the backup file ended up in the /var/opt/mssql/data directory.

In the next post, we will look at restoring the database onto SQL Server on Linux.