SQL Server Restore Error: Directory lookup for the file "db.mdf" failed

Question

When trying to restore a dbname.bak file (from a windows machine) for SQL Server on a linux machine using:

RESTORE DATABASE dbname
FROM DISK = '/path/to/dbname.bak'

I got the following error:

Error: Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL\DATA\dbname.mdf" failed with the operating system error 2(The system cannot find the file specified.).
SQLState: S0001
ErrorCode: 5133

and also another error for the dbname_log.ldf file.

Why is SQL Server trying to reference windows files on a linux machine, I hear you ask?

Answer 1

Explanation

MS SQL Server assumes by default that the file path(s) saved inside the dbname.bak is where the database should be restored to. If the file path(s) don't exist, you'll get an error like that.

Solution

Explicitly tell the DB to use a different file(s). But first you need to know how the file(s) is (are) referred to by executing the following T-SQL:

RESTORE FILELISTONLY FROM DISK = '/path/to/dbname.bak'

which might give you something like this:

Dbname_Empty
Dbname_Empty_log

which you can then use to execute the following T-SQL:

RESTORE DATABASE dbname
FROM DISK = '/path/to/dbname.bak'
WITH MOVE 'Dbname_Empty' TO '/var/opt/mssql/data/dbname.mdf',
MOVE 'Dbname_Empty_log' TO '/var/opt/mssql/data/dbname.ldf'

hopefully without getting any errors.