SQL Server 2005 is stuck "in recovery" of several databases. How can I get it to just give up or finish?

I have shot myself in the foot with SQL Server 2005; it is stuck in recovery of several databases. I could solve everything OK myself, if I only knew how to get SQL Server 2005 to either:
a.      Give up and declare them unrecoverable, or
b.      Get on with finishing the recovery
I have backups of all the ones that are in recovery elsewhere that I can restore from.

To try to get it to get on with finishing up the recovery, I have tried several combinations of: Letting the SQL Server database service just run, along with the other SQL Server 2005 services either running or stopped, and either having Management Studio open or closed. Ive rebooted the system 2-3 times.

The net effect is that over about 6 hours, it went from one database in recovery because of pilot error to 5 of 20 in recovery, to 3 of 20 in recovery, and back to 4 of 5 in recovery.

The original pilot error was to 1) Create a new database named edaspdc; 2) Take it offline, but not detach it; 3) Replace its .mdf and .ldf files with the same files from my customers SQL Server 2005; 4) Try to put edaspdc online. It never did go online and it was all downhill from there.
Please see the attached screen shot for key info from my SQL Server Mgmt Studio
The largest database in recovery is GSAData with a 4 GB .mdf file and a 1 GB .ldf file. the others are much smaller.
My WinXP is at SP 3 plus various automatic updates so it's current.
001-SQLSvrDBs.gif
Douglass MacLeanCEO, CTOAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

St3veMaxCommented:
Do you have the original copies of the .mdf and .ldf files? If so, in future you dont need to create a DB, detach, then replace the files. All you need to do is put the files in the right place, right click on databases and select 'attach', point to your mdf and follow the instructions.

Bit of a longshot - what if you try 'ALTER DATABASE x WITH RECOVERY' - Does that help ?
0
Douglass MacLeanCEO, CTOAuthor Commented:
Hi St3veMax,
I'll follow your advice for the future; it's much simpler. Regarding your longshot, as usual, I'm getting confused by the Transact SQL documentation.

Here's what it says about Recovery:
<recovery_option> ::=
{
    RECOVERY { FULL | BULK_LOGGED | SIMPLE }
  | TORN_PAGE_DETECTION { ON | OFF }
  | PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }
}

Here's what I tried and it complained about incorrect syntax neer '::='

ALTER DATABASE ProLinkModel recovery_option ::= RECOVERY SIMPLE
GO

What should the syntax be?



0
St3veMaxCommented:
What you've looking at above is what recovery model to set the database to, not to recover it.

To try and bring the DB online, try:

RESTORE DATABASE ProLinkModel WITH RECOVERY

This usually works if you're been restoring a database, but stopped in the middle to restore additional transaction logs, so it will bring the DB online at that point - may work in your case, may not, but worth a shot.

HTH
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Mohed SharfiVice CEOCommented:
Hi dmaclean2,
how are you doing, please try this to begin trace
dbcc checkdb(DB_Name) with all_errormsgs, no_infomsgs
thanks
0
St3veMaxCommented:
Alternatively, if you're getting confused with the syntax, you could always right click on the DB, select properties and do things like setting the recovery model via the GUI.

HTH

Steve
0
Douglass MacLeanCEO, CTOAuthor Commented:
Dear St3v3Max and mSharfi,

Please forgive me and allow me to clarify my original question.
What I really want is for the "in recovery" status go away so I can just delete the 4 problem database for now.  How can I easily tell SQL Server to just "stop it" with trying to recover?

I've tried to Detach and to Take Offline the "in recovery" databases. Nope. SQL Server says it's too busy doing something with them  (recovering) to let me do that.

My truly important databases are OK. Three of the four that are problems are far less important. I do not have backups of them immediately available, but  can get backups or customer copies sometime in the next few days. The new one, edaspdc, I can put into the SQL Data folder and attach as St3veMax suggested.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wiljeCommented:
First, you should review the SQL Server log to see why SQL Server is putting those databases in recovery.  There should be messages for each database showing that each database is recovering - and if you don't have those, then SQL Server is not doing anything.
You can try to finish a restore and mark the restore as done using:
RESTORE DATABASE {database} WITH RECOVERY;
I don't think this will work unless you were actually trying to restore the databases.  In that case, your next option is to just delete the databases.  Right-click on the database and delete.  Once deleted, you can restore from backups.
0
Anthony PerkinsCommented:
>>Ive rebooted the system 2-3 times.<<
That is the cause of the Recovery mode.  Don't do that.  Your best bet is now to wait until it is complete.  depending on the size it may take all night.
0
Douglass MacLeanCEO, CTOAuthor Commented:
I appreciate the help from all four experts
0
Anthony PerkinsCommented:
It would be interesting to know how long it actually took and how big are the databases.
0
Douglass MacLeanCEO, CTOAuthor Commented:
The largestt database was about 6 GB. I let it run all night; no progress. Here is how I solved the problem and got back to productive work:
1. Set all the SQL Server services to Manual in Administrative Tools/Services and restarted Windows
2. Moved the .mdf and .ldf files of the 4 "in recovry" databases out of the SQL Data folder to a different folder
3. Started all the SQL Server services
4. Started SQL Server Mgmt Studio; all was well with the remaining databases
5. Reset the SQL Server services to Automatic startup
6. Found useful backups of 3 problam database elsewhere and restored them; asked the customer for a current backup of the database where I originally shot myself in the foot (edaspdc).
Problem solved.
0
Anthony PerkinsCommented:
That is good to know thanks for the feed back.  Hopefully it will help someone else in future.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.