Database professionals get the standard requirement for SQL database restores from production to non-production instances. As you know, SQL Server runs on the Linux and Windows operating systems starting from SQL Server 2016. Suppose you are exploring the SQL Server Linux instance features and configurations.
You want to compare the performance of objects (stored procedure) for a database in the Windows environment as well as the Linux environment. You can either generate the objects scripts (with data) from the source and execute the script on the Linux database. It might not be feasible for a vast database. In that case, you want to restore the SQL database backup from the Windows SQL Server to SQL Server Linux.
For example, you get a requirement to restore the databases with the following source and destination:
- Source: SQL Server 2019 on Windows platform
- Destination: SQL Server 2019 on Ubuntu Linux
Can we restore the databases on cross-platform SQL Server instances? It is interesting to see if we can see do it and if yes, how?
I have the following SQL Server instances in my lab environment, as shown below.
Version: 15.0.2000.5 (SQL 2019 RTM)
Version: 15.0.4053.23 (SQL 2019 CU6)
OS: Windows Server 2016
OS: Ubuntu Linux 18.04
In this lab, my source and destination SQL versions are different. We cannot restore a database into the lower version. It is okay because the destination Linux SQL instance is on a higher version than the source version.
Right-click on SQL instance and view the SQL Server properties to verify the operating system and platform.
If you do not know the process to install SQL Server on Linux, you can follow existing articles on SQLShack.
Steps to restore a database from Windows SQL Server to SQL Server Linux
For this article, let’s create a new database [DemoForLinuxRestore] on the Windows SQL Server. You can right-click on the databases tab and create a new database with default properties, as shown below.
Insert some sample records into the new database. For this purpose, I use the SELECT INTO statement that creates a new table automatically from the source table data.
Take a full backup of the [DemoForLinuxRestore] database
You can take a full database backup either using SSMS GUI wizard or with the BACKUP DATABASE script.
Backup Database DemoForLinuxRestore to disk=’C:\SQL\DemoForLinuxRestore.bak’
Copy the database backup from Windows server to Linux server
Usually, when you need to restore the database between two SQL instances on the Windows servers, you create a shared folder and access the backup file from the destination instance.
In this article, we need to copy the backup file from a Windows to Linux operating system. For this purpose, we use WinSCP.
Download the WinSCP and install it with the default ( Commander) interface style. In this mode, we use two separate consoles for the source and destination server. You can drag the files from source to destination in this Commander mode.
Launch the WinSCP. It automatically connects to the Windows server while it asks you for connection information on another server.
We can connect the Linux server using the SFTP protocol on Port 22. Specify the hostname (IP address), user name and the password to connect the Linux server.
On the left side, we have the Windows server directory while on the right, it has the Linux server.
Change the directories in the WinSCP, as shown below.
- Source: C:\SQL ( the backup directory)
- Destination: /tmp/ ( Temporary directory to copy the files in Linux)
Drag the SQL database backup from Windows to Linux console as shown below.
Now, connect to the Linux server using the terminal and view the backup file exists.
$ cd /tmp
$ ls -lrt Demo*.bak
Use Sudo access to browse to the SQL Server Linux directory /var/opt/mssql.
$ sudo su
$ cd /var/opt/mssql
The mssql directory has the data, log folders that contains SQL database primary file and log file.
/var/opt/mssql# cd data
Let’s create a new directory in the /var/opt/mssql and copy the backup file from the /tmp to the new directory.
/var/opt/mssql# mkdir backup
/var/opt/mssql# scp /tmp/Demo*.bak /var/opt/mssql/backup
View the backup file in the backup directory, as shown below.
Restore the backup file in SQL Server Linux using the Azure Data Studio
We can use command-line tools such as SQLCMD in SQL Server Linux to perform database activities. Azure Data Studio is also useful on the Linux server to perform certain database activities. If you want to use the SQL Server Management Studio, you need to connect the Linux instance in SSMS installed on a Windows server.
You can download the Azure Data Studio latest version and connect the Linux instance.
Currently, we have one user database [LinuxAGSQL] for linuxnode2 instance.
Right-click on the instance -> Manage to launch the server dashboard. In this dashboard, click on Restore.
In the restore database wizard, browse to the Linux directory contains the backup file. You can browse similar to a Windows directory in Azure Data Studio.
We get an error – SQL Server is unable to open the backup file in the /var/opt/mssql/data. Due to the permission issue, the RESTORE HEADERONLY command terminated.
To investigate the issue, check the backup file owner with the ls-lrt command. Currently, the root user has ownership of the SQL database backup file.
Modify the permissions to the mssql user using the chown command as shown below.
# /var/opt/mssql/backup# ls -lrt
# /var/opt/mssql/backup# chown mssql Demo*.bak
Now, relaunch the restore database wizard, and it can access the backup file. You get the backup file details in the restoration plan.
You can directly execute the backup ( click on Restore) or generate a RESTORE DATABASE script. In this script, verify the following:
- Data and Log file location is /var/opt/mssql/data
- backup file location is /var/opt/mssql/backup
Click on Run to execute the restore command. It shows you the progress of the database restores in the message tab.
Refresh the databases in Azure Data Studio, and you see the restored [DemoForLinuxRestore] database in the Linux.
Database consistency check
As we restore a Windows-based SQL database to the Linux instance, it is good to perform a database consistency check using DBCC CHECKDB. As shown below, CHECKDB did not raise any consistency issues.
Verify table record counts
In the last step, verify the table record counts. Previously, we inserted the 290 rows in Windows SQL before taking database backup. As shown below, it has the same record count after database restore in SQL Server Linux.
In this article, we restored a SQL database from the Windows SQL Server to SQL Server Linux instance. You can restore the database similar to restore between two different Windows SQL instances.
- Apply SQL Server patches or cumulative updates in SQL Server Always On Availability Groups - October 14, 2020
- Make the most of secondary replicas in SQL Server Always On Availability Groups - October 13, 2020
- Column-level SQL Server encryption with SQL Server Always On Availability Groups - October 12, 2020