The FILESTREAM feature in SQL Server allows you to store Binary Large Objects (BLOBs) with a varbinary (max) column externally onto the file system instead of storing them inside a database. However, this feature is not enabled by default. You need to enable it by using the SQL Server Management Studio (SSMS) and SQL Server Configuration Manager. When this feature is enabled, the database is stored in the FILESTREAM file groups. Once it is stored in a FILESTREAM column, you can access the files by using T-SQL transactions.
Sometimes, modifying or deleting the files in the FILESTREAM folder or saving a file to the SQL Server database can result in inconsistencies or corruption in the SQL Server database. If the FILESTREAM filegroup is corrupted, then your database will go into the ‘recovery pending’ state. In this blog, we’ll discuss the causes that can lead to corruption in SQL Server FILESTREAM database and see how to resolve corruption and recover the database.
Causes of Corruption in FILESTREAM-enabled Database in SQL Server
The FILESTREAM-enabled database can get corrupted due to various reasons. Some common ones are:
- FILESTREAM stores file references and metadata in the database. If the SQL database itself gets corrupted due to hardware failure, disk issues etc., the references linking the file to the database also become inconsistent.
- The actual data in FILESTREAM is stored on the file system. This data can get corrupt due to bad sectors, disk failure, and other issues.
- If you directly delete or modify the files in the file system folder inside the data containers, it can cause inconsistencies between the file system and the SQL Server, leading to corruption in the FILESTREAM data.
How to Detect Corruption in FILESTREAM-enabled Database in SQL Server?
You can use the DBCC CHECKDB command to detect corruption or physical and logical consistency issues in the FILESTREAM-enabled SQL database. It also validates link-level consistency between metadata of the table and the files and directories of the FILESTREAM file system. Here’s how to check the corruption inthefilestream-enabled database using the DBCC CHECKDB command:
DBCC CHECKDB ‘database_name’;The DBCC CHECKDB command can report errors, like 7903, 7904, 7905, 7907, etc. Such errors indicate corruption, inconsistencies, or integrity issues that occur due to tampering with the SQL Server FILESTREAM folder.
Methods to Fix Corruption in SQL Server FILESTREAM-enabled Database
If the FILESTREAM-enabled SQL database is corrupted, damaged or inaccessible, you can follow the below methods.
Method 1: Restore from Filegroup Backup
Filestream data is stored in FILESTREAM filegroup. It contains the file system directories known as data containers. If you have files and filegroup backup in SQL Server, you can easily restore the database from the backup. Before that, ensure that the backup file contains the FILESTREAM data using the RESTORE FILELISTONLY command. This command displays all the files in the backup.
Here are the steps to restore database from the backup in the SQL Server:
Note: First, read the limitations and restrictions for restoring files and filegroups backup. Also, make sure you have RESTORE permissions.
- Open SSMS and then connect to the appropriate instance of the SQL Server Database Engine in ‘Object Explorer’.
- Expand the server tree by clicking on the server name.
- Next, expand ‘Databases’ tab. Now, either choose a user database or expand the ‘System Database’ accordingly.
- Click on System Database, right-click on the database, click Tasks > Restore > Files and Filegroups.
- Next, specify the source and location of the backup sets by choosing the option ‘From database or From device’.
- In the ‘Select the backup sets to restore’ grid, choose the backups you want to restore. The grid displays all the backups available at the selected location.
- Click the ‘Options’ page below the ‘Select a Page’ on the “Restore Files and Filegroups’ dialog box to select the advanced options.
- In the Restore options panel, select one of the options:
- Restore as file group
- Overwrite the existing database
- Prompt before restoring each backup
- Restrict access to the restored database
- Click OK.
This will restore the database. You can locate the physical folder of the FILESTREAM group using the below command:
SQL
SELECT name, physical_name, state_desc, type_desc
FROM sys.database_files
WHERE type_desc = 'FILESTREAM'Alternatively, you can use Transact-SQL commands to restore the files and filegroups backup.
Method 2: Use DBCC CHECKDB Command
If the backup is obsolete or not available, you can repair database using the DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS repair option. Here are the steps:
- First, set the database to SINGLE_USER mode using the below command:
ALTER DATABASE Dbtesting SET SINGLE_USER- Next, repair the database using the DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS as given below:
DBCC CHECKDB (N ’Dbtesting’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO- Now, set the database to MULTI_USER mode by executing the following command:
ALTER DATABASE Dbtesting SET MULTI_USERAlthough the above DBCC CHECKDB command can fix corruption-related issues in the FILESTREAM-enabled database, it has certain limitations. It may leave your database in a logically inconsistent state. While repairing complex errors, you need to use the command multiple times, which is a time-consuming process. Furthermore, it might also deallocate rows or pages while repairing the database, resulting in loss of data.
Method 3: Use a Professional SQL Repair Tool
To repair the database file with precision and integrity, you can use Stellar Repair for MS SQL – a specialized SQL repair tool that can quickly repair MDF files, even with higher levels of corruption. After repairing, the tool saves the recovered data in a new MS SQL database file.
The tool helps in resolving all corruption-related errors in the SQL database reported by the DBCC CHECKDB command. It has an intuitive user interface that allows users to perform the repair process with ease.
Key Features:
- Repairs both MDF and NDF database files without any data loss
- Recovers all database components, including deleted tables, keys, indexes, triggers, and stored procedures
- Supports SQL Server FILESTREAM data types, XML data types, SQL Server sparse columns, XML indexes, and SQL Server columns set property
- Recovers SQL tables with PAGE and ROW compression
- Offers preview of recoverable database objects before saving
- Supports selective recovery of database objects
- Saves the repaired database to a new or live database and formats like CSV, HTML, and XLS
- Supports SQL Server database 2022, 2019, and lower versions
- Compatible with both Windows and Linux operating systems
To Conclude
Corruption in FILESTREAM-enabled databases can occur due to many reasons. We have discussed above how to repair corrupted FILESTREAM-enabled database in SQL Server. You can easily restore database from the backup. But if your backup is not accessible, then you can use the DBCC CHECKDB command to repair the database. However, this method would require your time and effort and also doesn’t guarantee complete database recovery. So, for a seamless recovery process, use a professional SQL repair tool, like Stellar Repair for MS SQL.
