How to Repair Suspect Database in MSSQL

Published by Jacob Martin on June 22, 2015

MS SQL Server is a relational database management system that is manufactured by Microsoft. SQL stands for Standard Query Language. It’s used for communicating with a database. It’s a standard language for relational database management system. SQL is used for performing tasks such as update data on database, retrieve data from database. Few common Relational Database Management System that use SQL are: Oracle, Sybase, MS SQL Server, Ingres, Access, etc.

Causes:

• The database could have become corrupted.
• There is not enough space available for the SQL Server to recover the database during startup.
• The database cannot be opened due to inaccessible files or insufficient memory or disk space.
• The database files are being held by OS, third party backup software etc.
• Due to an unexpected SQL Server Shutdown, power failure or a hardware failure.

Resolution:

In order to go for these steps it necessary for you to have MS SQL Server Management Studio installed on your PC.

Note: Before making any change to the database you must be ready with a back-up if anything goes wrong. Customers in shared hosting can do this through Control Panel.

Follow the steps below;

1. Firstly open MS SQL Server Management Studio and connect it to your database.
2. Click on the new Query button.
3. Paste the following SQL Script into your new Query page replacing (Your Databse) with the name of your database.

EXEC sp_resetstatus [YourDatabase];

ALTER DATABASE [YourDatabase] SET EMERGENCY

DBCC checkdb([YourDatabase])

ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ([YourDatabase], REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE [YourDatabase] SET MULTI_USER

1. After finishing click Execute

Your database should no longer be seen as suspect, and must be easily accessible.

Furthermore, if the error isn’t solved yet then simply go a third party repair tool that will help in overcoming all the errors and issues regarding MS SQL Server. And one such all in one tool is MS SQL Database (MDF) Repair.

SQL database objects that can be repaired:

• Tables
• Views
• Stored procedures
• Indexes
• Primary and Foreign keys
• Unique keys
• System information
• Triggers
• User defined types
• Defaults and Checked constrains
• Partition functions and Partition schemes

SQL Server 2008-2012 file stream data types

Computed and Sparse columns

Features of SQL Database (MDF) Repair Software:

• Repair Corrupt SQL Server Database – It effectively scans the database and repairs database corruption to bring it back to the consistent state.
• Restores Objects of MDF & NDF Files – It repairs both the primary and secondary data files of SQL Server i.e. MDF and NDF files and restores them.
• Repair All Database Components – Repair and recovers all the components of SQL database including tables, triggers, rules, views, indexes, stored procedures, etc.
• Resolves Several SQL Database Corruption Errors – It resolves number of corruption issues and errors like clustered index leaf level corruption, consistency corruption, schema corruption and so on.
• Recovers PAGE & ROW Compressed Data – Repair and recovers SQL tables with PAGE and ROW compression schemas to their original state.
• Previews Database Objects – Once scanning finishes, it previews all the recoverable data in a tree-like structure and the tables are listed in a sorted manner. You can easily select any of the table to see its content that you want to recover.

Steps to Repair Suspect Database in MSSQL:

Step 1: Stop the running MS SQL server. Perform the repair task on the copy of the corrupt database, click ‘ok’ to continue.

Step 2: Click on ‘Select database’ button and select the path of corrupt MDF file. You can also search your corrupt database file by using ‘Look in’ and ‘File Type’ button. Click on the ‘Scan file to start the repairing process.

Step 3: The recoverable objects of database are listed in a tree view on the left side of the window. Preview can be seen by clicking on the object.

Step 4: You can also search for a particular object by using ‘Find item’ option. Write the object name or a part of the object name in the given text box, check on ‘Match case’ or ‘Match whole word’, then click on find next button.

Step 5: Click on ‘Start Repair’ icon. A dialog box will appear fill the SQL server instance name. To save the repaired file in the desired location click on browse button and give the path, else the repaired file will be saved in the ‘Default SQL Location’. Then press ‘Ok’ button.

485 Total Views 2 Views Today