Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
admin001
Posting Yak Master
166 Posts |
 Posted - 2003-05-24 : 01:35:11
|
| Hello ,Need some help . I have a prod. env. with SQL Server 6.5 . One of my database has got corrupted while running scripts and has gone into a suspect mode . In such situation is it possible to retrieve the Database back to normal or copy the DB to another SQL 6.5 server ? Unfortunately there was no backup planned only for this particular database . IS there a way out ? Many thanks . |
|
|
pjanil1
Starting Member
10 Posts |
Posted - 2003-05-24 : 02:38:44
|
| hi, you can reset the suspect stataus by running an sp_resetstatus proc.this u have to create in master. scipt is available in books online. just check for 'resetting the suspect status' in books online.pl. post the resulttake careanil |
 |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-05-24 : 03:07:51
|
| I'm not sure why so many people think sp_resetstatus will fix these problems. I would bet that in 95% of suspect databases cannot be fixed via sp_resetstatus. I'm not even sure that sp_resetstatus was available in 6.5? I don't think it was, but it has been several years since I have done anything with 6.5.I would suggest calling MS PSS. Of course if you had a BACKUP, you wouldn't need to. Unfortunately, you may be SOL. You should definitely consider upgrading to SQL 2000. -Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
pjanil1
Starting Member
10 Posts |
Posted - 2003-05-24 : 03:26:20
|
| sp_reseststatus is available in books online. u have to create the sp. I too agree it is not a solution always. but on many occasions it works. In my experience in two out of 4 cases it workedanil |
|
|
|
admin001
Posting Yak Master
166 Posts |
Posted - 2003-05-24 : 03:46:11
|
| Hello ,I did reset the database status to 64 now. Now while I am trying to backup the database from the EM , it is giving an error on sysobjects system table as:Error 1109 Severity 21 , stat1 , SPID 4 Attempt to allocation page 0 failed either because object is not correct ....Can i create a new DB by the same name on another SQL Server and export only the user tables from the old DB ( which was reset ) to the new DB ( with the same name ) on another server and then back it up ? Thanks a lot for the info. and help. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-05-24 : 04:30:05
|
| Run checkdb and newalloc on the db. See what errors you get.-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
pjanil1
Starting Member
10 Posts |
Posted - 2003-05-24 : 06:00:04
|
| I think export to another m/c should be alright.Run DBCC checkdb, DBcc newalloc, DBCC checkcatalog etc to ensure that ur objects are alright. if the problem still persists see whether u have any dependency problems(sp_depends) |
|
|
|
|
|
|
|
|
Database in Suspect mode.