Discussing ways to easily recover SQL Server 2000 suspect database

-->

MS SQL Server 2000 is widely used all over the world as the data storage component of several large websites. It is a cross-platform application that supports various advanced and user-friendly features, such as Microsoft search service, federated servers, indexed views, etc. It can help you achieve the desired performance levels required for running large web applications. However, an SQL database may occasionally become corrupt. The potential reasons responsible for this corruption are virus infection, hardware errors, abnormal system shutdown, etc. As a consequence, all valuable objects in the database become inaccessible. To overcome this problem, you need to go for SQL recovery through a reliable third-party utility.

Suppose, you use MS SQL Server 2000. While trying to access one of your databases, you may receive the following error message on your screen:

“96/11/18 10:48:32.60 kernel udopen: Operating System Error 32 (The process cannot access the file because it is being used by another process.) during the creation/opening of physical device,
C:\DATA\SQL\MSDB.DAT (or C:\DATA\SQL\MSDB.MDF)”

Cause: The above error occurs because you are trying to access a database that is marked as 'suspect'. There could be a number of reasons responsible for this, such as database corruption, missing device driver, database being moved to another location, etc.

This would render your SQL database inaccessible and lead to severe data loss. At this stage, you may use an updated backup to restore the database. However, if the backup is unavailable, you should follow the below mentioned resolution steps.  

Resolution: You can try to resolve the issue by following any of the given methods:

• Make sure that your system has all the necessary device driver files.
• If your SQL database is corrupt, you need to follow the given procedure.

• First, drop and recreate all indexes in your database.
• Create a new database table and copy all records from the original table to this newly created table.
• Now, update the statistics.
• Run the following command:

'DBCC UPDATEUSAGE'.

• Finally, run the 'sp_recompile' procedure

If the problem still persists, you should take help from a competent SQL server recovery tool. These third-party software are proficient in repairing corrupt SQL databases to recover valuable objects, including tables, views, indexes, constraints, triggers, stored procedures, etc. They allow you to take a sneak peek at the database objects prior to recovery.