Database is in recovery status.

Question

Database is in recovery status.

August 11, 2015 at 11:31 pm

This was removed by the editor as SPAM

Viewing 8 posts - 31 through 37 (of 37 total)

You must be logged in to reply to this topic. Login to reply

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 1

Nitin Gupta - SQL DBA (3/26/2010)
Might be it's not work in this case but I have done this previously as I got my database is in recovery mode. I just stop the SQL services, rename the data files (.mdf & .ldf), drop the existing database and attached the data files with same database name simple.
after that I run DBCC Checkdb to verify database health and I found everything are looks good without any error. 😀

Nitin

This is not correct solution.first you have to find the root cause or issue.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Nitin Gupta - SQL DBA Ten Centuries Points: 1058 More actions · Answer 2

Yes, its right very first step is find out the problem root cause but if you not have any logs and you are unable to find out the RC, wt is the next step you have to follow.

If you have any better solution please share.

Thanks & Regards,

Nitin Gupta
SQl Server (7.0,2000,2005) - Sr. DBA
HCL Technologies, India

dbarenjith SSC Journeyman Points: 99 More actions · Answer 3

Usually checking the errorlog will show you the percentage of progress.

exec xp_readerrorlog

Elliswhite SSCommitted Points: 1783 More actions · Answer 4

Go through this way

1.Right click on the database in SQL Server Management Studio, and hit Detach.

2.Select the Drop Connections checkbox only, and hit ok.

3.Then the database will disappear.

4.Right click on the Databases folder, and click Attach...

5.Click Add and find the .mdf file for the database you deleted. You will have to find it in the file system.

6.Click Ok, and it will be back online.

SSMS Expert

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Elliswhite (5/5/2014)
Go through this way
1.Right click on the database in SQL Server Management Studio, and hit Detach.
2.Select the Drop Connections checkbox only, and hit ok.
3.Then the database will disappear.
4.Right click on the Databases folder, and click Attach...
5.Click Add and find the .mdf file for the database you deleted. You will have to find it in the file system.
6.Click Ok, and it will be back online.

Four year old thread, and no, it won't be back online. It will have to go through the recovery process from the beginning. If the recovery process can't start, then the attach will fail.

Detaching a DB in any state other than ONLINE is a huge risk, not something to be recommended without major caveats.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

akhamoshioke Ten Centuries Points: 1110 More actions · Answer 6

Thanks Gail.

Hope this helps;

Run this script to show you how long it will take to become online again for versions 2005 - 2008R2

DECLARE @DBName VARCHAR(64) = 'databasename' -- Change this to the name of the database in recovery

DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))

INSERT INTO @ErrorLog

EXEC sys.xp_readerrorlog 0, 1, 'Recovery of database', @DBName

SELECT TOP 5

[LogDate]

,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete

,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining

,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0 AS HoursRemaining

,[TEXT]

FROM @ErrorLog ORDER BY [LogDate] DESC

For 2012, SQL uses a different stored procedure to read the error log: