Want to know what terror feels like? Open up the properties for a production database and see that the last backup date is null. That'll do it. Then look in the log and see that the reason the database isn't backing up is a truly funky error message:

Msg 9987, Level 16, State 1, Line 1 The backup of full-text catalog '' is not permitted because it is not online. Check errorlog file for the reason that full-text catalog became offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.

The most interesting part of the error message is that the name of the full-text catalog is an empty string.

As you might guess, this was something that needed figured out and fast! So it was off to Google, which failed me. And Twitter, which didn't help (when BrentO doesn't have any idea, you know you've found something interesting). And finally StackOverflow. All the while, working on coming up with a solution.

I did a file backup of the data and restored it to another database. That gave me a copy of the database with the same problem, well, almost the same problem. On the original server, I was running the following SQL statement:

select state_desc,* from sys.master_files where type_desc = 'FULLTEXT'

This showed the file state as being OFFLINE. On the new server they showed up as RECOVERY PENDING. And the full text search catalogs showed up in SQL Management Studio. So I tried deleting one, and that turned the file back to OFFLINE.

This enabled me to at least reconstruct what had happened. The database had been restored from SQL 2000 to SQL 2005, which doesn't automatically restore the full text search catalogs. That requires you to do additional restores at the file level, which we didn't do. But that was ok since we weren't using full text search in the database, we just dropped the catalogs.

Unfortunately, that left the database in a state where it threw the error and wouldn't let it back up.

The solution, it turns out, was very simple. Detach the database but specify @keepfulltextindexfile=N'false'. This flag is true by default, but setting it to false removes all of the full text indexing file information. Detached with that flag, reattached and was able to backup on the server I was testing on.

Did that on the production server and had a very short downtime and all was well with the world... well, better at least.