Full Text not observing MOVE on a RESTORE

mrclod

Running into an issue when restoring a backup to an alternate server. This server contains multiple restores at any given time.

To get around file conflicts, we move files to appropriate places or names using the MOVE option. While restoring, everything appears okay, but once done, the files disappear and/or are moved one directory level higher, but with original naming (causing naming conflicts on other restores).

We did not see this issue with 6.4 version.

Trying this with 7.4 SQL Backup, Windows Server 2008 R2, SQL Server 2008 R2 SP1.

Here is command example generated by UI. Added some <CR> to make it easier to read:


EXECUTE master..sqlbackup
'-SQL "RESTORE DATABASE [sandbox_s4]
FROM DISK = ''\\BackupServer\DatabaseBackups\s4\FULL_s4_20130729_004816.sqb''
WITH PASSWORD = ''<password>'', RECOVERY,
MOVE ''db_Data'' TO ''C:\DBC1\I7\DATA\sandbox_s4.mdf'',
MOVE ''sysft_KBIndex'' TO ''C:\DBC1\I7\LOG\FullText\KBIndex_sandbox_s4'',
MOVE ''sysft_FieldValueIndex'' TO ''C:\DBC1\I7\LOG\FullText\FieldValueIndex_sandbox_s4'',
MOVE ''sysft_HistoryIndex'' TO ''C:\DBC1\I7\LOG\FullText\HistoryIndex_sandbox_s4'',
MOVE ''db_Log'' TO ''C:\DBC1\I7\LOG\sandbox_s4_log.ldf'', REPLACE, ORPHAN_CHECK"'


In this case, the sysft files are left as more generic names in the FullText directory and not within their respective MOVE directories.

For example, sysft_KBIndex would be expected to be contained in C:\DBC1\I7\LOG\FullText\KBIndex_sandbox_s4 with all it's related files, however, we instead see just this:

C:\DBC1\I7\LOG\FullText\ftrow_KBIndex.ndf


Tried adding a filename even to the MOVE command and that was ignored as well.

petey

Could you please post the contents of the SQL Backup log file for that restore process? The default folder where the logs are stored is C:\ProgramData\Red Gate\SQL Backup\Log\<instance name>.

Thanks.

mrclod

Here are log files contents:


===============================================
SQL Backup log file 7.4.0.23

-SQL "RESTORE DATABASE sandbox_S4 FROM DISK='\\BackupServer\DatabaseBackups\P5\S4\FULL_s4_20130729_004816.sqb' WITH REPLACE, MOVE 'db_data' TO 'C:\DBC1\I7\DATA\sandbox_S4.mdf', MOVE 'db_log' TO 'C:\DBC1\I7\LOG\sandbox_S4_log.ldf', MOVE 'sysft_KBIndex' TO 'C:\DBC1\I7\LOG\FullText\KBIndex_sandbox_S4', MOVE 'sysft_FieldValueIndex' TO 'C:\DBC1\I7\LOG\FullText\FieldValueIndex_sandbox_S4', MOVE 'sysft_HistoryIndex' TO 'C:\DBC1\I7\LOG\FullText\HistoryIndex_sandbox_S4', PASSWORD='XXXXXXXXXX' "

---

PROCESSES COMPLETED SUCCESSFULLY

---

7/31/2013 11:42:51 AM: Restoring sandbox_S4 (database) on I7 instance from:
7/31/2013 11:42:51 AM: \\BackupServer\DatabaseBackups\P5\S4\FULL_s4_20130729_004816.sqb

7/31/2013 11:42:51 AM: RESTORE DATABASE [sandbox_S4] FROM VIRTUAL_DEVICE = 'SQLBACKUP_67B9237E-4DF4-47AA-B059-5B2D4EEF055A', VIRTUAL_DEVICE = 'SQLBACKUP_67B9237E-4DF4-47AA-B059-5B2D4EEF055A01', VIRTUAL_DEVICE = 'SQLBACKUP_67B9237E-4DF4-47AA-B059-5B2D4EEF055A02' WITH BUFFERCOUNT = 18, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576 , RECOVERY, MOVE 'db_data' TO 'C:\DBC1\I7\DATA\sandbox_S4.mdf', MOVE 'db_log' TO 'C:\DBC1\I7\LOG\sandbox_S4_log.ldf', MOVE 'sysft_KBIndex' TO 'C:\DBC1\I7\LOG\FullText\KBIndex_sandbox_S4', MOVE 'sysft_FieldValueIndex' TO 'C:\DBC1\I7\LOG\FullText\FieldValueIndex_sandbox_S4', MOVE 'sysft_HistoryIndex' TO 'C:\DBC1\I7\LOG\FullText\HistoryIndex_sandbox_S4', REPLACE

7/31/2013 11:48:23 AM: Processed 2578912 pages for database 'sandbox_S4', file 'db_Data' on file 1.
7/31/2013 11:48:23 AM: Processed 172 pages for database 'sandbox_S4', file 'db_Log' on file 1.
7/31/2013 11:48:23 AM: Processed 122442 pages for database 'sandbox_S4', file 'sysft_KBIndex' on file 1.
7/31/2013 11:48:23 AM: Processed 46413 pages for database 'sandbox_S4', file 'sysft_FieldValueIndex' on file 1.
7/31/2013 11:48:23 AM: Processed 72707 pages for database 'sandbox_S4', file 'sysft_HistoryIndex' on file 1.
7/31/2013 11:48:23 AM: Converting database 'sandbox_S4' from version 611 to the current version 661.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 611 to version 621.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 621 to version 622.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 622 to version 625.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 625 to version 626.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 626 to version 627.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 627 to version 628.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 628 to version 629.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 629 to version 630.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 630 to version 631.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 631 to version 632.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 632 to version 633.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 633 to version 634.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 634 to version 635.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 635 to version 636.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 636 to version 637.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 637 to version 638.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 638 to version 639.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 639 to version 640.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 640 to version 641.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 641 to version 642.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 642 to version 643.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 643 to version 644.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 644 to version 645.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 645 to version 646.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 646 to version 647.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 647 to version 648.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 648 to version 649.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 649 to version 650.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 650 to version 651.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 651 to version 652.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 652 to version 653.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 653 to version 654.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 654 to version 655.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 655 to version 660.
7/31/2013 11:48:23 AM: Database 'sandbox_S4' running the upgrade step from version 660 to version 661.
7/31/2013 11:48:23 AM: RESTORE DATABASE successfully processed 2820644 pages in 302.444 seconds (72.860 MB/sec).
7/31/2013 11:48:24 AM: SQL Backup process ended.

petey

I could not reproduce your error using SQL Backup 7.4.0.23. It doesn't make sense why there is a secondary data file named ftrow_KBIndex.ndf in the target full-text folder.

Could you please try restoring this backup on another server, and see if you get the same results?

Thanks.