Post reply

Moving database files and catalogs. Not sure how to move catalog.

  • January 22, 2018 at 8:06 pm

    #342028

    Folks, this is part discovery along with a problem so pelase read my observations as what I THINK is going on. This may be part of the problem.

    I needed to move the database files from one drive to another.

    Using appropriate ALTER DATABASE commands I successfully moved MDF, LDF and NDF files and brought the database back online without incident.

    However when running the following query to ensure nothing was missed I found an exception.
    SELECT
      db.name AS DBName,
      type_desc AS FileType,
      Physical_Name AS Location
    FROM
      sys.master_files mf
    INNER JOIN
      sys.databases db ON db.database_id = mf.database_id

    I get a row returned that looks like this

    S_Production  |  FULLTEXT  |  D:\MSSQL_Databases\S_Production_2.SearchAddress

    The file ideally should be on the C drive along with the MDF,LDF,NDF.

    The actual path is to a directory rather than a file.

    After reading I understand this is a Full Text Catalog and when I look at the database

    DBName > Storage > Full Text Catalogs > SearchAddress appears.

    I get the usage of ALTER for moving files but does it apply in this case and if so what is the Logical Name and what is the path?
    eg
    USE master; --do this all from the master
    ALTER DATABASE S_Production
    MODIFY FILE (name='SearchAddress'
         ,filename='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\S_Production_2.SearchAddress);

    I'd considered rebuilding but I suspect this would just rebuild in the same location.

    This is a live database using merge replication so anything I do has to be done insitu and without detach or other methods that break merge replication.

    Your advice appreciated.

  • January 23, 2018 at 8:12 am

    #1976913

    You can get the name and path from sys.database_files.
    And yes the process looks correct. You can find it documented in the follow - check the Moving Full-text Catalogs section:
    Moving User Databases

    Sue

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply