So this problem really got me down until I applied myself to it and then it turned out to just need a bit o the old common sense.

I received a SQL Server .BAK database file from a client recently and like I've done a million times before I went to create the backup as a new database using the following method:

* Copy the .BAK file to disk somewhere
* Create an new (empty) database with the same name as the original DB
* Right mouse click the database and select RESTORE
* Restore Database, Restore from Device and then on the Options tab select OVERRIDE
* Click OK to restore the database and voila, you have a copy of the database on your dev machine

Works great almost every time, except this time. I began receiving errors about the sysft_mycatelog file which has to be a full text catalog. This database did not have a full text catalog when I restored it in the past and I'm not sure where this one came from but philosophical musings about the possible origin the the file aside, I had to deal with this error and I did not have any 'sysft_mycatelog' file to refer to. The error that I first received was:

TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server 'BLAHBLAH'.  (Microsoft.SqlServer.Smo)

System.Data.SqlClient.SqlError: The path 'L:\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\mycatalog' has invalid attributes. It needs to be a directory. It must not be hidden, read-only, or on a removable drive. (Microsoft.SqlServer.Smo)
------------------------------

Well, I don't have an L drive so then I got the brain-wave to simply create the directory it was looking for in the MSSQL dir which on my machine is in the directory "C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\mycatalog"

I know, so smart, but then I got this error:

TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server 'BLAHBLAH'.  (Microsoft.SqlServer.Smo)

System.Data.SqlClient.SqlError: The operating system returned the error '3(The system cannot find the path specified.)' while attempting 'CreateFile' on 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\mycatalog\SQL.HDR'. (Microsoft.SqlServer.Smo)
------------------------------

sigh, so then I drank a beer and wallowed in my own annoyance when suddenly I remembered something my mentor Ken Granderson (my personal Yoda) said: 'Luke (he didn't call me Luke) when all else fails, it is probably a permissions issue.' So I looked at the permissions in this FTData directory that I had created and I compared that to the permissions in the existing DATA directory and lo and behold. The user MSSQL2005MSSQLUSER$machinename$MSSQL2005MSSQLUSER had full control over the data directory but only partial control over my newly created FTData directory. So I gave it full control and Voila! I was able to restore my database.

Hoping this saves someone else hours of annoyance.

Code Forth,
Martin Welch