Backing Up and Restoring Full-Text Catalogs

Article
12/03/2008

With this release of Microsoft SQL Server, you can use the BACKUP and RESTORE statements to back up and restore full-text catalogs in the same way you back up and restore database file data.

To back up full-text catalogs, use the BACKUP statement:

BACKUP DATABASE database_name
TO backup_device

Each full-text catalog is treated as a file and is included in the database file set that is backed up. During the backup process, full-text catalogs may not be dropped or added.

Generally, SQL Server performs these tasks during a BACKUP operation:

Temporarily suspends Microsoft Full-Text Engine for SQL Server (MSFTESQL) service and flushes to disk all pending changes to the full-text catalog. Also, SQL Server stops all write activity on the catalog and queues all changes that have not yet been processed at the start of the backup in the notification log so that these changes could be committed to the full-text catalogs when the backup is complete.
The catalog status is persisted on disk. At this point, you can still query the catalog.
Backs up all table and full-text catalog data, change tracking log, and metadata.
Backs up all data, in the transaction log, that has occurred since the backup process started.
Resumes MSFTESQL service as well as the population of full-text indexes.

For more information about backing up database data, see Creating Full and Differential Backups of a SQL Server Database. For more information about the BACKUP statement in Transact-SQL, see BACKUP (Transact-SQL).

After the full-text catalogs are backed up, SQL Server starts the MSFTESQL service, commits the updates queued in the notification log, and resumes regular operations.

In addition to the complete backup of full-text catalog and database data, you can back up full-text catalogs in the following ways:

Differential backups
To back up changes in the full-text catalogs along with database data since the last complete backup, specify WITH DIFFERENTIAL in the BACKUP command.

For example, the following statement backs up the changes, since the last complete back up, in the database data, including the full-text catalogs.
```
BACKUP DATABASE database_name
TO backup_device
WITH DIFFERENTIAL
```
Only files that have changed or been added since the last complete backup are backed up. If there are files that have been deleted since the last complete backup, these files will be deleted during the restore process. Changes that occur during the backup are appended to the notification log, backed up, and then committed when all files have been backed up.

Differential backups are not supported for full-text catalogs in FAT32 file systems.

File backup of full-text catalogs
To back up only the full-text catalog (and not the database data), specify the FILE clause in the BACKUP command.

For example, the following statement backs up only the full-text catalog fulltext_cat.
```
BACKUP DATABASE database_name
FILE = 'sysft_fulltext_cat'
TO backup_device
```
Note

The full-text catalog filename is the name of the full-text catalog prefixed by 'sys'.

To back up only the filegroup that stores multiple full-text catalogs, specify the FILEGROUP clause in the BACKUP command.

For example, the following statement backs up only the filegroup fulltext_catFG.
```
BACKUP DATABASE database_name
FILEGROUP = 'fulltext_catFG'
TO backup_device
```

Differential file backup of full-text catalogs
To back up only the changes in a full-text catalog, specify the FILE clause and WITH DIFFERENTIAL in the BACKUP command.

For example, the following statement backs up the changes, since the last complete back up, in the full-text catalog fulltext_cat.
```
BACKUP DATABASE database_name
FILE = 'sysft_fulltext_cat'
TO backup_device
WITH DIFFERENTIAL
```
To back up only the changes in all full-text catalogs stored in a filegroup, specify the FILEGROUP clause and WITH DIFFERENTIAL in the BACKUP command.

For example, the following statement backs up the changes, since the last complete back up, in all the full-text catalogs that are stored in the filegroup fulltext_catFG.
```
BACKUP DATABASE database_name
FILEGROUP = 'fulltext_catFG'
TO backup_device
WITH DIFFERENTIAL
```

To restore full-text catalogs, use the RESTORE command.

RESTORE DATABASE database_name
FROM backup_device

When this command is issued, the backed-up data -- including the full-text catalog data -- is restored from the disk location on which the data was backed up.

For more information about the RESTORE statement in Transact-SQL, see RESTORE (Transact-SQL).

In addition to the complete restore of full-text catalog and database data, you can restore full-text catalogs in the following ways:

Restore to an alternate location
To specify an alternate location to the root path of the full-text catalog, specify WITH MOVE in the RESTORE command.

For example, the following statement restores the full-text catalog fulltext_cat to the directory F:\FtCat.
```
RESTORE DATABASE AdventureWorks
FROM backup_device
WITH MOVE 'sysft_fulltext_cat' TO 'F:\FtCat'
```
When WITH MOVE is specified, SQL Server updates the metadata for the full-text catalog in the sys.fulltext_catalogs and the sys.databases_files catalog views with the new location of the catalog's root path. The full-text catalog metadata is updated in the same way and at the same time as other database and table data files.

WITH MOVE can also be specified to restore to an alternate location differential backups as well as backups of full-text catalog files or filegroups.

Note

You cannot restore a full-text catalog to the root directory.

Restore differential backups of full-text catalog
To restore the differential backup of the full-text catalogs and database data, perform two restore operations: a complete restore followed by a differential restore. The second restore updates only the database extents that changed between the full and differential backups.
```
RESTORE DATABASE AdventureWorks
FROM backup_device
WITH NORECOVERY
RESTORE DATABASE AdventureWorks
FROM backup_device2
FILE = 'sysft_fulltext_cat'
```
In this scenario, the database remains offline and Microsoft Search stopped between the restore operations. They are brought back online only after the second restore is complete.

Differential file and filegroup backups also can be restored.

Restore file backups of full-text catalogs
To restore file and filegroup backups of full-text catalogs, specify the FILE or FILEGROUP clause in the RESTORE command.
```
RESTORE DATABASE AdventureWorks
FILE = 'sysft_fulltext_cat'
FROM backup_device

--or 
RESTORE DATABASE AdventureWorks
FILEGROUP = 'fulltext_catFG'
FROM backup_device
```
If you try to restore full-text catalog data with an older version of the data, SQL Server returns a warning.

Note

When restoring a transaction log back up, the full-text index could potentially be in an inconsistent state. To bring the full-text index to a consistent state you need to run a full crawl.

Share via

Backing Up and Restoring Full-Text Catalogs

See Also

Concepts

Help and Information

Additional resources