How to Repair SQL Server Database on Linux Ubuntu & Fix Corrupted Data

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, multiple issues are present 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 process is the one that experts used & 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 having damaged SQL files.

Where Do Linux Machine Stores the MDF File of MS SQL on Ubuntu/CentOS?

Obviously Linux itself doesn’t store MDF files. It is the SQL Server that stores MDF on Linux. If you have Microsoft SQL Server running on a Linux machine, it stores MDF files in a specific location helping users to repair SQL Server database on Linux. By default, this location is:

/var/opt/mssql/data/

Evidently, here this directory holds the data files for all your SQL Server databases. We can find out here that every database has a corresponding MDF file (primary data) and an LDF file (transaction log) 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 Explained

Process to Repair SQL Server Database on Linux Ubuntu

Now, this process isn’t a simple one. Here, we are going to execute a process in three stages. First, we have to locate the location of the MDF file on the Linux machine & send it to a Windows machine. Secondly, we have to repair the MDF in a Windows system. Lastly, we can send the MDF file back to the Linux system.

# Stage 1 –  Locate MDF File & Transfer to Windows Machine

This stage one involves two actions. Locating the MDF files & Transferring them to the Windows system.

Locating the MDF File:

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 as “TestDatabase,” will have its MDFas “TestDatabase.mdf”.

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. 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 are involved, we are going to keep it short & simple by using advanced 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.

Step-1. Launch the Tool bHit the Open button to add files.

Step-2. Select the Quick or Advanced Scan modes for file scanning.

Step-3.  Preview Files to Set the Destination Location to Accommodate the MDF files.

Step-4. Click on the Export button to repair corrupt SQL database on Linux.

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

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

1. Transfer Method: 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.