2

According to https://msdn.microsoft.com/en-us/library/ms173778.aspx, there are four types, L, D, F and S.

All of the actual database instances I've seen so far (SQL Server 2008 and up) only have types L and D. I've written a script to backup and restore databases very quickly (there are a wealth of answers on Stack Overflow with instructions) but I'm wondering if I'll ever need to worry about the other two types: F and S.

I've read that full-text catalogues (F) are virtual from SQL Server 2008 onwards, so it seems like I will never encounter those. The documentation says that S can be an in-memory OLTP container, but I'm not sure if FileStream and FileTable are also virtual or not.

0

In SQL Server, the full-text catalog is a logical concept and does not reside in a filegroup. Therefore, to back up a full-text catalog in SQL Server, you must identify every filegroup that contains a full-text index that belongs to the catalog. Then you must back up those filegroups, one by one.

When a full-text index is created, it is placed in one of the following locations: A user-specified filegroup. The same filegroup as base table or view, for a nonpartitioned table. The primary filegroup, for a partitioned table.

Source

If the database has full text catalog, you'll need to backup the filegroups pertaining to it. It is possible that there is a filegroup only containing the index for the full text catalog and nothing else, as seen by "A user-specified filegroup".

If a full-text index resides in a separate filegroup from all of the associated table data, the behavior of piecemeal restore depends on which of the filegroups is restored and brought online first:

Source

Now for filestream, you can see an example of backups and restores here: MSSQL

You can see that the filestream backup data is classified as type S as you talked about. It is also seen in SQL as FILESTREAM type and is it's own file to be restored.

To recap, you may run into F and S type in the filelist. S type seems more likely as it's possible the full text indices reside in a filegroup with tables or other data and would be seen as D. You can specify your own filegroup for full text indices that contain just the indices however.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.