Search code examples
sql-serverwindows-10ubuntu-18.04database-restoremdf

Cannot restore mssql database because .mdf file is missing

I am trying to backup a database from SqlServer 2014 (in Windows 10 OS) and restore it to SqlServer 2017 (in Ubunutu 18.04 Digital Ocean).

I'm used the following SQL Command to Backup:

USE [master]
GO
BACKUP DATABASE [SampleDb] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Backup\SampleDb.bak' WITH CHECKSUM, COPY_ONLY, FORMAT, INIT, STATS = 10;

I'm trying to use the following SQL Command to Restore:

RESTORE DATABASE [SampleDb] FROM DISK = '/home/faizan/SampleDb.bak' WITH CHECKSUM, MOVE 'SampleDb_Data' TO '/home/faizan/SampleDb_Data.mdf', MOVE 'SampleDb_Log' TO '/home/faizan/SampleDb_log.ldf', RECOVERY, REPLACE, STATS = 10;

However, I'm getting the following error in my Ubuntu server when I try to restore the database: Error message

When I ran the following Command to check the status of the .mdf and .ldf files,

RESTORE FILELISTONLY FROM DISK = '/home/faizan/SampleDb.bak'

I get the following result: enter image description here

I am an ubuntu newbie and I don't see an mdf file here at all. And when I try to restore without the mdf file I get an error.

So how do I restore this Mssql backup in ubuntu when the .mdf file is missing?

(Extra note: The SampleDb.mdf file is present in Windows 10, in C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA, it just doesnt get transfered to Ubuntu)

Solution

  • This issue is not really Ubuntu-specific, it would appear also on Windows boxes.

    There is one small thing to fix in your RESTORE script. The logical name of MDF file is according to a screenshot is SampleDb but not a SampleDb_Data.

    Therefore adjusted command is:

    RESTORE DATABASE [SampleDb] FROM DISK = '/home/faizan/SampleDb.bak' 
WITH CHECKSUM
, MOVE 'SampleDb' TO '/var/opt/mssql/data/SampleDb_Data.mdf'
, MOVE 'SampleDb_Log' TO '/var/opt/mssql/data/SampleDb_log.ldf'
, RECOVERY, REPLACE, STATS = 10;

    Update: As Larnu remarked, another thing to consider is the location of the database files. MSSQL user should have read/write access and /var/opt/mssql/data is a default location for such files.