Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Restore a SQL Server database on a Linux based instance from a backup on a Windows instance


By:   |   Read Comments   |   Related Tips: More > SQL Server on Linux

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


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.

Source and Destination Instances in SQL Server Management Studio

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.

SQL Server Source details version

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'

Run Full SQL Server Backup

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'

Restore filelist only in SQL Server

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.

WinSCP of the SQL Server Backup Files

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.

ls -lrt to review the backup files

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.

copy the SQL Server backup file to the backup folder

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'

RESTORE db Manvendra in SQL Server

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.

RESTORE db Manvendra was successful in SQL Server

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.

Query sysdatabases in SQL Server to validate the database exists

We will validate the rows of the table as we captured in Step 1. We can see the data does exist for this table.

SELECT * FROM EMPLOYEES to validate the row count

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.

Compare the databases in SQL Server Management Studio
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools