恢复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步骤来比较好，至少不会出什么错。