How to recover table in Sybase 12

How to recover table in Sybase

 

Scenario

 

User can’t use some table found error in sybase log. Confirm the error Msg id from the “trouble shooting guide” that means data page of the table account_db.n_test_table has corruct and can’t be fixed by dbcc.

 

DBCC result looks likes blow,

RUN   dbcc fix_al(account_db)     result: found table corrupt and one entry is bad

dbcc fix_al(account_db)
TABLE: n_test_table             OBJID = 766625774
INDID=0  FIRST=1129      ROOT=1129       SORT=0
        Data level: 0.  483311 Data pages allocated and 60473 Extents allocated.
INDID=2  FIRST=308905    ROOT=308904     SORT=1
Msg 2529, Level 16, State 7:
Server 'TESTDB', Line 1:
Table Corrupt: Attempted to get page 2546720, object 766625774;  got page 0,
object 0.

 

Perform the revocer steps

 

1.         Bcp out the table to allenunix:/buffer

2.         Sp_helpdepends,sp_help n_test_table to see related objects with corrupt table n_test_table before drop the table

3.         Drop table n_test_table in account_db and create the table without index

4.         Bcp in the data file, reload the trigger file since it was one of droped object from sp_helpdepends

5.         Re-create index with create-index sql script.

 

Details

 

1.       # bcp account_db..n_test_table out /buffer/n_test_table.dat -c -t'{|}' -r'$\n' -Usa -Ppassword –STESTDB

 

2.       allenunix:root /  4 > isql -Usa -STESTDB

Password:

1> use account_db

2> go

1> sp_depends n_test_table

2> go

Output ommit…………..

1> drop n_test_table

2> go

1>exit

 

3, isql –Usa –Ppassword –STESTDB < n_test_table.table_create.sql

4, bcp account_db..n_test_table in /buffer/n_test_table.dat -c -t'{|}' -r'$\n' -Usa -Ppassword –STESTDB

 

Note: If there are too many lows, you can use bcp –F and –L parameter to perform two or more bcp in operation at same time

 

5, isql –Usa –Ppeaceful –STESTDB < n_test_table.index_create.sql