How to Recover SQL Server FILESTREAM Enabled Database?
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 storing the var binary(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 ‘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 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 to complete database backup. However, you can also try restoring files and filegroup backup.
In ‘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 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 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 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
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:
|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:
|ALTER DATABASE FileStreamDemoDB_test SET MULTI_USER
You will now be able to access your database.
Method 3 – Use SQL Recovery Tool
If none of the above method works for you, try using a SQL recovery tool such as Stellar Repair for MS SQL to restore FILESTREAM database. Such a tool helps you select a corrupt database file (.mdf/.ndf), repair it, and recover all the data types.
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 backup, run DBCC CHECKDB command with the repair option. But if nothing works, using a SQL recovery tool can come in handy.