Moving databases between different servers when replication and full text catalog are defined !

I have come across many articles on web that describes how to move databases from one server to another. But I didn’t find much information pertaining to my situation.

Recently, we carried out a server consolidation project and I was required to move databases and cubes between different servers. Moving databases is fine and is pretty straight forward task (you can use Detach/Attach method). But when the databases you are moving are involved in replication or a Full text (FT) catalog is defined on a database, there things become tricky !

In my situation, transactional replication was involved and the databases were having FT catalog defined. So to move databases, I followed below mentioned steps:

1. Use the below query to find what files are involved with the database.

USE [database_name]

go

select name as logical_name, physical_name, type_desc, state_desc

from sys.database_files

This will give you the details of the files associated with a particular database. (Note: here you can use SP_HELPDB ‘db_name’; but this will not give you the location of FT file.)

2. Then script out the replication. (One of the cool feature of SQL server 2005 SSMS is the ability to script out things: rt click the publisher à generate scripts à select create scripts and give a proper location where the script file will be put).

3. Also, to remove replication generate Drop scripts.

4. Then you can use the Detach/Attach method of moving databases form one server to another.

--Detach the database.

USE master

Go

Sp_detach_db [database_name]

Go

At this point, Copy the mdf, ldf, ndf (if any) and the FT directory to the new server.

--Physically move the full-text catalog to the new location.

--Attach the database and specify the new location of the full-text catalog.

CREATE DATABASE [database_name] ON

(FILENAME = 'S:\data\[database_name]_Data.mdf'),

(FILENAME = 'X:\Logs\[database_name]_log.ldf'),

(FILENAME = 'S:\myFTCatalogs\AdvWksFtCat')

FOR ATTACH;

GO

5. After the attach is done, you can verify that the files that are attached are online or not by using:

USE [database_name]

go

select name as logical_name, physical_name, type_desc, state_desc

from sys.database_files

6. Then you can run the create replicaiton scripts to recreate replication. (Note, as you are moving databases to different server, the scripts are to be edited for the new location of the server!).

GOTCHAS!

1. If replication is not dropped, then detach database will fail.

2. While attaching the database, if the full text catalog path is not provided correctly, then the full backup will fail with following error:

"Backup failed for Server 'ComputerName\SQLInstanceName' " or "The backup of the file or filegroup 'sysft_FullTextCatalog' is not permitted because it is not online"

This can be resolved as follows:

ALTER DATABASE [database_name] SET OFFLINE

ALTER DATABASE [database_name] MODIFY FILE ( NAME = [logical_name_of_FTCatalog] , FILENAME = 'S:\FullText\catalog_folder_name')

ALTER DATABASE [database_name] SET ONLINE

This should bring the FT catalog ONLINE and you are good to take full backup.

If that does not bring the FTcatalog Online, then you have to rebuild the FT catalog or delete the old one and recreate from scratch.

Ref: http://support.microsoft.com/kb/923355; http://msdn.microsoft.com/en-us/library/ms345483.aspx; http://msdn.microsoft.com/en-us/library/ms174397.aspx; http://social.msdn.microsoft.com/forums/en-US/commserver2007/thread/307b9141-9063-4788-9673-e23b49fe6aeb

Hope this helps !

\\K