By: Manvendra Singh | Comments (3) | Related: > SQL Server on Linux
Problem
I explained how to install SQL Server vNext on Linux in my last couple of tips. Now let's dig into administrative tasks. In this tip, I will show you how to restore a database on a Linux based SQL Server instance from a backup taken on Windows based SQL Server.
Solution
Restoring databases are basic admin tasks which DBAs do day to day. Recently Microsoft launched SQL Server for Linux based servers, so I will take a backup of a Windows based SQL Server database and will restore it on a Linux based SQL Server instance. The method to do this is quite similar to what we do in a Windows environment except for few steps which we will cover in this tip.
Restore database on a Linux based SQL Server
Step 1: The below picture shows the details of the source and destination servers. I have a SQL Server 2014 instance hosted on Windows Server 2012 R2 and a SQL Server vNext instance hosted on Red Hat Linux 7.2. I have a database named "Manvendra" on the Windows based SQL Server and I will take a backup of this database and restore it on the Linux based SQL Server.
Let's verify the version of SQL Server on the source server along with the details of table in the Manvendra database. We will compare this output to the one we captured on the Linux server post restore.
Step 2: Create a backup of the database "Manvendra" on the Windows server either by executing the below command or using SQL Server Managemen Studio (SSMS).
--Run Full Backup of Manvendra BACKUP DATABASE Manvendra TO DISK = 'F:\MSSQL\Backup\Manvendra_Full.bak'
We can see the full backup of database Manvendra has completed successfully in the above picture. Before going ahead, we need to check the data and log file names of this database because we need to use the MOVE option along with the RESTORE command as there is a different directory structure in Windows vs. Linux. Run the below command to get the logical and physical names of each database file.
--Run FILELISTONLY to get database file names. RESTORE FILELISTONLY FROM DISK = 'F:\MSSQL\Backup\Manvendra_Full.bak'
I executed this command on the source server only, but you can run this command on your destination server as well after moving the backup file. If you are running the above command on the Linux server make sure to change to the location of the backup file.
Step 3: The next step is to copy this full backup file from the Windows server to the Linux server. There are many ways to transfer files between these servers, but I used WinSCP a free application to copy files to a Linux server. You can install WinSCP on your Windows machine. Once you open the application, it will ask you to enter the destination server details to establish a connection. Once the connection is established you will get a similar window like the below picture. The left side shows your local Windows machine and the right side shows the remote Linux machine.
To copy a file from the source to the destination server just drag and drop the file from the left side to the right side. You can see the full backup file Manvendra_Full.bak is now on both sides. The location of the file is highlighted on both sides. I have copied it to /home/Manvendra/. We can verify the file exists by running ls -lrt command on the Linux server. Next, connect to a Linux server using PuTTY as I did in the below screenshot then run the below command to display the files in location /home/manvendra.
Step 4: We can see Manvendra_Full.bak file has been copied to the target Linux server where we want to restore it on SQL Server. I will create a directory named backup under /var/opt/mssql and copy the backup file to folder /var/opt/mssql/backup as shown below.
#Run SUDO sudo su #go to location /var/opt/mssql cd /var/opt/mssql #Make directory backup mkdir backup #move backup file to directory backup mv /home/manvendra/Manvendra_Full.bak /var/opt/mssql/backup
We can see our backup file has been copied to /var/opt/mssql/backup.
Step 5: The next step is to connect to the SQL Server instance and then restore this backup file. We will use sqlcmd to make a database connection on the Linux server. Run the below command to make a connection.
#Make a db connection to your server. Enter the sa password once you get the prompt. sqlcmd -S192.168.209.128 -Usa
Once you get connected to SQL Server, run the below RESTORE command to restore this database on the Linux server. As both servers have different directory structures we need to use the MOVE option with the RESTORE command. I am restoring all database files to location /var/opt/mssql/data. The name of the logical files and the physical files can be identified from the screenshot in Step 2 where we ran a RESTORE FILELISTONLY command.
#Restore database on Linux. RESTORE DATABASE Manvendra FROM DISK='/var/opt/mssql/backup/Manvendra_Full.bak' WITH MOVE 'Manvendra' to '/var/opt/mssql/data/manvendra.mdf', MOVE 'Manvendra_1' to '/var/opt/mssql/data/manvendra_1.ndf', MOVE 'Manvendra_2' to '/var/opt/mssql/data/manvendra_2.ndf', MOVE 'Manvendra_log' to '/var/opt/mssql/data/manvendra_log.ldf'
Once you execute this command, it will restore the database, run a series of updates and let you know when the restore has completed as shown below.
Step 6: Now we will validate that the database restore was successful. We can check the sysdatabases system catalog view to get the database details. Run the below command to check for an entry for this database in sysdatabases.
SELECT name from sysdatabases
We can see database "Manvendra" is a now a database on this server.
We will validate the rows of the table as we captured in Step 1. We can see the data does exist for this table.
We can also validate using SSMS on the Windows server. I have connected to both the source and destination servers and compared the objects and databases. We can see that both servers have the same database name and table name.
Next Steps
- Take the time to learn more about SQL Server vNext.
- Stay tuned for new tips based on SQL Server on Linux.
- Explore more knowledge on SQL Server Database Administration Tips.
- Read these other SQL Server on Linux Tips.
About the author
Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
