RECOVERING A DATABASE WITH SUSPECT STATUS

To RECOVER a DATABASE STATUS you need to run sp_resetstatus. If you don’t have one, below are the steps on how to create and execute sp_resetstatus on WINDOWS platform for SYSBASE server.

1) Go to start --> run --> cmd
2) At cmd prompt type this command

C:\>isql –Uusernamehere –Sservernamehere

3) To create and execute sp_resetstatus, take the following steps
At command prompt

1> use master
2> go
1> sp_configure "allow updates", 1
2> go


4) Create the sp_resetstatus procedure in the master database.

Start --> program --> Sybase --> Sybase central java edition

After that, write the following to the sp. Name it as sp_resetstatus:

CREATE PROC sp_resetstatus @dbname varchar(30) ASDECLARE @msg varchar(80)IF @@trancount > 0BEGINPRINT "Can't run sp_resetstatus from within a transaction."RETURN (1)ENDIF suser_id() != 1BEGINSELECT @msg = "You must be the System Administrator (SA)"SELECT @msg = @msg + " to execute this procedure."RETURN (1)ENDIF (SELECT COUNT(*) FROM master..sysdatabasesWHERE name = @dbname) != 1BEGINSELECT @msg = "Database '" + @dbname + "' does not exist!"PRINT @msgRETURN (1)ENDIF (SELECT COUNT(*) FROM master..sysdatabasesWHERE name = @dbname AND status & 256 = 256) != 1BEGINPRINT "sp_resetstatus can only be run on suspect databases."RETURN (1)ENDBEGIN TRANUPDATE master..sysdatabases SET status = status ^ 256WHERE name = @dbnameIF @@error != 0 OR @@rowcount != 1ROLLBACK TRANELSE BEGINCOMMIT TRANSELECT @msg = "Database '" + @dbname + "' status reset!"PRINT @msgPRINT " " PRINT "WARNING: You must reboot SQL Server prior to "PRINT " accessing this database!"PRINT " "ENDGO

5) after the procedure is created, at the command prompt type

1> use master
2> go
1> sp_configure "allow updates", 0
2> go


6) to run sp_resetstatus, at the command prompt type

1> sp_resetstatus "databasename"
2> go