Bringing a database out of Restoring state in SQL Server 2005

by Allan Svelmøe Hansen June 12, 2008 07:13

One of the many tasks of anybody who works more in depth with databases is the restoring of data.

Not being completely adept at this practice myself – mostly because it is rare that we administrate production databases – it is something I’m looking into at times.
Occasionally – I’ve seen databases being stuck in “Restoring” state. Basically without getting all to technical (because that’ll be a blog piece for another time), it is mostly because the Database is restored with the “NORECOVERY” clause, so it is ready to be rolled forward with transaction log backups.

So – a method of bringing a database back from restoring state is to run the script:
RESTORE DATABASE <DATABASE_NAME> 
WITH RECOVERY

This has worked for me so far anyways... :) 

Bookmark and Share DotnetKicks dotnetshoutout

Tags:

SQL

Permalink | Comments (9) | Post RSSRSS comment feed

Comments

10/3/2009 7:38:08 PM #

Perfect! Worked great for me! Thanks..

Raymond

Raymond Netherlands

10/7/2009 12:11:34 PM #

Kewl it worked for me as well Smile

I removed mirroring for principal db and partner db got stuck into restoring state
u gave me magic solution Smile

Sathy

sathy United Kingdom

10/7/2009 5:18:28 PM #

Glad it can help - had the issue myself and it bugged the heck out of me.

Allan S. Hansen Denmark

10/16/2009 1:08:03 AM #

No joy for me.  I inherited a failed log shipping installatin from a previous DBA and when I executed the script the following error occured.

"Msg 4333, Level 16, State 1, Line 2
The database cannot be recovered because the log was not restored."

I want to take the DB offline for a period, then archive it to offline storage and get disk space back.  Noone who currently works here knows what the database is for, or if anyone uses it.

Greg United States

12/1/2009 6:32:37 AM #

I just wanted to thank you very much for this enlightening article.  I have already bookmarked your site, when I have more free time I am going to have to do some further reading. Well back to my dreaming of <a href="blogs.law.harvard.edu/.../a> or back to the books - I wonder which one is going to win out.  Smile

Scotty from Harvard

12/24/2009 1:01:51 PM #

Good stuff.  Can I quote you on my blog?

Margarito Milnes United States

4/19/2010 4:37:43 PM #

Awesome, worked like a charm.

Sethmo United States

5/12/2010 3:27:07 PM #

Very good article. I've found your blog via Bing and I'm really happy  about the information you provide in your articles. Btw your blogs layout is really messed up on the Chrome browser. Would be cool if you could fix that. Anyhow keep up the good work!

Joseph Cargill United States

7/13/2010 4:41:52 PM #

That worked for me. Thanks for the tip

Alexis United States

Add comment




  Country flag

biuquote
  • Comment
  • Preview
Loading



Powered by BlogEngine.NET 1.6.1.0
Theme by Mads Kristensen | Modified by Mooglegiant

About:
Allan Svelmøe Hansen

My real name is Allan Svelmøe Hansen.
I live in Denmark, where I work as a developer for hedal:kruse:brohus using SQL Server and the .NET framework since 2004.
My primary fields of expertise is back-end data integration, database design and optimization.


       View Allan Svelmøe Hansen's profile on LinkedIn     

Disclaimer

The opinions expressed herein are my own personal opinions and thoughts and does not represent my employers view in any way, nor are my results guaranteed for all situations.
Content is presented "as is", with no warranty.