SQL Server Migration – Moving a SQL Server DB from Windows to Linux Fails

linuxsql serverssmswindows

We are trying to move a SQL server DB from Windows to Linux. But we are facing few problems while migration.

  1. We took a Backup of the DB server, from SSMS in Windows
  2. We restored it in the linux running the following commands:
RESTORE FILELISTONLY FROM  DISK =N'/var/opt/mssql/data/backup_file_name.bak' 
RESTORE DATABASE DB_NAME 
    FROM  DISK = N'/var/opt/mssql/backup_file_name.bak' WITH  FILE = 1,   
    MOVE N'DB_NAME' TO N'/var/opt/mssql/data/DB_NAME.mdf',   
    MOVE N'DB_NAME_Log' TO N'/var/opt/mssql/data/DB_NAME.ldf', 
    NOUNLOAD,  REPLACE,  STATS = 1
GO

Running this I get an error more or less lie this:

file XX_FlatFline cannot be restored to PATH_IN_WINDOWS {…..mdf}.
Use WITH MOVE to identify a valid location for the file.

I get error for both MDF and LDF files. The windows path shown above in the error is the location of the mdf and ldf files in the Windows machine.

Can someone help me understand what's going wrong here? Why does the Windows path still matter when I am restoring the dump into a new DB?

Warning: I am a novice in SQL Server.

Best Answer

In the first release of SQL Server on Linux, I saw that SQL Server translates paths to related terms in windows, for example, root '/' will be C:. so if this logic exists, you may try to make it something like:

FROM DISK = N'C:\var\opt\mssql\backup_file_name.bak' WITH FILE = 1

but I think it's different now as mentioned here maybe you should first check the version of your SQL Server on Linux and then check the steps on that article.

Related Question