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

By:   |   Updated: 2017-03-01   |   Comments (4)   |   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.

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2017-03-01

Comments For This Article




Tuesday, September 3, 2024 - 11:56:17 PM - Adel Back To Top (92481)
Muchas gracias , me funciono

Wednesday, September 22, 2021 - 11:09:02 AM - Fernando Gabriel Back To Top (89251)
Thank you!

Tuesday, October 29, 2019 - 9:38:09 PM - Jim Zimmerman Back To Top (82927)

I have been at this all day and can not figure out what in the world is going on

I have sql 2017 enterpsie on both my local machine and the linux ubuntu 16.04 machine

I have created backups of some test databases on my local machine and then tried everything under the sun to get the database to restore.

I have even tried detatching the database from my local and moving the mdf and ldf files to the linux server and attaching them 

Every time I do I come up with the error 5 Access is denied.

Can i hire you to figure out what is going on or can you give me some clue what might be going on?

here is the code and error from ssh session

1> RESTORE DATABASE movetest FROM DISK='/var/opt/mssql/backup/movetest.bak' 2> WITH MOVE 'movetest' to '/var/opt/mssql/data/movetest.mdf',3> MOVE 'movetest_log' to '/var/opt/mssql/data/movetest_log.ldf'4> GOMsg 3201, Level 16, State 2, Server nopsql, Line 1Cannot open backup device '/var/opt/mssql/backup/movetest.bak'. Operating system error 5(Access is denied.).Msg 3013, Level 16, State 1, Server nopsql, Line 1RESTORE DATABASE is terminating abnormally.

Friday, September 20, 2019 - 6:22:01 AM - SGIA Back To Top (82514)

This was exactly what I was looking for. Thank you so much!














get free sql tips
agree to terms