<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by UTSAB...
»
A guide to recover a database out from...
43 posts, Page 1 of 5
1
2
3
4
5
»
»»
A guide to recover a database out from Suspect mode
Rate Topic
Display Mode
Topic Options
Author
Message
CDBA
CDBA
Posted Saturday, February 06, 2010 10:31 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Wednesday, March 27, 2013 3:20 PM
Points: 194,
Visits: 334
Comments posted to this topic are about the item
A guide to recover a database out from Suspect mode
CDBA
Post #861264
AndrewJacksonZA
AndrewJacksonZA
Posted Sunday, February 07, 2010 11:54 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, May 07, 2013 5:52 AM
Points: 165,
Visits: 1,022
Caution!
Below steps will cause you to loose data...
It's "lose data."
Nice article, thanks. I'll remember
RESTORE DATABASE WITH RECOVERY
Post #861442
Ritesh Medhe
Ritesh Medhe
Posted Monday, February 08, 2010 12:38 AM
Old Hand
Group: General Forum Members
Last Login: Wednesday, July 18, 2012 1:22 AM
Points: 325,
Visits: 234
very helpful article. thx
Post #861452
bijoy.prasad
bijoy.prasad
Posted Monday, February 08, 2010 1:56 AM
Grasshopper
Group: General Forum Members
Last Login: Monday, February 08, 2010 6:03 PM
Points: 13,
Visits: 19
Good article - To the Point.
I had a problem of SUSPECTED Database few years back (SQL2000). I updated the status in sysdatabases, and the problem never reoccured.
Thanks
B.K.Prasad
Post #861476
prasad2085
prasad2085
Posted Monday, February 08, 2010 2:06 AM
Forum Newbie
Group: General Forum Members
Last Login: Monday, April 02, 2012 12:54 AM
Points: 1,
Visits: 66
Well thats a really helping things to bring the Database Online. But before going to perform the thrid step I would like to go for DBCC DBRECOVER(DBNAME) command. Also after setting the Database in emergency do not forget to execute the DBCC Checkdb command, this will give what are the objects or transcations having the problem. Then in the end go for DBCC CHECKDB with allowloss option.
Post #861488
jshailendra
jshailendra
Posted Monday, February 08, 2010 2:23 AM
Old Hand
Group: General Forum Members
Last Login: Friday, December 21, 2012 5:24 AM
Points: 366,
Visits: 436
Very helpful arcticle..thanks a lot. Wish I should not come across this situation.
Post #861497
TravisDBA
TravisDBA
Posted Monday, February 08, 2010 5:11 AM
Ten Centuries
Group: General Forum Members
Last Login: Thursday, May 09, 2013 9:23 AM
Points: 1,288,
Visits: 2,996
Paul S. Randal is the definitive source on Database Corruption and recovering corrupt databases. Check him out at http://www.sqlskills.com/BLOGS/PAUL/category/CHECKDB-From-Every-Angle.aspx He wrote DBCC CHECKDB for Mircosoft. A lot of things in this article and a whole lot more is in his site....
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...
"
Post #861562
sjimmo
sjimmo
Posted Monday, February 08, 2010 6:47 AM
SSCrazy
Group: General Forum Members
Last Login: Today @ 7:15 AM
Points: 2,864,
Visits: 2,467
Another possible issue/solution, probably under possibility 1:
I get a lot of SAN detatch errors from a certain iSCSI SAN. The SAN Drives will drop, and reattach. Fortunately, the Windows OS sees the drives as being back but once SQL Server looses the connection, there is no reconnect. These will show up as suspect databases (multiple). Solution so far (and I have been lucky) is simply restart the SQL Service. Usually shows up as a 9002 error in the error log.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Post #861612
GilaMonster
GilaMonster
Posted Monday, February 08, 2010 7:08 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 3:08 PM
Points: 37,720,
Visits: 29,973
Possibility 1: Some one deleted/misplaced a data/log file when SQL Server was offline and the database can not start because of missing file. The solution is to place the missing data/log file in proper location. The SQL Server Error Log error message will give you the exact name and path of the missing file. Once you place the file execute below command to bring your database online with no data loss.
RESTORE DATABASE WITH RECOVERY
Possibility 2: SQL Server could not access or place an exclusive lock on the data or log file while coming online. Typically I experience this when SQL Server is shared with some other tool (like Antivirus), which puts an exclusive lock on the data/log file. To resolve it, use process explorer and kill the file handler which placed lock on the file. You may want to involve your System Admins to get this step executed. Then execute below command and you will have your database online with no data loss:
RESTORE DATABASE WITH RECOVERY
Sorry, but I am going to completely, 100% disagree with you.
RESTORE DATABASE <DB Name> WITH RECOVERY is what you run if your database is in a RESTORING state because an earlier backup was restored with NORECOVERY. It is not used to bring a database out of the suspect state.
I did a quick test. Created a DB, shut SQL down, renamed the data file and restarted SQL. The DB came up in the RECOVERY PENDING state (not suspect). I then followed your instructions, renamed the file correctly and ran the RESTORE DATBASE statement.
RESTORE DATABASE SuspectDB WITH RECOVERY
Msg 3148, Level 16, State 3, Line 1
This RESTORE statement is invalid in the current context. The 'Recover Data Only' option is only defined for secondary filegroups when the database is in an online state. When the database is in an offline state filegroups cannot be specified.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
The correct solution, when a file has been moved or was locked by some other app when SQL started, is to rectify the root cause first (put the file back, remove whatever was locking it), then take the DB offline and bring it back online (or restart the SQL Service). That will allow SQL to open the DB again, this time find all the files and run the restart_recovery again this successfully.
This will work if the DB state is RECOVERY PENDING (meaning that restart-recovery has not started due to missing or inaccessible files). If the DB state is truly suspect, it means there's a problem in the data file or log file, the restart recovery started (so all files are accessible) but could not complete. That's a lot more severe than just a missing file and usually indicates corruption of the data/log that was encountered during the restart-recovery.
One additional note on Emergency mode repair. It's a last resort. If it fails, there's nothing else that can be done.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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
Post #861637
Grant Fritchey
Grant Fritchey
Posted Monday, February 08, 2010 7:22 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 6:59 AM
Points: 13,378,
Visits: 25,163
I'm a little surprised that you're not recommending running CHECKDB on the database in question. Yes, it's marked as suspect, and yes, you might find a cause in the error log, but then again, you might not. Running a database consistency check will frequently identify the root cause. It might be something recoverable through other means than running repair with data loss, which is a very dangerous thing to do to a production system.
----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for
Red Gate Software
Post #861649
« Prev Topic
|
Next Topic »
43 posts, Page 1 of 5
1
2
3
4
5
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
