SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A guide to recover a database out from Suspect mode


A guide to recover a database out from Suspect mode

Author
Message
CDBA
CDBA
Right there with Babe
Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)

Group: General Forum Members
Points: 784 Visits: 334
Comments posted to this topic are about the item A guide to recover a database out from Suspect mode


CDBA

Andrew Jackson
Andrew Jackson
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1047 Visits: 1121
Caution! Below steps will cause you to loose data...
It's "lose data."

Nice article, thanks. I'll remember RESTORE DATABASE WITH RECOVERY
Ritesh Medhe
Ritesh Medhe
Say Hey Kid
Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)

Group: General Forum Members
Points: 705 Visits: 234
very helpful article. thx
bijoy.prasad
bijoy.prasad
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 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
prasad2085
prasad2085
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 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.:-)
jshailendra
jshailendra
SSChasing Mays
SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)

Group: General Forum Members
Points: 654 Visits: 455
Very helpful arcticle..thanks a lot. Wish I should not come across this situation. :-)
TravisDBA
TravisDBA
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3236 Visits: 3069
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. ...:-D"
sjimmo
sjimmo
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4700 Visits: 2907
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)

Group: General Forum Members
Points: 210619 Visits: 46249
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, 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


Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93338 Visits: 33004
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search