Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Can anyone give me a complete guide to verifying/repairing a SQL Server database?

At the moment our backup is failing on the Solarwinds database saying the database NetPerfMon is corrupted (we are using Backup Exec 12.5 to perform the backups). I have SQL Server 2005 Management Studio Express installed if that helps other than that I don't have much of an idea what to do with SQL other than little bits of dabbling with it.

share|improve this question

closed as too broad by Mark Storey-Smith, Paul White, Max Vernon, ypercube, RolandoMySQLDBA Jul 15 at 15:15

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

2  
You can refer to Gail's excellent doc Help, my database is corrupt. Now what? –  Kin Jul 14 at 15:02
add comment

2 Answers 2

up vote 0 down vote accepted

i used

EXEC sp_resetstatus D0011112;

ALTER DATABASE D0011112 SET EMERGENCY

DBCC checkdb(D0011112)

ALTER DATABASE D0011112 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB (D0011112, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE D0011112 SET MULTI_USER

D0011112 is Suspected Database name

it work

There are various reasons that lead to SQL database corruptions, as in:

Accidental or intentional deletion of data and files Invalid database file header Internal Program Error(s) Crash or failure of hard drive if you want repair SQl database than use MDF Repair Kit: http://www.mdf.repair/

share|improve this answer
1  
Please dont follow this advise. You absolutely no need to run repair_allow_data_loss if you have valid backup. This method should be Last resort to recover from corruption. Above command might delete as much data as possible without giving you any information so If you love your data please dont use it. It is also Not guaranteed that it will for sure bring your database online. This post seems more like a advertisement for data recovery tool –  Shanky Jul 14 at 21:56
add comment

Please run below command in your SQL server database to check the level of corruption.If database is showing suspect it wont allow you to run this command

DBCC CHECKDB (DB_NAME) WITH ALL_ERRORMSGS,NO_INFOMSGS

This command can be used to check corruption in SQL Server database. Also I must say you are running very old version of SQL server if you are not on SP4 your SQL server is in un supported state. If above command returns any consisteny and allocation error there is corruption in SQL server database if not then issue is with backup tool you need to contact vendor in that case.

Best method to recover from corruption is to restore from latest valid backup. You can use RESTORE VERIFYONLY to check consistency of backup before restoring although only successful restore can guarantee that backup is in complete consistent state.

Your other task is to look at SQL Server errrorlog and windows event viewer to find any information related to corruption and take appropriate action. Major cause is inconsistent disk subsystem.

Restoring from valid backup would involve first restoring full backup with no recovery then differential backup(if any) with no recovery and then all log backup witn no recovery but the last log backup with recovery.I you just have full database backup you can restore it with recovery.

How to restore database using SSMS

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.