Full-text Search in Backup & Recovery

Are you a user of full-text search on SQL Server 2000?  If so, you know that you must back up the full-text catalog separately from any database or transaction log backups that you perform.

Microsoft has improved this functionality in SQL Server 2005 such that you no longer have to back up a full-text catalog separately from the database.  The SQL Server 2005 BOL states:

During a full backup in SQL Server 2005, full-text data is backed up together with other database data. The backup operation treats full-text catalogs as files. For example, the catalogs can be backed up in isolation by using a FILE= clause to select the catalogs. (The logical file name for each full-text catalog is of the form sysft_.)

So life is easier if you want to backup the database along with the full-text catalog.  But what if you want to exclude the full-text catalog for speed or space improvements of the backup and/or recovery process?

Basically, as I understand it, you could perform a file level backup of the database using FILE='foo' and list every file except the file containing the full-text catalog.  Your backup would now exclude the full-text catalog.  You could then restore from this backup to get a restoration that doesn't include the full-text catalog.

I haven't tested this yet, but I see no reason that it wouldn't work as expected.  If you have more time than me, maybe you can run a test and see how SQL Server 2005 behaves?  I'd love to hear about your findings.

Cheers!

-Kev

P.S. Thanks to Tibor Karaszi (SQL MVP), Hilary Cotter (SQL MVP), and Steve Schmidt (Microsoft Dev) for the inspiration on this entry.