This article explains how to repair SQL Server Database on Linux (Ubuntu & CentOS). While repairing MDF files is straightforward on Windows, it’s not the case on Linux. Here, several issues can arise that users need to avoid. We’ll guide you through exporting the MDF file to Windows for repair, and then importing the fixed version back to Linux. So far, this is the process that experts have used and got the desired results. We’ll also show you where Linux stores MDF data and provide a helpful tip at the end.

For both the Ubuntu & CentOS distributions of Linux, the SQL database file default location is the same. Therefore, the procedure & steps in this article can be helpful for users in both Ubuntu & CentOS versions with damaged SQL files.

Table of Content

Where does Linux Machine Store the MDF File of MS SQL Server?

To understand how to repair SQL Server database on Linux, it is important that we know about how SQL Server operates on Linux. Linux itself doesn’t store MDF files. Even on Linux, the MDF files are stored in SQL Server. In case users have Microsoft SQL Server running on a Linux machine, the MDF files are stored in a specific location. Below is the default location where the MDF files are stored in the database. 

/var/opt/mssql/data/

Evidently, this directory holds the data files for all SQL Server databases. We can see here that every database has a corresponding MDF file (primary database file) and an LDF file (transaction log file) within this directory.

This is the default location and there is no surety nowhere users change the default location. It differs based on the SQL Server setup of all the DBAs. You can check the server configuration or documentation for the exact path on your system.

Also Read: DBCC CHECKDB Repair Rebuild Example & Process

Also Read: Repair Corrupted System Tables in SQL Server With Best Methods

Steps to Repair SQL Server Database on Linux Ubuntu

Now, this process might be complicated for users who aren’t well aware of the technicalities and environment. Here, we are going to carry out the process in 3 stages:

  • Firstly, we will locate the MDF files on the Linux machine & move them to a Windows machine.
  • Secondly, we will repair the MDF file on a Windows system.
  • Lastly, we will save the MDF files back to the Linux system.

Let’s now move to understanding the stages

Stage 1 –  Locate MDF File & Transfer to Windows Machine

This stage involves two actions to repair SQL Server database on Linux: Locating the MDF files & transferring them to the Windows system.

Locate the MDF Files:

  1. Default Location:  The default location for SQL Server data files on Linux is:
    /var/opt/mssql/data/
    This directory stores data files for all your SQL Server databases.
  2. Verify Location:  It’s important to confirm the exact location in your specific case. This might differ due to a custom SQL Server installation. Here’s how to verify:
    1. Open a Terminal Window on your Linux machine.
    2. Run the following Command to check the configuration for the data directory:

      Bash
      sqlservr -b

  3. Identifying the MDF File:  Inside the data directory itself, every database holds a corresponding MDF file named after the database itself. For example, a database named “TestDatabase” will have its MDF as “TestDatabase.mdf”. Let’s now move to the transferring methods to repair SQL Server database on Linux.

Transferring the MDF File to Windows:

  1. Method Selection: While the MDF file contains the database data, it’s not directly usable for import into Windows SQL Server. You’ll need additional steps later (covered in Stage 2). However, you can still transfer the MDF file for further processing.
  2. Transfer Methods: Use any file transfer method that works for your setup. Here are some options:
    • Shared Storage: If your Linux and Windows machines share a storage system (like a network-attached storage), you can directly copy the file.
    • Secure Copy (SCP): Use the scp command on Linux to securely transfer the file over the network and repair SQL Server database on Linux. This requires knowing the IP address and login credentials for the Windows machine.
    • USB Drive: Copy the MDF file to a USB drive and transfer it physically to your Windows machine.

Note: Transferring the MDF file alone doesn’t create a functional database on Windows. Therefore, Stage 2 will guide you through the complete database transfer process along with recovery & repair tasks.

Stage 2 – Repair SQL Server Database on Linux Through Windows

Now, as there are already a lot of technicalities involved, we are going to keep it short & simple by using an advanced solution like SysTools SQL Database Recovery Tool for the operation. Download the software on your Windows system & follow the 4 simple steps. This advanced solution can repair MDF file in SQL without any errors or technical commands in both Windows & Linux Ubuntu/CentOS versions.

Download Tool Purchase Now

Step 1. Install and run the advanced solution to repair SQL Server database on Linux. Click on Open to browse MDF file.

launch software

Step 2. Choose one of the Quick or Advanced Scan modes to scan the database for corruption.

choose scan mode

Step 3.  Preview data in the software panel and click on Export Button to save the recovered files.

Preview Files

Step 4. Choose CSV file format or SQL Scripts format to save the database files easily to Linux.

Click on Export/Save Button

Stage 3 – Transfer MDF File Back to Linux System

BACPAC files are a great way to transfer a complete SQL Server database, including schema and data, between Windows and Linux machines. Therefore, here’s how to use the BACPAC export and import method to repair SQL Server database on Linux:

Step 1. Export BACPAC from Windows (SQL Server Management Studio – SSMS)

  1. Launch SSMS on Windows: Open SQL Server Management Studio on your Windows machine.
  2. Connect to Windows SQL Server: Connect to the SQL Server instance that hosts the database you want to transfer.
  3. Right-Click Database: In the Object Explorer window, navigate to the database you want to export and right-click on it.
  4. Select Tasks -> Export Data-Tier Application…
  5. Choose Destination & Settings: In the wizard window, specify a location on your Windows machine to save the BACPAC file. You can also configure additional settings like compression.
  6. Start the Export: Click “Next” to proceed through the wizard and initiate the export process. This might take some time depending on the database size.

Step 2. Transfer BACPAC File to Linux

Use any file transfer method that works for your setup to repair SQL Server database on Linux. Common options include shared storage, SCP (secure copy), or a USB drive.

Step 3. Import BACPAC on Linux (SSMS on Linux)

  1. Launch SSMS on Linux: Open SSMS on Linux.
  2. Connect to Linux SQL Server: Connect to the target SQL Server instance on your Linux system where you want to import the database.
  3. Right-Click Databases: In the Object Explorer window, right-click on the “Databases” folder.
  4. Select Tasks -> Import Data-Tier Application…
  5. Choose BACPAC File: In the wizard window, browse and select the BACPAC file you transferred from Windows.
  6. Specify Settings (Optional): Review and adjust any import settings if needed.
  7. Start the Import: Click “Next” to proceed through the wizard and initiate the import process. This might take some time depending on the database size.

Also Read: Restore Database from Emergency Mode in SQL Server Easily

Conclusion:

In the end, we can say that this process is quite different from the usual operations, but this is the most efficient way for users to get the desired results. Also, the advanced solution is the best way for users, as it eliminates all the risks present in the manual ways to repair SQL Server database on Linux Ubuntu & CentOS machines.