Move FTData folder.

Question

Move FTData folder.

chrisrdba 471

Greetings. I've moving my DB's to a new drive on the same instance. I moved the FTData folder to the new drive for safe keeping, but for the life of me can't figure out how to tell SQL Server it's moved.

I also get this ugly error message on startup:

Message
SQL Server failed to set security information on the full-text FilterData directory in the FTData folder. Full-text indexing of some types of documents may fail until this issue is resolved. You will need to repair the SQL Server installation.

Plenty of articles reference changing the value in the registry folder

"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance id>\Setup\FullTextDefaultPath", but that appears to be for older versions only. I don't have that value.

Plenty of other articles reference dead MS links.

How do I correctly move this guy.

Sam of Simple Samples 5,546 Reputation points

2021-08-31T22:30:51.577+00:00

I once deleted the file of a database and that caused problems and I asked how to then remove it from SQL Server. No one would answer the question. I suggest you back up and move the file back and then ask how to move it the right way.

5 answers

Your answer

Sam of Simple Samples 5,546 Reputation points

2021-08-31T22:30:51.577+00:00

I once deleted the file of a database and that caused problems and I asked how to then remove it from SQL Server. No one would answer the question. I suggest you back up and move the file back and then ask how to move it the right way.

Answer 1

Erland Sommarskog 121.8K MVP Volunteer Moderator

As I found my attempt to answer last night quite embarrassing, I had to dig into this a little more.

I still I have not found any documentation, but I did some investigation. I get the impression that the FTData folder is created in the folder which you specify as the Data folder when you run Setup. This value is then found as HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.INSTANCE\Setup\SqlDataRoot. (I made an installation where I set the data folder to be different from the other folders in the setup.) Note that this is not the same value that you see SSMS as the default data directory.

I assuming that you have this one located on the drive that is going away, and if my theory is correct, you would need to update this value. But I have not actually tested that this is the way to go.

chrisrdba 471 Reputation points

2021-09-01T21:55:31.493+00:00

No need for embarrassment Erland. I affectionately refer to you (and a few others) as "one of those guys that's forgot more about SQL Server than I'll ever know".

At any rate, I had the same impression as you yesterday after I posted this question. I found the registry entry, changed it, and the issue persisted after a restart.

Thanks for looking though!
Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator

2021-09-02T06:46:32.187+00:00

Ah, that's great to hear!
Ricardo A. Mari 1 Reputation point

2021-09-29T21:25:06.17+00:00

Erland, I have the same error after a upgrade from 2012 to 2017 and changing "SqlDataRoot" REG_SZ value was a solution. Thank you very much.

Answer 2

Erland Sommarskog 121.8K MVP Volunteer Moderator

I can't claim knowledge, but I my suggestion is that you move the folder back to were you found it. On my instance, I only see a bunch of small XML files in that folder. But it is true that I'm not using full-text very much.

chrisrdba 471 Reputation points

2021-08-31T22:00:31.74+00:00

The drive I found it on is going away, so that's not an option.
Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator

2021-08-31T22:03:00.517+00:00

Hm, mine is right in the installation folder on the C: drive, but yours were elsewhere?
chrisrdba 471 Reputation points

2021-08-31T22:05:36.49+00:00

Yes it is.
Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator

2021-08-31T22:06:55.827+00:00

Hm, that suggests that it is possible to move it then, I guess. Unfortunately, I don't know anything on the top of my head, and my bedtime is rapidly approaching.
chrisrdba 471 Reputation points

2021-08-31T22:22:07.14+00:00

Thanks for taking a stab at it, and have a good night!

Answer 3

AmeliaGu-MSFT 14,006 Microsoft External Staff

Hi chrisrdba,

What is the version of SQL Server are you using? Is your SQL Server upgraded from SQL Server 2000 or SQL Server
2005?
Starting from SQL Server 2008, full-text catalogs are integrated into the database and is now a logic object without a specific file path in the system. Therefore, a file path is no longer associated with the Full-Text Search catalog. And the path in the sysfulltextcatalogs view and the path returned by the sp_help_fulltext_catalogs and sp_help_fulltext_catalogs_cursor system stored procedures will now return a NULL value. Please refer to this doc for more details.

SQL Server failed to set security information on the full-text FilterData directory in the FTData folder. Full-text indexing of some types of documents may fail until this issue is resolved. You will need to repair the SQL Server installation.

Please refer to this thread which might be helpful.

Best Regards,
Amelia

If the answer is helpful, please click "Accept Answer" and upvote it.

chrisrdba 471 Reputation points

2021-09-01T15:02:28.153+00:00

This guy started life as 2016, and was then upgraded to 2019. Every link I see that mentions upgrades is for older versions.

Using the second link, I see that the DB Current Version is 904.

I think what you're saying is I don't need to worry about this -- is that correct?

I'd feel a lot better about it if I wasn't getting the error message.
AmeliaGu-MSFT 14,006 Reputation points Microsoft External Staff

2021-09-03T06:23:11.237+00:00

Hi chrisrdba,

Thanks for your reply.
I'm not sure if we don't need to worry about it, because I haven't encountered this issue yet.
I find a doc about upgrading Full-Text Search from an earlier version of SQL Server, hope this could help you.

Best Regards,
Amelia
Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator

2021-09-03T06:39:02.6+00:00

Amelia, in case you have noticed, Chris has already sorted out this issue. Check my answer below and Chris's comment.

(Chris you could mark my post as the answer, even if it is a little unfair, since you figured this out on your own.)
chrisrdba 471 Reputation points

2021-09-03T16:54:15.827+00:00

Erland I think you misread my comment, the issue remains.
Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator

2021-09-03T21:04:25.2+00:00

Oops, sorry about that! I was too optimistic. (Note to self: this thread is not going well for you, is it?)

I don't really have much of a clue. I looked at the error message, and to verify that it is due to the FTData folder missing (and not a permissions issue), I renamed the FTData folder to something else and restarted SQL Server. Sure enough, I got that very error message. (Although, I failed to observe it at first.)

Answer 4

chrisrdba 471

We've engaged Microsoft on this. Our tech has been unable to reproduce with the message I'm getting, and unable to figure out why I'm getting it.

Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator

2021-09-11T21:48:17.467+00:00

Thanks for the update! Please let us know the outcome. I had a conversation with an MVP colleague, and his suggestion was that you would have reinstall SQL Server. I would like to believe that there is an easeier way.
chrisrdba 471 Reputation points

2021-09-12T03:51:27.947+00:00

The latest is from them is to run the Repair option -- obviously I'm trying to avoid this. We're supposed to do this all in Prod next weekend so I'm hammering the vendor to pinpoint precisely how full text gets used in the app to make sure we're testing adequately.

Will advise...
Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator

2021-09-12T14:13:18.957+00:00

IIf it uses full-text at all. I get the feeling that this is a message that appears due to a routine operation that SQL Server performs at startup. Would it be that you are not using full-text on this instance, maybe the easy way out is to uninstall the feature.
chrisrdba 471 Reputation points

2021-09-12T17:22:17.943+00:00

No, we have some catalogs in the DB. However, I get the same feeling you do in that it's simply a generic message.

As mentioned the MS tech wants me to run the repair option. I may throw caution to the wind and give it a whirl tomorrow in one on my non-prods.

I'll keep you posted, and again thanks for all the help!
chrisrdba 471 Reputation points

2021-09-16T22:38:45.563+00:00

The Repair process simply hangs in non-prod. However, we've verified that full text searching is working as expected.

All said this is simply an error we're going to live with, unless more is revealed in when I do this in Prod this weekend.

Answer 5

We had the same issue happening , having moved the system databases of a freshly installed MSSQL Server 2019, according to the procedure https://learn.microsoft.com/en-us/sql/relational-databases/databases/move-system-databases?view=sql-server-ver15

In that procedure is stated that the location in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.INSTANCE\Setup\SqlDataRoot should be modified.

We solved the issue as follow:

Create the following folder and subfolder in the new SqlDataRoot:

FTData
FTData\FilterData

Set the correct rights and inheritance (!) on these folders:

Do mind the rights of the Dbengine service account (typically NT SERVICE\MSSQLSERVER) and Fulltext search service account (NT SERVICE\MSSQLFDLauncher) on these folders : they should be set according to those applied on the folders in the original SqlDataRoot location

stop the dbengine and Fulltext search launcher services

copy the content of those folders in the new location

restart the dbengine, which should automatically start the fulltext search service

the error in the eventlog should not appear anymore

Share via

Move FTData folder.

5 answers

Your answer