1

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.

How can I restore this database?

I'd be willing to restore it without the full-text index - is there a way to do that?

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)

Note: I have found this Stack Overflow post; the poster had a different problem, but was also willing to restore the DB without the full-text data. It doesn't say how (if!) he ever resolved his problem, so it doesn't really give me an answer.

  • The Stack Overflow post you provide a link to isn't the same problem - that individual was trying to restore the DB, including the full-text index, and had indicated that the data and log files should be moved, but not that the full-text file should be moved; the error was that they couldn't write the full-text file because there was already a file with the same name, and it was in use (by the original copy of the database). – RDFozz Jul 12 '18 at 19:37
  • Thanks! I know it is not the same problem, but, is it posible to restore a full database without text. If yes, the solution will be enough for me. – user155183 Jul 12 '18 at 19:39
  • Not that I know of - if the .ft file is a part of the backup, then it has to go somewhere. Have you tried changing the directory the .ft file is written to? – RDFozz Jul 12 '18 at 19:55
  • Are you sure that the restore was able to restore the other two files successfully - did the error not happen until after those files were created? Can you get another Windows backup of a DB without a full-text index, and try to restore that? – RDFozz Jul 12 '18 at 20:19
  • Thanks for the editing! Yes, I have tried 2 different folders. Originally I tested in personal folder (for the 3 files, mdf, ldf and ft). After, I tried in the mssql/data folder as suggested by MS docs. And, yes, I changed in both cases `chmod 777 data`` – user155183 Jul 12 '18 at 20:19
0

I had exactly the same issue and found a workaround by specifying a combination of Windows/Linux path for the full text files in the restore database files as options. E.g. instead of the configured default "/SQLServerData/catalogname", I changed it to "C:\SQLServerData/catalogname" and then it was happy.

Your Answer

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