恢復Sybase ASE 15.0.x中的master資料庫

恢復Sybase ASE 15.0.x中的master資料庫是一件比較痛苦的事情，官方文件有時候也未免解說不夠詳細，自己動起手來又完全不是那麼回事，尤其是涉及到字符集問題的時候。下邊是詳細的恢復過程。

一、準備工作

1.    備份

啟動備份伺服器，Adaptive Server以-m方式啟動，將master資料庫備份出來

1> dump database master to 'd:/sybase/master.dump'

2> go

WARNING: In order to LOAD the master database, the ASE must run in single-user

mode. If the master database dump uses multiple volumes, you must execute

sp_volchanged on another ASE at LOAD time in order to signal volume changes.

Backup Server session id is:  5.  Use this value when executing the

'sp_volchanged' system stored procedure after fulfilling any volume change

request from the Backup Server.

Backup Server: 6.28.1.1: Dumpfile name 'master0905813BEF ' section number 1

mounted on disk file 'd:/sybase/master.dump'

Backup Server: 4.188.1.1: Database master: 1372 kilobytes (18%) DUMPED.

Backup Server: 4.188.1.1: Database master: 9786 kilobytes (100%) DUMPED.

Backup Server: 3.43.1.1: Dump phase number 1 completed.

Backup Server: 3.43.1.1: Dump phase number 2 completed.

Backup Server: 3.43.1.1: Dump phase number 3 completed.

Backup Server: 4.188.1.1: Database master: 9794 kilobytes (100%) DUMPED.

Backup Server: 3.42.1.1: DUMP is complete (database master).

2.         記下伺服器安裝時使用的字符集

sp_helpsort

......

Sort Order Description

 ------------------------------------------------------------------

 Character Set = 173, gb18030

     Character set for P.R.C standard GB 18030-2000

     Class 2 Character Set

 Sort Order = 50, bin_gb18030

     Binary sort order for simplified Chinese using gb18030.

(return status = 0)

3.         轉移資料

關掉adaptive server (shutdown with wait)，然後

刪除%SYBASE%/data/master.dat或者重新命名（實驗需要），記下它的大小哦

二、恢復

1.       初始化master資料庫的裝置

sqlsrvr -dd:/sybase/data/master.dat -b 30M -z 2k

(這裡的30M就是原來的master.dat的實際大小)

2.       以”-m”方式啟動ASE adaptive server

sqlsrvr -dd:/sybase/data/master.dat -sSEANLAPTOP -ed:/sybase/ASE-15_0/install/errorlog.log -id:/sybase/ini -Md:/sybase/ASE-15_0 –m

這時你如果查詢，發現系統用的是cp850字符集（痛苦.....），看來需要修改之。可是sp_configure之類的儲存過程又不能用(因為沒有裝進去)

3.       強行安裝master建庫指令碼

isql -Usa -P -i d:/Sybase/ASE-15_0/scripts/instmstr

4.       更改字符集

charset -Usa -P binary.srt gb18030

D:/>isql -Usa -P

1> select id, name from syscharsets where id>=170

2> go

 id  name

 --- ------------------------------

 170 eucgb

 173 gb18030

 179 sjisbin

 192 eucjisbn

 194 big5bin

1> sp_configure "default character set id",173

2> go

In changing the default sort order, you have also reconfigured ASE's default

character set.

 Parameter Name

         Default                Memory Used            Config Value

         Run Value                Unit

         Type

 ------------------------------------------------------------

         ---------------------- ---------------------- ------------------------

         ------------------------ ----------------------------------------

         --------------------

 default character set id

                   2                      0                     170

                    2             id

         static

(1 row affected)

然後，兩次重啟(以-m方式) adaptive server

5.       註冊BACKUP server

1> update sysservers set srvnetname='SEANLAPTOP_BS' where srvname='SYB_BACKUP'

2> go

(1 row affected)

1> commit

2> go  

6.       恢復

1> load database master from 'd:/sybase/master.dump'

2> go

然後以正常方式啟動ASE即可。

還有一種暴力方式，就是不安裝master建庫指令碼，直接執行5, 然後6，你會發現，第6步出現字符集不相容的錯誤，要求使用traceflag 3100，於是：

1> dbcc traceon(3100)

2> go

DBCC execution completed. If DBCC printed error messages, contact a user with

System Administrator (SA) role.

1> load database master from 'd:/sybase/master.dump'

2> go

WARNING: In order to LOAD the master database, the ASE must run in single-user

mode. If the master database dump uses multiple volumes, you must execute

sp_volchanged on another ASE at LOAD time in order to signal volume changes.

Backup Server session id is:  7.  Use this value when executing the

'sp_volchanged' system stored procedure after fulfilling any volume change

request from the Backup Server.

Backup Server: 6.28.1.1: Dumpfile name 'master0905811BC1 ' section number 1

mounted on disk file 'd:/sybase/master.dump'

Backup Server: 4.188.1.1: Database master: 2390 kilobytes (17%) LOADED.

Backup Server: 4.188.1.1: Database master: 13318 kilobytes (100%) LOADED.

Backup Server: 4.188.1.1: Database master: 13326 kilobytes (100%) LOADED.

Backup Server: 3.42.1.1: LOAD is complete (database master).

Started estimating recovery log boundaries for database 'master'.

Database 'master', checkpoint=(5580, 3), first=(5580, 3), last=(5580, 9).

Completed estimating recovery log boundaries for database 'master'.

Started ANALYSIS pass for database 'master'.

Completed ANALYSIS pass for database 'master'.

Started REDO pass for database 'master'. The total number of log records to

process is 7.

Redo pass of recovery has processed 1 committed and 0 aborted transactions.

Completed REDO pass for database 'master'.

Started filling free space info for database 'master'.

Completed filling free space info for database 'master'.

Started cleaning up the default data cache for database 'master'.

Completed cleaning up the default data cache for database 'master'.

(4 rows affected)

Database 'master' is now online.

這樣，再執行第4步，修改字符集。最終也能達到目的。畢竟，master庫中的內容儲存的全是cp850相容字符集格式，沒有國際化問題。但我並不推薦這種方式。還是老老實實，按照3, 4, 5, 6步驟來比較好，至少不會出什麼錯。