How To Recover SQL Server FILESTREAM Enabled Database

Introduction

Besides storing table-based data in SQL Server, you can also store Binary Large Object (BLOB) data as files using the SQL Server FILESTREAM feature. This feature allows the storing of the varbinary(max) objects onto the file system instead of placing them inside a database. However, you can create a database with a 'FILESTREAM' filegroup, allowing you to perform actions on the data stored in the file system using the database.

Sometimes, when saving files to a database using FILESTREAM in SQL Server, the files may turn corrupt. Also, modifying or deleting the files placed in the file system folder (as FILESTREAM data container) may result in consistency errors reported by DBCC CHECKDB. To fix the errors, you can try restoring the database to its original state. But if the backup is not available, repairing the FILESTREAM database using the DBCC CHECKDB command with the REPAIR_ALLOW_DATA_LOSS option may help.

This article discusses the methods to perform SQL Server FILESTREAM database recovery. It also advises an alternate solution to restore the FILESTREAM database when the backup or restore process fails to fix the issue.

Methods To Recover FILESTREAM Database in SQL Server

Following are the methods you can use to recover the FILESTREAM database in SQL Server:

Method 1: Restore Database From Backup

If you have an updated backup of your FILESTREAM database, restore the database to another location. And then, replace the problematic file in the FILESTREAM container with the file found after restoring the backup. Here’s how you can do this:

• In SSMS, locate the database you want to restore and right-click on it, and then click Tasks >Restore > Database.

Note: Here, we are explaining steps to restore a complete database backup. However, you can also try restoring files and filegroup backup.

• In the ‘Restore Database’ wizard, perform these steps:
  • Under Source, select the Device option and click the Browse (…) button next to it.
  • Click Add in Select backup devices dialog box.
  • Select the FILESTREAM database backup file (in our case FILESTREAMTestDB.bak file) and click OK.
• The path of the FILESTREAM database backup file will be added in the Backup media section. Click OK.
• The database backup file location and the database name will be displayed in the ‘Restore Database’ wizard.

• Next, click the Files tab under Select a page from the left side menu. In the window that opens, change the location of the data file and log file folders of the source database, as the restored database should be not be added into a similar location as the original DB having the same file names.

• Click OK. The database will be restored and saved in the specified location.
• Now, navigate to the location of the FILESTREAM container in SQL Server where the newly restored database is saved.
• Once you have found the corrupted or missing file by restoring the backup, you can copy the restored database file into the FILESTREAM container.

Method 2: Run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS

If the backup for the FILESTREAM database is not available or you can’t find the file in the backup, you can recover the FILESTREAM database by executing the DBCC CHECKDB command with ‘REPAIR_ALLOW_DATA_LOSS’. Doing so will fix corruption in the file stored in the FILESTREAM container.

Follow these steps to repair the corrupt FILESTREAM database:

• Put the SQL database in single-user mode by running the following command:

ALTER DATABASE FILESTREAMTestDB_test SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

GO

The above command will kill any active connection in the database and put the database in single-user mode.

• Now, try to repair the database by using the below command:

xxxxxxxxxx

DBCC CHECKDB('FILESTREAMTestDB_test', REPAIR_ALLOW_DATA_LOSS)

Running this command will delete the row containing the FILESTREAM file and fix consistency errors.

• Set the database to ‘MULTI_USER’ mode by executing the following command:

xxxxxxxxxx

ALTER DATABASE FileStreamDemoDB_test SET MULTI_USER

 GO

You will now be able to access your database.

Method 3: Use SQL Recovery Tool

If none of the above methods works for you, try using a SQL recovery tool such as Stellar Repair for MS SQL to restore the FILESTREAM database. Such a tool helps you select a corrupt database file (.mdf/.ndf), repair it, and recover all the data types.

Conclusion

If you are experiencing issues with your SQL Server FILESTREAM enabled database, such as database corruption, inaccessible files, etc. you can try the methods discussed in this post to recover the FILESTREAM database. You can try restoring the database from backup and replace the corrupt or missing file (in the FILESTREAM container) with the file you have found after restoring the backup. If you don’t have a backup, run the DBCC CHECKDB command with the repair option. But if nothing works, using a SQL recovery tool can come in handy.