The FILESTREAM is a powerful feature in SQL Server, which is used to store binary large object (BLOB) data as files on your system’s file system. The BLOB data can be unstructured data like documents, images, GIFs, etc. If the FILESTREAM feature is enabled on the server, this file is linked to the database by storing its references. And the database gets stored in a file group.
You can easily access FILESTREAM data using Transaction-SQL statements. However, saving or updating the FILESTREAM folder can result in corruption in the SQL Server database. The FILESTREAM database in an SQL Server can get corrupted for many other reasons, such as bad sectors on the system’s disk, inconsistent references linked to the database, etc. In this article, we will discuss the methods to detect corruption in the FILESTREAM database in SQL Server and its repair methods.
How to check Corruption in FILESTREAM-enabled Databases in SQL Server?
You can easily detect the FILESTREAM-enabled database inconsistencies errors like 7903, 7905, 7904, etc., from your SQL error logs. These logs are created each time your SQL server instance is started. You can check the SQL Server error logs for the FILESTREAM-related issues by following the Windows path below:
<drive>:\Program Files\Microsoft SQL Server\MSSQL.<n>\MSSQL\LOG\ERRORLOGYou can also run the DBCC CHECKDB command to detect the FILESTREAM-based errors in SQL. The command directly reports the inconsistencies that occur due to incorrect modifications of the SQL Server FILESTREAM folder. Here is the command:
DBCC CHECKDB ‘database_name’;Effective ways to repair a corrupt FILESTREAM Database in SQL Server:
If you found that your SQL FILESTREAM database is inaccessible, corrupted, or damaged, then restore or repair it using the following methods:
Way 1: Restore your Filegroup Backup
If you have created a Filegroup Backup file and it contains the FILESTREAM data, then you can use it to restore the corrupt FILESTREAM database in SQL Server. However, before proceeding with the restore process, it is recommended that the data be checked in the backup file. This will confirm the readable FILESTREAM data in the file. To check the backup file for the FILESTREAM database, run the RESTORE FILELISTONLY command as shown below in the image:
RESTORE FILELISTONLY FROM DISK = N'D:\Internal\BackupfileRD.BAK'
GO
- In your SSMS, connect to the desired instance of the SQL Server Database Engine in ‘Object Explorer’.
- Click on the server name to expand the server tree.
- Now, expand the 'Databases’ option. Next, expand the ‘System Database’ and click on it.
- Right-click on the database, click Tasks, click Restore, and then select Files and Filegroups.
- After this, click “From database or From device” to specify the source and the backup sets' location.
- In the ‘Select the backup sets to restore’ grid, click on the backups you need to restore. It will display all the backups present at that selected location.
- Next, to select the advanced options, click the ‘Options’ page below the ‘Select a Page’ on the “Restore Files and Filegroups’ dialog box.
- Next, in the Restore options panel, select one of the appropriate options and then click OK.
Way 2: Repair FILESTREAM-enabled database using DBCC CHECKDB Command
If the file stream file is not available in a backup file or your backup file is unreadable, you can repair the database using the DBCC CHECKDB command with the REPAIR_ALLOW_DATA_LOSS repair option. The following are the steps to run this command:
- First, use the ALTER DATABASE command to change the state of the database to SINGLE_USER:
ALTER DATABASE Dbtt1 SET SINGLE_USER- Next, run the DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS to repair the database:
DBCC CHECKDB (N ’Dbtt1’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO- After this, change the database to MULTI_USER using the following command:
ALTER DATABASE Dbtesting SET MULTI_USERNote: The aforementioned DBCC CHECKDB command can help you in repairing the FILESTREAM-enabled database. However, after using this command, your database might become logically inconsistent and cause data loss. Also, repairing complex errors requires you to use the same command several times, which takes time.
Conclusion:
If the FILESTREAM-enabled database is corrupted, then you can restore the backup file to restore it. But for this, the FILESTREAM data should be available in that backup. If the backup is not available, then you can use the native DBCC CHECKDB command to repair the database. But this command can risk your important data in the database. To prevent this, you can use a professional MS SQL Repair tool like Stellar Repair for MS SQL. It can quickly repair all SQL Server databases, including FILESTREAM-enabled databases. Also, it supports SQL Server FILESTREAM data types, XML data types, spare columns, etc. The tool supports all versions of SQL Server, including MS SQL Server 2022, MS SQL Server 2019, and earlier versions.
