The Situation: I was recently involved in the migration of a group of core databases from three SQL Server 2005 instances to three SQL Server 2012 instances. There were quite a few moving parts that needed to be considered as part of the upgrade including replication, CLR, service broker and full text search.

The basic approach was to build the 2012 instances in a DR site while the main site continued to run on SQL 2005. Then log-ship the databases from the 2005 instances to the 2012 instances. This way the upgrade of the databases from 2005 to 2012 is postponed until cut-over when recovery is run. (As an aside – this is the reason that you can’t use with standby on the log restores; because this means the database has to go through recovery and hence would upgrade.)

The Error: Before the cutting over the main SQL instances I was working on a single instance upgrade of a few smaller databases. Still going from 2005 to 2012, still using a bunch of technologies including full text search and still using log-shipping to migrate the databases. The morning of the cut-over arrived and I recovered one of the databases…

Converting database ‘my_database’ from version 611 to the current version 706.

Database ‘my_database’ running the upgrade step from version 611 to version 621.

Database ‘my_database’ running the upgrade step from version 621 to version 622.

Database ‘my_database’ running the upgrade step from version 622 to version 625.

Database ‘my_database’ running the upgrade step from version 625 to version 626.

Database ‘my_database’ running the upgrade step from version 626 to version 627.

Database ‘my_database’ running the upgrade step from version 627 to version 628.

Database ‘my_database’ running the upgrade step from version 628 to version 629.

Database ‘my_database’ running the upgrade step from version 629 to version 630.

Database ‘my_database’ running the upgrade step from version 630 to version 631.

Database ‘my_database’ running the upgrade step from version 631 to version 632.

Database ‘my_database’ running the upgrade step from version 632 to version 633.

Database ‘my_database’ running the upgrade step from version 633 to version 634.

Database ‘my_database’ running the upgrade step from version 634 to version 635.

Database ‘my_database’ running the upgrade step from version 635 to version 636.

Database ‘my_database’ running the upgrade step from version 636 to version 638.

Database ‘my_database’ running the upgrade step from version 638 to version 639.

Database ‘my_database’ running the upgrade step from version 639 to version 640.

Database ‘my_database’ running the upgrade step from version 640 to version 641.

Database ‘my_database’ running the upgrade step from version 641 to version 642.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Msg 30097, Level 23, State 100, Line 1

The fulltext catalog upgrade failed because of an inconsistency in metadata between sys.master_files and sys.fulltext_catalogs for the catalog ID xx in database ID x.

Try to reattach this database. If this fails, then the catalog will need to be dropped or recreated before attach.

During the course of setting up log-shipping and planning for the migration I had recovered the databases a number of times and had never seen this error before. The database was in a recovery pending state and the only way forward seemed to be to drop the full text catalogs at the source, take a fresh full backup and restore that backup. (Don’t be fooled by the suggestion in the message to reattach the database – I couldn’t find a way to make this work in this situation.)

That approach worked for the smaller database but it wouldn’t be an option for the main site. In this case we had several terabytes of database, we couldn’t drop full text, backup, copy and restore in any reasonable length outage window.

The Theory: As well as log-shipping from the main site to DR we also log-shipped into our data warehouse every night. The data warehouse server was also a SQL 2012 box. I was curious about why I hadn’t seen this error on the data warehouse server – after all we recovered the main database onto it every morning. Then one morning the recovery failed and we got the same error.

The theory was – if a change was made to the full text schema this would break the recovery. I needed to be able to reliably reproduce this – and then solve it before we attempted to migrate the main site.

The Reproduction: I set up a VM with a SQL 2005 instance and a SQL 2012 instance and log shipped from the 2005 to the 2012 instance. I made a change to one of the full text indexes (dropped a column) and allowed a log backup and restore to happen. Then I tried to recover the 2012 database and I got the error. I’d never been so happy to see an error in my life. I did it again, and got the error again. Now that I could reliably reproduce the error I could test ways of avoiding it.

The Solution: The first and most obvious solution was to put a change freeze on any full text schema changes. These are fairly rare so that wasn’t too much of a hardship. The other solution was to drop the full text indexes and catalogs at the source database, allow a log backup to occur (even a final tail of log backup). Allow the log backup to restore and then recover the database. Then rebuild the full text catalog and indexes.

For completeness dropping and recreating a full text index and catalog:

-- disable and drop the index and catalog
ALTER FULLTEXT INDEX ON [my_table] DISABLE;
GO
DROP FULLTEXT INDEX ON [my_table]
GO
DROP FULLTEXT CATALOG [my_table];
GO
 
-- now recreate the catalog and index
CREATE FULLTEXT CATALOG [my_table]
    IN PATH N'C:\some_path' --no effect from SQL2008
    WITH ACCENT_SENSITIVITY = OFF
    AUTHORIZATION [dbo]
GO
 
CREATE FULLTEXT INDEX ON [my_table](
    [my_column], [my_other_column]
    )
KEY INDEX [PK_my_table] ON [my_table]
WITH CHANGE_TRACKING AUTO
GO