While trying to backup a SQL database using Microsoft SQL server management studio the following error occurs:

Microsoft SQL Server Management Studio
——————————

Backup failed for Server ‘ServerName’. (Microsoft.SqlServer.Smo)

——————————
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The backup of the file or filegroup “sysft_DatabaseName” is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)

——————————

In this case the error is rather misleading as the database is online and fully accessible, on further investigation it appears that the full text catalogue is what is causing the problem. A missing or corrupt full text catalogue will cause this error, in this case it is the result of the database being transferred from an SQL2000 to an SQL2005 server by detaching, moving the ldf and mdf files and re-attaching to the new server.

The index needs to be removed and rebuilt to solve this error.

To delete the catalogue open SQL Server Management Studio and navigate down through the problem database to Storage -> Full Text Catalogs, right click catalogue and select Delete from the context menu.

If you want to reuse the same catalogue name you will have to flush the transaction logs, best done by backing up the database. Alternatively you can truncate the transaction logs immediately using the SQL command ‘BACKUP LOG dataBaseName WITH TRUNCATE_ONLY but be aware that this will invalidate any current transaction log backups so you should ensure that you have a full database backup before running this command.

If you don’t flush the logs before recreating the catalogue you will see this error:

Microsoft SQL Server Management Studio
——————————

Cannot execute changes.

——————————
ADDITIONAL INFORMATION:

Create failed for FullTextCatalog ‘CatalogName’. (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

File ’sysft_CatalogueName’ cannot be reused until after the next BACKUP LOG operation. (Microsoft SQL Server, Error: 1833)

——————————

The backup should now complete successfully, so the original problem is solved but we now need to recreate the full text catalogue and wait for it to rebuild before it can be used. Be aware that this can take a considerable amount of time with a large database.