Saturday, January 3, 2009

How to repair a SUSPECT SQL database

Firstly a very Happy and a Prosperous new year to everyone!

As usual I was doing things on my VPC and I noticed that one of my SQL database was marked suspect. After some thought I guessed that the reason for the database to be marked suspect is because of me turning off my VPC without proper shutdown. The same reason why a DB can be marked suspect after a power failure. Now I was in a fix because I didn't had backup (Another dumb thing to miss on my part). So what should I do now? Well some research brought me good results, ended up framing following query:

EXEC sp_resetstatus ‘DBname’;
ALTER DATABASE DBname SET EMERGENCY
DBCC checkdb(’DBname’)
ALTER DATABASE DBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (’DBname’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE DBname SET MULTI_USER

So all you have to do is copy and paste above query in query analyzer and replace DBName with your database name. And you are good to go now!

11 comments:

Alex said...

In this situation advise try-repair sql database,software helped me many times,as far as i know it is free,utility repair data from corrupted databases in the MS SQL Server format (files with the *.mdf extension),supports data extraction via the local area network,can save recovered data as SQL scripts, it is also possible to split data into files of any size,compatible with all supported versions of Microsoft Windows, such as Windows 98, Windows Me, Windows NT 4.0, Windows 2000, Windows XP, Windows XP SP2, Windows 2003 Server, Windows Vista,tool supports the following database formats: Microsoft SQL Server 7.0, 2000, 2005,also can repair .mdf files of Microsoft SQL Server 2005, repair mdf file of Microsoft SQL Server 2005 (64-bit).

Kunal said...

Hi Alex,

Thanks for sharing this tool. My solution was one of the quick fix. This tool is definitely a good asset.

Thanks,
Kunal

Anonymous said...

Know good tool which recover data also it restore sql files too-repair sql server database,it is free as how as i remember,utility repair data from corrupted databases in the MS SQL Server format (files with the *.mdf extension),supports data extraction via the local area network,can save recovered data as SQL scripts, it is also possible to split data into files of any size,compatible with all supported versions of Microsoft Windows, such as Windows 98, Windows Me, Windows NT 4.0, Windows 2000, Windows XP, Windows XP SP2, Windows 2003 Server, Windows Vista,tool supports the following database formats: Microsoft SQL Server 7.0, 2000, 2005,also can repair .mdf files of Microsoft SQL Server 2005, repair mdf file of Microsoft SQL Server 2005 (64-bit).

repair access said...

A good SQL database utility scans your corrupted database and then drags the data from it. Some times this may be possible that whole of the database is not corrupted apart from its few objects. So while having the software, you need to first check out whether this can perform MDF repair for them or not.

Achiles said...

Hi,Kunal

I have read Solution for this matter, but i didn't know database which run scripts. Could u help me, please?

datdp
achilesitpro@gmail.com

Alexis said...

In my opinion there is unbeaten tool,which recover sql files-mssql repair database 2005,because it has many probabilities and as far as I know has free status,program can help with this problem and retrieve the data, that was considered to be lost,mwill extract housekeeping data from the source database and preview the data, that can be recovered,this tool is a good solution to recover data from corrupted databases in MS SQL Server format,restore databases represent files, like any other documents, they can be easily corrupted by viruses, all sorts of malware, hard drive failures, file system errors, incorrect user actions, etc,supports both data extraction to your hard drive as scripts in SQL format and data export directly to a database in MS SQL Server format.

Anonymous said...

All in all in this situation there are many ways of solution this task,and in my opinion one of the best-sql recovery,software has free status as far as I know,it can help with this problem and retrieve the data, that was considered to be lost,mwill extract housekeeping data from the source database and preview the data, that can be recovered,this tool is a good solution to recover data from corrupted databases in MS SQL Server format,restore databases represent files, like any other documents, they can be easily corrupted by viruses, all sorts of malware, hard drive failures, file system errors, incorrect user actions, etc,supports both data extraction to your hard drive as scripts in SQL format and data export directly to a database in MS SQL Server format.

Anonymous said...

anyone could help me to share rapair sql database program please...(www.recoverytoolbox.com)

please email me dindinudin@yahoo.com

Alex said...

When I was in serious situation for me helped-recover sql database,because my sql files was corrupted,this tool is free as is known,and utility repair data from corrupted databases in the MS SQL Server format (files with the *.mdf extension),supports data extraction via the local area network,can save recovered data as SQL scripts.

Sam Joseph said...

SQL Recovery Software is an easy to use application designed to recover data from lost or heavily damaged .mdf files that might have got deleted or corrupted on the event of a virus attack, unexpected system shutdown, media read error etc.

David said...

You can repair your sql database with the hekp of sql recovery software. This software recover lost data, tables, views, stored procedures, rules, defaults, user defined data types and triggers from Microsoft SQL server database.