0

UPDATE: I continue with the same error, but, with the comments, I have done some progress: - Initially, the full text search feature was not in my installation. I installed with https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-full-text-search?view=sql-server-linux-2017 - I have checked the unsupported elements in https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-release-notes?view=sql-server-linux-2017#Unsupported . Full text search in Linux has some filters missing (PENDING: I have a clue that I have to investigate: The original database is in cp1252 charset. It seems (not confirmed) that full text search works better with UTF-8)

ORIGINAL QUESTION:

I'm trying to move a Windows SQL Server database from Windows 10 to Linux.

For this, I'm following the instructions in

https://docs.microsoft.com/es-es/sql/linux/sql-server-linux-migrate-restore-database?view=sql-server-linux-2017

The linux database is freshly installed in an Ubuntu 16.04.4 LTS.

The Windows database backup is a previously existing (I have not executed the backup, but it's a full backup)

When I try to restore, it generates an error in the catalog database (access denied), as shown (database name changed to 'mydb' for privacy):

sqlcmd -S localhost -U SA -Q "RESTORE DATABASE mydb 
FROM DISK = '/var/opt/mssql/backup/mydb_backup_201804300000.bak' 
WITH MOVE 'mydb' TO '/var/opt/mssql/data/mydb.mdf', 
MOVE 'mydb_log' TO '/var/opt/mssql/data/mydb_log.ldf', 
MOVE 'sysft_appuser_catalog3' TO '/var/opt/mssql/data/catalog.ft'"

Msg 7610, Level 16, State 1, Server irulan, Line 1
Acceso denegado a '/var/opt/mssql/data/catalog.ft' o la ruta de acceso no es válida.
Msg 3156, Level 16, State 50, Server irulan, Line 1
El archivo 'sysft_appuser_catalog3' no se puede restaurar en '/var/opt/mssql/data/catalog.ft'. Utilice WITH MOVE para identificar una ubicación válida para el archivo.

The other 2 files (mdf and ldf are created without problems in the same folder). I have tried with different file names, creating previously (touch) the file, and so on with no success.

This is the output of FILELISTONLY (to check the content of backup)

LogicalName                                                                                                                      PhysicalName                                                                                                                                                                                                                                                         Type FileGroupName                                                                                                                    Size                 MaxSize              FileId               CreateLSN                   DropLSN                     UniqueId                             ReadOnlyLSN                 ReadWriteLSN                BackupSizeInBytes    SourceBlockSize FileGroupId LogGroupGUID                         DifferentialBaseLSN         DifferentialBaseGUID                 IsReadOnly IsPresent TDEThumbprint                              SnapshotUrl                                                                                                                                                                                                                                                                                                                                     
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---- -------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- -------------------- --------------------------- --------------------------- ------------------------------------ --------------------------- --------------------------- -------------------- --------------- ----------- ------------------------------------ --------------------------- ------------------------------------ ---------- --------- ------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mydb                                                                                                                             D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydb.mdf                                                                                                                                                                                                    D    PRIMARY                                                                                                                                    3460300800       35184372080640                    1                           0                           0 D64B0490-3FF6-4EFE-A9A1-491B5993F3AF                           0                           0           2348613632             512           1 NULL                                        30094000017824000037 B7E468AB-78C2-4732-8D73-2F07E3ABAF9D          0         1 NULL                                       NULL                                                                                                                                                                                                                                                                                                                                            
mydb_log                                                                                                                         D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydb_log.ldf                                                                                                                                                                                                L    NULL                                                                                                                                       1540227072        2199023255552                    2                           0                           0 A6B8CF28-C3D8-4B50-B030-4D5B14F82084                           0                           0                    0             512           0 NULL                                                           0 00000000-0000-0000-0000-000000000000          0         1 NULL                                       NULL                                                                                                                                                                                                                                                                                                                                            
sysft_appuser_catalog3                                                                                                           D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\appuser_catalog3                                                                                                                                                                                          F    PRIMARY                                                                                                                                        931744                    0                65539        17974000000690900001                           0 0B0AEAB0-86A2-42ED-9B37-E70EE556383C                           0                           0               983040             512           1 NULL                                        30094000017824000037 B7E468AB-78C2-4732-8D73-2F07E3ABAF9D          0         1 NULL                                       NULL                                                                                                                                                                                                                                                                                                                                            

(3 rows affected)

Also, I have found in stack another post with similar problem, but it doesn't say the final name he/she used.

https://stackoverflow.com/questions/536182/mssql2005-restore-database-without-restoring-full-text-catalog

migrated from stackoverflow.com Jul 13 '18 at 8:01

This question came from our site for professional and enthusiast programmers.

  • Silly question - is there already a file located at /var/opt/mssql/data/catalog.ft? If so, I'd try deleting it and trying the restore again. – Ben Thul Jul 12 '18 at 16:47
  • Sorry, I have not well explained. I have tried with empty directory too (the first try). After, I tried creating the file to discard permissions issue. – Sourcerer Jul 12 '18 at 17:03
  • @ClimberBear - This link from the help center may help you re-establish ownership of this question here on DBA.SE, so you can edit it without the approval process. – RDFozz Jul 16 '18 at 17:38
0

Previous to restore your DB over Linux Os you should be sure if your Server installation have all required features like your previous server where your DB was running. Also could be a good practice check the software limitations on Linux like FileTables, FileStream, Merge Replications, etc. See link below.

However as I said previously may be that your SQL installation requires any standalone installation, remember SQL Server over Linux use separate installers for some features like Full-Text Search, SQL Agent and others.

Please read here if some version feature limitation is not affecting your restoration. https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-release-notes?view=sql-server-linux-2017#Unsupported

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy