clone mssql database with full text catalog

總會有些project 是用到 full text catalog
好地地launch 左之後, 個user report, 點解個 search 冇反應架 ...

原來個user 就咁copy個mdf + ldf 就算
但佢有個好重要的東西叫 full text catalog
如果只是一個db 就好地地
兩個db 用同一set full text catalog 就會lock 死了 (都唔明microsoft 唔識detect 這些問題)
所以在copy database 時要花d 工夫, 連full text catalog 都要copy 埋出黎

確實microsoft msdn 有這段tutorial ...programmer 都應該要看看




   1. Run the following statement.

      ALTER DATABASE database_name SET OFFLINE

   2. Move the file or files to the new location.
   3. For each file moved, run the following statement.

      ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' )

   4. Run the following statement.

      ALTER DATABASE database_name SET ONLINE

   5. Verify the file change by running the following query.

      SELECT name, physical_name AS CurrentLocation, state_desc
      FROM sys.master_files
      WHERE database_id = DB_ID(N'<database_name>');

 Relocation for Scheduled Disk Maintenance

To relocate a file as part of a scheduled disk maintenance process, follow these steps:

   1. For each file to be moved, run the following statement.

      ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )

   2. Stop the instance of SQL Server or shut down the system to perform maintenance. For more information, see Stopping Services.
   3. Move the file or files to the new location.
   4. Restart the instance of SQL Server or the server. For more information, see Starting and Restarting Services.
   5. Verify the file change by running the following query.

      SELECT name, physical_name AS CurrentLocation, state_desc
      FROM sys.master_files
      WHERE database_id = DB_ID(N'<database_name>');