Bobcares

How to Restore an SS DB on Linux from Backup on Windows Instance

PDF Header PDF Footer

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

How to Restore an SS DB on Linux from Backup on Windows InstanceWhen 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.

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

  1. 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`
  2. 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 Code

    We need to save this output for comparison after the restore.

  3. 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 Code

    Backing 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.

  4. 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.

  5. 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.

  6. 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 Code

    This step ensures that the SQL Server has access to the backup file.

  7. Now, connect to SQL Server on Linux using `sqlcmd`:
    sqlcmd -S 192.168.x.x -U sa -P ourpasswordCopy Code
  8. 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.

  9. Next, use the following command to ensure the database exists:
    SELECT name FROM sysdatabasesCopy Code

    Query the tables to compare data between the original (Windows) and restored (Linux) versions:

    SELECT * FROM [Bob].[dbo].[YourTable]Copy Code

    We 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:

  1. Go to Google Cloud Console > Manage Resources.
  2. Select the project used for this tutorial.
  3. 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

Submit a Comment

Your email address will not be published. Required fields are marked *

Get featured on the Bobcares blog and share your expertise with a global tech audience.

WRITE FOR US
server management

Spend time on your business, not on your servers.

TALK TO US

Or click here to learn more.

Speed issues driving customers away?
We’ve got your back!