System Table corruption renders to “All system tables must be in....” Error

Whenever a DBA creates any MS SQL object (be it a table, view, report, form, etc.), its properties (metadata) are saved in System Tables. These system tables are primarily used to discover differences and similarities between tables and columns at the time of database upgrade, view performance statistics, and collect lock information. These tables are hidden and even with full DBA access rights, remain restricted. (This is because if a DBA changes the location of any of these tables then the database becomes unmountable). While nothing can change the location of system tables, logical damage in MS SQL Server database can. In such cases, the DBA needs to perform restoration from an updated online or offline backup. But, if in case the database backup is unavailable, then the DBA can execute DBCC CHECKDB command to repair the database. The command, while successful in most cases, can fail under many situations, like severe database damage. For such scenarios, the DBA needs to opt for an effective MDF recovery application.

To elaborate the above case, consider the below error message that appears when a MS SQL Server DBA attempts to mount his/her database:

“System table 'OBJNAME' (object ID O_ID, index ID I_ID) is in filegroup FG_ID1. All system tables must be in filegroup FG_ID2. ”

Cause:

The above error message appears because the location of the system tables has been changed due to logical or damage in MS SQL Server database. Few causes for logical damage in MS SQL database are human mistakes, virus hits, unexpected system shutdown, and more.

Resolution:

To resolve the above database corruption error message, a DBA needs to follow the below steps:

· Perform database restoration from a current backup.

· In case of unavailability or inaccessibility of current backup, then execute DBCC CHECKDB command with suitable repair clause.

· In case the repair command also fails, then use third-party MDF recovery tool to repair corrupted MS SQL database.

MS SQL Recovery Software Information:

A commercial MDF recovery tool repairs corrupt database irrespective of its size. Such MDF file repair applications are easily understandable and can be used by any user. The database repair tools are safe and perform repair on the copy of the database. Most of these MDF file recovery tools come with demo versions that allow the user to preview its repaired database.

Applied for:

• MS SQL Server 2000
• MS SQL Server 2005
• MS SQL Server 2008