Knowledge Base

Moving a full-text catalog on RESTORE

Category: How do I?
Date: 18 Nov 2008
Product: SQL Backup
SQL Server 2005 and SQL Server 2008, unlike SQL Server 2000, store full-text catalog data as a "special" filegroup. By default, backing up and restoring a full-text catalog will place the data in the file path of the server from which it had originally been backed up. This file path may not be suitable for the server you are restoring the catalog to because it may be an invalid path or inaccessible to SQL Server.

Changing the restore path for a full-text catalog, therefore, is simply a matter of using the WITH MOVE... command to place the data in the desired folder. The name of the full-text catalog logical file name is well-known -- it is the name of the full-text catalog prefixed by 'sysft_'. When restoring the database using SQL Backup's GUI, the "'filegroups and files" panel in step 2 of the restore wizard allows you to change the locations for the full-text indexes indexes before submitting the restore job to SQL Server 2005.

In the following example script, the full-text catalog called Tracking is being moved to the "'d:\sql2005\mssql.1\mssql\ftdata" folder:

EXECUTE master..sqlbackup '-sql "RESTORE DATABASE [IssueTracker] FROM DISK=''d:\sql2005\backups\IssueTracker_full.sqb'' WITH REPLACE, MOVE ''sysft_Tracking'' TO ''d:\sql2005\mssql.1\mssql\ftdata''"'