Database Recovery in SQL 2005

Question

Hi,

ALTER DATABASE Database_Name SET EMERGENCY (Successfully).
ALTER DATABASE Database_Name SET SINGLE_USER (Successfully).
DBCC CHECKDB('Database_Name', REPAIR_ALLOW_DATA_LOSS) (Failed).

So obviously i couldn't continue with the remain commands:

ALTER DATABASE Database_Name SET ONLINE
ALTER DATABASE [Database_Name] SET MULTI_USER WITH NO_WAIT

The error message i get for that command is:

Msg 922, Level 14, State 1, Line 1
Database 'Database_Name' is being recovered. Waiting until recovery is finished.

I have waited for almost a day, and when i came next day i was unable to continue with the other commands (Below). I think its getting stuck and not recover anything.

Now what to do to resolve this issue. This is my test server.

Answer 1

Hi,

When you say that the DBCC CHECKDB failed, what were the errors?

Any backups of this database???

---

Thanks,

Andrew Bainbridge
SQL Server DBA

Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

Answer 2

Hello

Please post the errors you got from DBCC CHECKDB

---

Javier Villegas | @javier_vill | http://sql-javier-villegas.blogspot.com/

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 3

Hi,

DBCC CHECKDB('Database_Name', REPAIR_ALLOW_DATA_LOSS)

While executing above command i got below error:

Msg 922, Level 14, State 1, Line 1
Database 'AdventureWorks' is being recovered. Waiting until recovery is finished.

Answer 4

Hi,

DBCC CHECKDB('Database_Name', REPAIR_ALLOW_DATA_LOSS)

While executing above command i got below error:

Msg 922, Level 14, State 1, Line 1
Database 'AdventureWorks' is being recovered. Waiting until recovery is finished.

I having backup.

Answer 5

How big's the database?  have you tried RESTORE DATABASE mydb WITH RECOVERY?

---

Thanks,

Andrew Bainbridge
SQL Server DBA

Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

Answer 6

Hi ,

Size: 73 MB

Getting below error while restoring.

Msg 3154, Level 16, State 4, Line 2
The backup set holds a backup of a database other than the existing 'AdventureWorks' database.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

Answer 7

are you now trying to restore your database from backup?  Try "WITH REPLACE", or select the Overwrite database option in SSMS

---

Thanks,

Andrew Bainbridge
SQL Server DBA

Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

Answer 8

Vijay,

When your database is in recovery DBCC commands will not help, you must recover your db with RECOVERY first. REPAIR ALLOW_DATA_LOSS must be your last option! There are different scenarios for database corruptions with solutions for each.

Can you explain what you want to achieve?

thanks,

Janos 

---

There are 10 type of people. Those who understand binary and those who do not.
My Blog

Answer 9

Here is an Example with Replace for the below mentioned Issue

Msg 3154, Level 16, State 4, Line 2
The backup set holds a backup of a database other than the existing 'AdventureWorks' database.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

RESTORE DATABASE [Database_Name] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Database_Name.bak' WITH  FILE = 1,  NOUNLOAD,  REPLA

Or Simply

RESTORE DATABASE DB_Name FROM DISK = ‘C:\DBNAME.bak’  WITH REPLACE 

TO Check the backup File list --

RESTORE FILELISTONLY FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Database_Name.bak'

Hope It will be Helpful

Praveen

Answer 10

Hello Vijay,

I think backup is the second last option to recover your database. Have you maintained an updated backup for your database? If YES then restore database from backup otherwise try any 3rd party Microsoft gold satisfied partenr's SQL recovery product. I have seen that some of the 3rd party sql recovery utilities word well even DBCC CHECKDB with repair_allow_data_loss fails to perform repair operation.   

---

Read my blog (Blog)
Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 11

Apparently REPAIR_ALLOW_DATA_LOSS couldn't repair the database, meaning that is cannot recovery. It is not a matter of waiting. You would want somebody who is experienced with these things to do this typw of work, including doing root cause analysis, checking error messages fomr when the problem happened initially and from each command (line CHECKDB) and based on all that information determine next step. That is likely a restore from a heathly backup, but there are *sometimes* other things that can be done. Without more information, we really can't do anymore. If this seems overwhealing, considering opening a case with MS support, but as already stated, restore is a likely outcome in the end.

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 12

Hi,

It's is going to be resolved if we restore from backup.

But my curiosity why it is happening? What is the root cause? I testing in Test environment.Is it non-resolvable issue?

Answer 13

Hi,

As Tibor said you may ask a more experienced person to do the RCA. Without knowing what the error was, it is really irresponsible to run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS.

You may run this command before trying to repair something which may work :)

DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS

Janos

---

There are 10 type of people. Those who understand binary and those who do not.
My Blog

Answer 14

Hi,

I have restored from backup, now everything is working fine.

Answer 15

hi,

I'm glad you made it, but it is very dangerous to do this in production in the same way. Please note that REPAIR_ALLOW_DATA_LOSS allows data loss as it states! Do not try to run this without knowing what the real error is or what error DBCC CHECKDB returned to you. 

There are some error which can be repaired by backup restores only, but note that it is not as easy as it sounds. 

Janos

---

There are 10 type of people. Those who understand binary and those who do not.
My Blog