CODEWITHSUNDEEP
filestreamfilegroup
Larry R. Pettit
Larry R. Pettit

Reputation: 1

SQL Server 2022 Filestream filegroup file deleted

The empty filestream filegroup physical file got deleted while playing with the FileStream feature on a database. Now the database is in Emergency mode. It appears as though there is no recent backup. Yes, I know I'm an idiot for at least two reasons.

dbcc checkdb (dbname, repair_allow_data_loss) gives following error: Unable to open the physical file "C:\help\db_Help". Operating system error 2: "2(The system cannot find the file specified.)". The file is not in the recycle bin.

I don't need this FileStream filegroup. There is no data in it (certainly none I care about). All alter database commands fail giving the error that the database cannot be opened due to inaccessible files.

Is there any way to tell SQL Server to ignore this filegroup when opening database? Is there anyway to copy mdf and ldf and restore to a point in time (last night for example)? Any other suggestions?

Upvotes: 0

Views: 47

Answers (1)

Gary Futch
Gary Futch

Reputation: 1

No, SQL Server does not allow you to directly ignore a filegroup when opening a database, nor can you simply copy MDF and LDF files to restore to a specific point in time.

You can resolve both issues by taking the following steps:

Handling the Missing Filestream Filegroup: SQL Server requires all filegroups to be present for the database to function normally. Since the filestream filegroup is missing:

Set the database to Emergency mode:

ALTER DATABASE YourDatabaseName SET EMERGENCY;

Use DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS option to remove the damaged filegroup:

DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS);

This will repair the database but may result in data loss, especially related to the missing filegroup. Restoring to a Point in Time: You cannot restore to a specific point in time by merely copying MDF and LDF files. Instead, you need a backup and log files. If backups are available:

Restoring to a Point in Time: You cannot restore to a specific point in time by merely copying MDF and LDF files. Instead, you need a backup and log files. If backups are available:

RESTORE DATABASE YourDatabaseName FROM DISK = 'PathToBackupFile.bak' WITH NORECOVERY;

Now apply transaction logs to a specific point in time:

RESTORE LOG YourDatabaseName FROM DISK = 'PathToLogFile.trn' WITH STOPAT = 'YYYY-MM-DD HH:MM:SS', RECOVERY;

If No Backup Exists: If backups are unavailable and only the MDF and LDF files exist:

CREATE DATABASE YourDatabaseName ON 
(FILENAME = 'PathToMDFFile.mdf'),
(FILENAME = 'PathToLDFFile.ldf')
FOR ATTACH;

But I would like to tell you this works only if the files are undamaged. To avoid such issues in the future, maintain regular backups and a reliable recovery strategy.

Upvotes: 0

Related Questions