November 10, 2008 at 2:07 pm
Hi,
When I tried to restore the backup of production to my dev server my dtabase went to suspect mode in sql 2000
when I checked the error log files for that I found this message
The backup data in 'F:\Program Files\SQL\Backup\abc.BAK' is incorrectly formatted.
Backups cannot be appended, but existing backup sets may still be usable.
I have only one .BAK file no trn files. How can I recover my datbase from suspect mode??
I tried sp_configre and then changed database status to 16(torn page detection) from 48(suspect)
but after that also I can see the databse in suspect mode.
Do I need to create another databse and restore prod backup on it??Please let me know
Thanks
November 10, 2008 at 2:54 pm
Is your dev or production database suspect?
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
November 10, 2008 at 3:14 pm
no none of them in suspect mode
November 11, 2008 at 2:19 pm
roadtrain64 (11/10/2008)
How can I recover my datbase from suspect mode??
You stated you wanted help to "recover from suspect mode"......The backup you are trying to restore, is it SQL 2000 backup being restored to SQL2000 or is it a SQL 2005 backup being restored to SQL 2000 (which you cannot do)? You posted in a 2005 forum but mention "my dtabase went to suspect mode in sql 2000".
-- You can't be late until you show up.
November 13, 2008 at 3:20 am
Hi Dear,
Do you have database MDF and LDF? If yes you can try this...
Solution:
1. Create Database with exact name and mdf-ldf files
2. Stop MSSQLSERVER service, replace created mdf file with original one
3. Start MSSQLSERVER service, the database will be in Suspend mode
4. From Query Analyzer (QA) execute script
use master
go
sp_configure 'allow updates', 1
reconfigure with override
go
5. From QA execute script
update sysdatabases set status= 32768 where name = ' '
6. Restart MSSQLSERVER service, the database will be in Emergency mode
7. Rebuild Log. From QA execute script
DBCC REBUILD_LOG(' '). You got a
message - Warning: The log for database ' ' has been rebuilt.
8. From QA execute script
Use master
go
sp_configure 'allow updates', 0
Go
9. Clear from Enterprise Manager on database properties options tab Restrict
access checkbox
November 13, 2008 at 11:00 am
Shripad - horrible advice as you describe it. You're missing the warning about the effects of rebuilding the log (i.e. causing more corruption) and that you should run a checkdb with repair_allow_data_loss afterwards.
Anyway reading that advice also needs to read this - http://www.sqlskills.com/BLOGS/PAUL/post/Corruption-Last-resorts-that-people-try-first.aspx
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply