SOS DATA Bace marked as suspect 1

[TwoTons]

Well I am an IT admin for about a year now doing ok but always still learning. I was testing my company’s backup and restored a drive image to the root. The server came back up and every thing was fine until i checked SQL it’s said my (DATA (SUSPECT)) I went to barns and noble really could not find much in the book department. I really need help to get the company’s Data base back up. I am running win2000 server and SQL 2000.

 

[mrdenny]

Are you sure that the physical files for that database are on the drive?

Where the database files restored? How were they backed up?

You can try to detach the reattach the database from the SQL Server.

What's your backup stragity for the SQL databases?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[TwoTons]

1. yes 90% because the root c: that I brought back has nothing to do with those files they are on the D: .

2. we do drive images of all the drives the newest image I have is of 3/11/06. we can pull just files but we don't know what files we need to pull for it. the data base is called DATA. could we just restore from the image??

3. Ok I am trying this now be having never do this before.
Do I start this in the Query Analyzer?

sp_detach_db [@DATA =]'DATA'
[,[@skipchecks =]'skipchecks']


what do I have to to run this syntax??
need to know how to do this step by step.

and also how would i do the sp_attach_db

sp_attach_db [@DATA = ] 'DATA'
[@filename1 = ] 'filename_n' [ ,...16]

I use power quest as my back ups i can restore A file or hole drive image. my backup are up to date. if i know what files to restore i can use power quest and restore to destination.

I know i am asking for a lot thanks for responding denny.

 

[TwoTons]

this is the error i am getting in the errorlog

spid8 error: 823, Severity: 24, State:2
spid8 I/O error (torn page) detected during read at
offset 0x00000000030000 in file 'd:\mssql\data\msdbdata.mdf
clearing tempdb database
by passing recovery for database 'data' because it is marked suspect.

the two that are marked suspect are DATA
MSDB

 

[TwoTons]

ok well i just tryed restoreing the msdbdata.mdf off the back up no change in the data base.

 

[TwoTons]

this is the other suspect data bace i have. I have tryed restore the .mdf and the .ldf off the drive image no luck.

so i tryed to detaching the data base.

I go into SQL Query Analyzer

open query window

sp_detach_db [ @msdb = ] 'msdb'
[ , [ @skipchecks = ] 'skipchecks' ]

then I execute the query

server msg 170 level 15, state 1,line1
line1:incorrect syntax near 'msdb'

what did i do wrong ???

 

[mrdenny]

With SQL you can't just backup an image of the mdf and ldf files which SQL Server is running. Doing so will give you useless files (as you have seen). Do you have any SQL Server backups of the databases?

msdb is a system database and can't be detached.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[TwoTons]

Well, we got it working and it looks like the image files do work, because I did use them. What happened is my raid controller trashed the first back up done of Friday but we do 3 back ups each night for redundancy. So we were able to detach (data), taking the image saving it to another location on the server and then attached. When that did not error out we knew we had a good data, then we shut down SQL server copied the mdf,ldf back into the d:\mssql\data\ then restarted and reattached the new mdf,ldf data base files and it was fine. Took us about 19hours but we did it and by start of business on Monday, nobody can tell anything happened. I think that's the coolest part of IT. If this should not have worked could you tell me why???

Maybe we were just lucky. I do thank you for replying, Denny.

 

[SQLBill]

So we were able to detach (data), taking the image saving it to another location

That makes a difference. You didn't say that before. If you make the database offline (by detaching or stopping the services) then, yes, you can make a copy of the .mdf and .ldf files. Don't try that if the database is still in use.

-SQLBill

Posting advice: FAQ481-4875