Tuesday, August 23, 2011

Restore from Recovery Failure Error 3414

MS SQL server database gets corrupt or damaged due to several reasons like power failure, hardware failure, human errors, virus attack, and so on. Microsoft offers several inbuilt utility to handle all above type of corruptions in the SQL server database like DBCC CHECKDB and many more.

Consider a practical scenario, wherein you are recovering SQL server database using inbuilt feature of MS SQL but fails with the below error message:

Error: 3414, Severity: 21, State: 1.
An error occurred during recovery, preventing the database 'mydb' (database ID 13) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.

Any attempt made on the database put SQL server database in suspect mode. You can see the status of database from the sql server management studio or sys.databases.state_desc column. If you will do any action of this suspect database then you may got another error message.

Msg 926, Level 14, State 1, Line 1
Database 'mydb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information

Reason for Failure: The main reason for recovery failure is an error that proceeds error 3414.

Possible Solutions: You have three possible solutions for handling such type of error message, those are given below:
  1. Restore from clean backup
  2. Run DBCC CHECKDB
  3. Third party recovery software
Restore from Clean Backup: It is the best solution to resolve above error message but in case you don't have any backup then try second option.
Run DBCC CHECKDB: Use emergency repair method offered by DBCC CHECKDB.

EXEC sp_resetstatus 'yourDBname';
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb('yourDBname')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER

Third Party recovery Software: When you will use DBCC CHECKDB command then you may lost some amount of data. If you want your complete database then try any third party SQL recovery software. Third parties SQL server recovery software repair corrupted SQL server database in just few clicks.

No comments:

Post a Comment