February 8, 2004 at 7:53 pm
how we can recover suspected database if its does n't have backup
tell me how to recover
balaji ramanar
February 10, 2004 at 1:43 am
Rule 1 always have a backup !!!
These are the notes I have on suspect databases - in general terms don't expect too much, but these are the best known options i have.
How to recover a suspect database.
Top Tips
1. If the sytem dbs are ok but the users dbs are suspect maybe the log drive didn't boot on server reboot,
or the log drive has become disconnected.
Reconnect the log drive and restart the server
2. If you've lost the log drive for ever or just have the mdf file use
EXEC sp_attach_single_file_db @dbname = 'pubs',
@physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
Format is sp_attach_single_file_db {dbname},{physical location of mdf file}
It says the database should have been detached properly but experiance indicates this is not always the case.
This command rebuilds a new log file
-----------------------------------------------------------
See also:-
sp_add_log_file_recover_suspect_db
sp_add_data_file_recover_suspect_db
Use these where a database has been marked suspect due to insuffcient free space to expand
check out bol for details
------------------------------------------------------------
Removing the suspect status
sp_resetstatus dbname
This changes the status bit in sysdatabases .. the effective command is :-
update master.dbo.sysdatabases set status=status^256 where name= dbname
Emergency mode... ( if it's all really bad )
Set the status of the database to 32768, this should allow you the chance to dts/bcp out data from the database.
( not tested )
-----------------------------------------------------------------------------------------
If you haven't a recent backup then you can do the following. Look in your logs first, to see if is autorecovering.
If nothing is in --the logs, run sp_who2 to see if there is a rollback on the db in question.
If there is leave it it will 'hopefully' recover.
If not, and as reset status will not work you'll have to set the db to emergency mode
update sysdatabases set status = 32768
Then set the db to single user
exec sp_dboption 'yourdb', 'single user', 'true'
Then run dbcc checkdb ('yourdb', REPAIR_REBUILD)
Set single user to false.
--
--P.S Don't reboot the server or restart SQL!!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 10, 2004 at 6:32 am
First I would try to determine WHY the database is being marked as suspect. Here are a couple of reasons we have had the problem at my office:
You have a SQL Server database that appears healthy and well [i.e. DBCC CheckDB reports nothing, and the physical disk
have no issues], yet, with a high frequency, the database gets marked as 'Suspect'.Solution
Perhaps you have turned on the 'Auto Close' option of the database. Enabling this shuts down SQL Server's use of any files that the database uses, permitting other system activities (scandisk, backups, etc.) to have an exclusive lock on the file. When SQL tries to re-access the database and fails, it will mark the DB as suspect.
Turn off the 'Auto Close' option, and you'll be in business again.
And
An SQL Server database will not startup, and is marked supsect, because a DTC Transaction is in an unknown state.
Solution
The transaction must be forced clear of the system before the database will be able to start up. If a two-phase transaction was taking place during the failure, the database is stuck waiting for a response from the second database which, unfortunately, is most likely not coming.
To remove the transaction, start the Component Services MMC snap-in. Under the computer that the SQL Server is running on, expand teh Dsitributed Transactions folder, and then click the Transaction List folder item. In the right-pane, the transaction that is causing your problem should be displayed.
With the SQL Server stopped, right-click the transaction and choose 'Abort.'
Restarting the SQL Server now should permit your database to fully recover.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply