請教OFFLINE數(shù)據(jù)庫如何刪除?(已搞定)

redwaves

請教OFFLINE數(shù)據(jù)庫如何刪除?

在作數(shù)據(jù)庫異機(jī)恢復(fù)時，先將數(shù)據(jù)庫以單用戶模式啟動后成功恢復(fù)了master數(shù)據(jù)庫.
重啟數(shù)據(jù)庫后通過sp_helpdb可以看到要恢復(fù)的數(shù)據(jù)庫xhb是offline狀態(tài)

通過drop database xhb，提示:
1> drop database xhb
2> go
Msg 840, Level 17, State 2:
Server 'AS5XHB', Line 1:
Device 'xhb_dev' (with physical name '/opt/sybase1254/data/xhb_dev.dat', and
virtual device number 3) has not been correctly activated at startup time.
Please contact a user with System Administrator (SA) role.
1>

請問如何刪除這個庫和設(shè)備文件重建以便作xhb這個數(shù)據(jù)庫的恢復(fù)?

wfcjz

都告訴你了先DROP設(shè)備，再DROP database了。

andkylee

回復(fù) 2# wfcjz


   理解不對，設(shè)備Device 'xhb_dev' 在數(shù)據(jù)庫服務(wù)器啟動的時候沒有被激活，所以才導(dǎo)致刪除其上的數(shù)據(jù)庫時報錯。

必須按照：先刪除數(shù)據(jù)庫，然后再刪被數(shù)據(jù)庫使用的設(shè)備。這個順序。

andkylee

回復(fù) 1# redwaves


    兩種方法：
第一種：
use master
go
dbcc traceon(3604)
go
dbcc dbrepair('xhb',dropdb)
go

第二種：　
use master
go
delete from sysusages where dbid = db_id('xhb')
go
delete from sysdatabases where name='xhb'
go

然后，手動刪除xhb所使用的設(shè)備。 delete from sysdevices where name ='設(shè)備邏輯名字'
最后重啟sybase數(shù)據(jù)庫，即可！（利用sysusages表中的vdevno或者low vstart對應(yīng)關(guān)系找到設(shè)備名稱，稍微有點(diǎn)麻煩。）


ps：請慎用第二種方法。

redwaves

多謝andkylee

redwaves

都告訴你了先DROP設(shè)備，再DROP database了。
wfcjz 發(fā)表于 2010-07-13 17:22

    我知道先drop database 然后drop device

wfcjz

回復(fù) 3# andkylee


   看誤了，不知道最近怎么了。

多擔(dān)旦

andkylee

回復(fù) 7# wfcjz


    呵呵，沒事。技術(shù)的事可以討論的。

redwaves

回4樓
按照您的指導(dǎo)作了還是不成功

1> use master
2> go
1> dbcc traceon(3604)
2> go
00:00000:00012:2010/07/14 17:15:35.75 server  DBCC TRACEON 3604, SPID 12
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
1> dbcc dbrepair('xhb',dropdb)
2> go
Msg 2573, Level 16, State 1:
Server 'AS5XHB', Line 1:
Database 'xhb' is not marked suspect. You cannot drop it with DBCC.
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
1>

按提示需要將數(shù)據(jù)庫xhb標(biāo)記為suspect
查過手冊提示:
After You Execute sp_marksuspect
Once the procedure is created successfully, updates to the system catalog should be immediately disabled as follows:

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


Syntax
sp_marksuspect database_name


Example
1> sp_marksuspect PRODUCTION
2> go

通過sp_help沒有找到sp_marksuspect命令,版本12.5.4

還有別的辦法嗎？

andkylee

sp_listsuspect_db 看看有沒有數(shù)據(jù)庫處于可疑狀態(tài)？


只有sp_makesuspect_obj　，而沒有讓數(shù)據(jù)庫直疑的過程�！�