MsSQL Permission Error – Fixing Permission Error When Restoring Database on Linux

dockerlinuxsql server

I'm trying to restore a .bak file created on Windows SQL Server to the Linux version of MSSQL, but I get an error like the following:

Msg 7610, Level 16, State 1, Line 112
Access is denied to "/var/opt/mssql/data/sysft_Catalog.ndf", or the path is invalid. 
    
    Msg 3156, Level 16, State 50, Line 112
File 'sysft' cannot be restored to '/var/opt/mssql/data/sysft.ndf'. Use WITH MOVE to identify a valid location for the file. 
    
    Msg 3119, Level 16, State 1, Line 112
Problems were identified while planning for the RESTORE statement. Previous messages provide details. 
    
    Msg 3013, Level 16, State 1, Line 112
RESTORE DATABASE is terminating abnormally.

The command I'm running is the following (or one of many variants I've tried):

RESTORE DATABASE Imported_Database FROM
DISK = '/var/opt/mssql/backup/database_to_import.bak'
WITH
  MOVE 'Dat' TO '/var/opt/mssql/data/Dat.mdf',
  MOVE 'ftrow' TO '/var/opt/mssql/data/Catalog.ndf',
  MOVE 'Log' TO '/var/opt/mssql/data/Log.ldf',
  MOVE 'sysft_Catalog' TO '/var/opt/mssql/data/sysft_Catalog.ndf',
  STATS = 5 -- REPLACE, PARTIAL, etc.
GO

The database is hosted on Ubuntu 20.04.1 LTS in a container created by docker-compose with:

version: "3.2"
services:
  sql-server-db:
    container_name: mssql
    image: mcr.microsoft.com/mssql/server:2019-latest
    ports:
      - "1433:1433"
    environment:
      SA_PASSWORD: "{{ PASSWORD }}"
      ACCEPT_EULA: "Y"
    volumes:
      - ./imports:/var/opt/mssql/backup:ro

What I've noted is that:

  • The error message is incorrect, as the path of the restore is writeable (i.e. touch /var/opt/mssql/data/sysft_Catalog.ndf as user mssql works fine)
  • Other databases restore fine, notably if they don't have full text search
  • The .bak file does restore to MSSQL running on Windows
  • Importing, then re-exporting from Windows to a new backup doesn't solve the issue
  • Creating an empty database and restoring to that empty database doesn't help
  • Creating the files in advance doesn't help
  • The database would not restore to an Azure SQL Server ("unknown error") or to Google Cloud SQL (same)
  • The exact same failure occurs when MSSQL is installed directly on Ubuntu (i.e. without Docker)
  • The MSSQL errorlog seems to have no additional information
  • Copy Database from Windows to Linux in SSMS doesn't work
  • Installing mssql-server-fts doesn't help

Some links to seemingly related issues:

Best Answer

Brian told me on the chat:

"@FrancescoMantovani Importing the database, dropping the catalog, then re-exporting looks to have resolved the issue. Thank you so much for your help, this was very valuable."

This was the root cause: https://support.microsoft.com/en-us/help/923355/error-message-when-you-perform-a-full-backup-of-a-database-in-sql-serv

@Brian, please add info if you need

Related Question