How-to-Resolve-SQL-Error-7904

SQL Administrators maintain a healthy, updated, and a restorable backup copy of the database, and if this database gets corrupt, then it is restored using a backup. But, there are instances of corruption in SQL database while restoring MDF files (primary database files) where the restored database shows an error message:

Table error: The Filestream file for column ID C_ID, ROWGUID RG_ID in object ID O_ID, index ID I_ID, partition ID PN_ID was not found.

Why does the Error 7904 occur?

The error is caused even if the backup copy and the Transaction Logs of SQL database from which the backup copy was created are healthy. Hence, the reason for the occurrence of Error 7904 is corruption in Filestream of SQL Server 2008 database, which leads to inaccessible MDF files. Due to this reason, Error 7904 is also termed as SQL Filestream Corruption.

Filestream is a new database storage system which manages the unstructured data vis-à-vis audio, video, images, etc. directly into the NTFS file system. The Filestream influences the Application Programming Interface or API and maintains transactional uniformity between the structured and unstructured data. When the ‘Filestream error’ strikes, the data is stored outside the NTFS file system, causing the Filestream Error.

A sequence of how the Filestream Error Strikes

When the files get skipped during the restoration of Transaction Logs via Log backup, then it results in SQL server error 7904. The following Flowchart describes the error sequence:

A-sequence of-how-the-Filestream-Error-Strikes3

What should be the User Action?

The following three methods can help resolve the Error 7904 or Filestream error

  1. Restore from Backup
  2. Check for the Hardware Failure
  3. Repair SQL Database

1. Restore from Backup

If the Transaction Logs display an error, then chances are that the root cause of an error is deep-seated in the backup copy of the SQL Server database. However, this may not be entirely correct. Hence, deleting the restored database, analyzing the backup copy from error perspective, and restoring the SQL database from a clean backup is one of the solutions.

The issue in the backup copy will again result in the Filestream error and should be avoided.

2. Check for Hardware Failure

Hardware Diagnostics are used to correct the hardware-related issues. Run Hardware Diagnostics and also inspect Microsoft Windows system, Application Logs, and SQL Server error logs to analyze whether the error occurred due to hardware failure. If the result is positive, then fix the hardware-related issues contained in those Logs.

Sometimes, the SQL administrators experience persistent issues of data corruption. In that case, swap the different components of hardware components to isolate the problem, one after another.

Also, check that the write-caching option is not enabled on the disk controller. If it is activated or suspected to be the cause of Filestream error, then contact the hardware vendor to resolve the issue.

Hardware problems may also conclude in replacing the hardware, but that is a lengthy process involving reformatting of disk-drives, installation of operating system and restoring from a database backup. And all this involves considerable downtime.

Such downtime is generally not the preferred choice and running DBCC CHECKDB will not help repair the SQL Server Error7904, as included in the reference section of this article.

3. Repair SQL Database

The Microsoft suggested utilities cannot repair the error, but software like Stellar Repair for SQL can fix the Filestream error in Transaction Logs of MDF files and reinstate the SQL database.

The software scans the SQL database, detects the mangled entries, corrects the inconsistencies in the MDF and NDF files and brings the database to a consistent state.

Repairing with SQL database software is a preferred option due to the following reasons:

• The software does repair SQL database
• The ‘Preview’ option of the software helps Administrators to verify whether that the SQL Error 7904 is resolved after the database is repaired.
• The software helps in selective recovery of MS SQL database. This helps Admins to restore only the error-affected section of the database, and save the time involved in recovering the entire database.

Conclusion

SQL Administrators take all precautionary measures to keep the SQL server database up and running, but there are errors which cannot be resolved manually. One of these errors is Filestream error or SQL database error 7904, which occurs when the Admin tries to restore the database from a clean backup.

When the error strikes then the situation is: the original database is corrupt, the restored database is also corrupt with no chance of re-restoring the database from the available backup. Secondly, the DBCC CHECKDB option may not be effective enough, as repairing the database and replacing the current hardware with a new one will also result in Filestream error.

In this case, when the entire list of “Help” options is rendered ineffective, there is only one way to resolve the issue – install a competent SQL database repair software, which provides an effective alternative to reinstate database accessibility by fixing the error and repairing SQL database.

References:
https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-7904-database-engine-error?view=sql-server-2017
https://www.stellarinfo.com/sql-recovery.php
https://community.spiceworks.com/topic/751250-how-to-recover-sql-server-database-from-corrupt-mdf-file

Leave a Reply

Your email address will not be published. Required fields are marked *