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:
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 firstname.lastname@example.org:./
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.
One thought on “SQL Server on Linux for the Business Intelligence Professional: Getting your Database on the VM”