Discover how to restore an SS DB on Linux from a backup on a Windows instance. Our SQL Server Support team is ready to assist.
How to Restore an SS DB on Linux from Backup on Windows Instance
When working across platforms, database professionals often face the need to restore SQL Server databases from production (on Windows) to non-production instances (on Linux).
Fortunately, SQL Server is now supported on both Windows and Linux since version 2016. This cross-platform functionality helps with performance benchmarking, development, and testing.
Today, we will walk through the steps required to restore an SQL Server database backup from a Windows Server to an SQL Server instance running on Linux.
An Overview:
Why Restore SQL Server Databases Across Platforms?
It lets us:
- Explore SQL Server on Linux.
- Benchmark stored procedures or database object performance between Windows and Linux.
- Avoid manually scripting and transferring large databases object-by-object.
Rather than using export/import methods or data scripting, restoring a full SQL Server backup is considered a better option for large databases.
For a broader understanding of backup strategies, check out this guide on types of SQL Server backups.
Prerequisites
Before we begin, we need:
- A Google Cloud project with VPC connectivity to the Active Directory domain controllers.
- A subnet ready for the SQL Server virtual machines.
- SQL Server installed on both your Windows and Linux servers.
- A tool like WinSCP or PuTTY for file transfers and SSH access.
If you’re looking for a more GUI-based alternative for backup and restore, tools like DBeaver might help. Here’s a helpful walkthrough on how to use DBeaver for SQL Server backup.
Step-by-Step: Restore SQL Server Backup from Windows to Linux
- First, we need to check our Source and Destination.
Our example setup includes:
- Source: SQL Server 2014 on Windows Server 2012 R2
- Destination: SQL Server vNext on Red Hat Linux 7.2
- Database: `Bob`
- Use this command to confirm the SQL Server version and validate the contents of the `Bob` database on the Windows machine:
SELECT @@VERSION SELECT * FROM [Bob].[dbo].[YourTable]
Copy CodeWe need to save this output for comparison after the restore.
- Next, use either SQL Server Management Studio (SSMS) or T-SQL to back up the database:
BACKUP DATABASE Bob TO DISK = 'F:\MSSQL\Backup\Bob_Full.bak'
Copy CodeBacking up databases is straightforward, but errors like SQL Server Backup Error 3041 can still occur. If you encounter this error, here’s a guide on its causes and fixes for error 3041.
- Then, get the logical and physical file names using:
RESTORE FILELISTONLY FROM DISK = 'F:\MSSQL\Backup\Bob_Full.bak'
Copy Code
We will need these names to use the `MOVE` option during restore. - Next, use WinSCP to transfer `Bob_Full.bak` from Windows to the Linux machine.
- Open WinSCP and enter our Linux server credentials.
- Drag and drop the file from the Windows (left) pane to Linux (right) pane.
- Confirm the file location using the following command:
ls -lrt /home/ourusername/
Copy Code
We can also connect using PuTTY and verify manually.
- Then, move the `.bak` file to SQL Server’s backup directory on Linux:
sudo su cd /var/opt/mssql mkdir backup mv /home/ourusername/Bob_Full.bak /var/opt/mssql/backup/
Copy CodeThis step ensures that the SQL Server has access to the backup file.
- Now, connect to SQL Server on Linux using `sqlcmd`:
sqlcmd -S 192.168.x.x -U sa -P ourpassword
Copy Code - Then, restore the database using the correct logical and physical file names:
RESTORE DATABASE Bob FROM DISK='/var/opt/mssql/backup/Bob_Full.bak' WITH MOVE 'Bob' TO '/var/opt/mssql/data/Bob.mdf', MOVE 'Bob_1' TO '/var/opt/mssql/data/Bob_1.ndf', MOVE 'Bob_2' TO '/var/opt/mssql/data/Bob_2.ndf', MOVE 'Bob_log' TO '/var/opt/mssql/data/Bob_log.ldf'
Copy Code
Wait for the restore process to complete. We will receive a confirmation when it finishes. If you encounter SQL Server Backup Error 3013 during this process, it usually indicates issues with file access or corruption. Refer to this guide for resolution steps on fixing error 3013. - Next, use the following command to ensure the database exists:
SELECT name FROM sysdatabases
Copy Code
Query the tables to compare data between the original (Windows) and restored (Linux) versions:SELECT * FROM [Bob].[dbo].[YourTable]
Copy CodeWe can also connect via SSMS to visually confirm that both databases are identical in structure and content.
Clean Up Resources
After testing, we need to clean up the resources to avoid unnecessary charges.
To delete the entire Google Cloud project:
- Go to Google Cloud Console > Manage Resources.
- Select the project used for this tutorial.
- Click Delete and confirm by typing the project ID.
Remember that deleting a project removes everything within it. If we want to keep the project but remove only specific resources, we can delete VMs or storage individually.
[Need assistance with a different issue? Our team is available 24/7.]
Conclusion
Restoring a SQL Server database backup from Windows to Linux is an easy process that saves time compared to generating scripts or performing data exports.
In short, our Support Engineers demonstrated how to restore an SS DB on Linux from a backup on a Windows instance.
0 Comments