asked on

SQL Database Backup Failing - The backup of the file or filegroup "sysft_FT...." is not permitted because it is not online.

Hello

Since failing over one of our Databases in SQL Server 2016 to our secondary node and then failing it back, we have been unable to take a full backup of the database due to a sysft_FT file stuck in a restoring state.

The full error from the failed backup is:

The backup of the file or filegroup "sysft_FT....." is not permitted because it is not online. Container state: "Restoring" (8). Restore status: 8. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

When I run the following query against the database, I can see the state is stuck in RESTORING:

SELECT 
 f.name file_group, 
 d.name file_name, 
 d.state_desc file_state
FROM sys.filegroups f
JOIN sys.database_files d
 ON f.data_space_id = d.data_space_id;
GO

Open in new window

The actual full text catalog file for the database is online (ftrow_FT....). I have seen articles advising to rebuild this file, however I am looking for guidance on the correct way to approach this issue.

Thank you

Raja Jegan R

>> Since failing over one of our Databases in SQL Server 2016 to our secondary node

Seems this might be the issue..
Either your Secondary node doesn't have the folder/file structures as that of your Primary node or else the account doesn't have permissions in the secondary node.
If all the files are available, then you can try rebuilding the indexes to get it working fine and then take the Full Backup..

https://support.microsoft.com/en-au/help/923355/error-message-when-you-perform-a-full-backup-of-a-database-in-sql-serv
https://docs.microsoft.com/en-us/sql/relational-databases/search/create-and-manage-full-text-catalogs?view=sql-server-2017

sw19-it

ASKER

Hello, thanks for the response and sorry for the delay.

The folder structure and files match across both nodes and the fail over was done with the sa account. The service account has full access to the files and folders as well.

After further research I believe the sysft file is a hang up from a SQL 2005 migration, as sysft appears to be the older naming convention for full text catalog files (correct me if im wrong). It is not visibly being used under the full text catalog information on the database, however the ftrow file is and is online. Although it hasn't been indexed for a long time.

We are going to take the approach of taking the database offline, re mapping the path to sysft file then bringing the database online first. Failing this we will look at destroying and re creating the catalog to clear out this legacy file.

ALTER DATABASE DatabaseName MODIFY FILE ( NAME = sysft_file , FILENAME = 'H:\Sql Data\SysFTCatfolder')

Open in new window

Raja Jegan R

>> I believe the sysft file is a hang up from a SQL 2005 migration

If that is the case, it should have been failing in your Primary node as well and not only on your Secondary node..
Kindly check whether there are any other changes apart from this across the nodes..

>> Failing this we will look at destroying and re creating the catalog to clear out this legacy file.

Try Rebuilding the catalog instead of destroying and recreating to see whether it helps out or not..

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

SQL Database Backup Failing - The backup of the file or filegroup &quot;sysft_FT....&quot; is not permitted because it is not online.

SQL Database Backup Failing - The backup of the file or filegroup "sysft_FT...." is not permitted because it is not online.