Sybase数据库技术,数据库恢复

 

随着Sybase被完全整合到SAP下，Sybase原来的支持网站被SAP Support Portal取代。
只有购买了SAP服务的用户才能使用账号登录SAP Support Portal进行介质下载、补丁升级、报Incident等。
目前，原Sybase所有产品（包括：Adaptive Server Enterprise、Sybase IQ、Replication Server、PowerDesigner等）的官方手册仍然可以从https://infocenter.sybase.com/help/index.jsp进行浏览或下载。暂不清楚该网站https://infocenter.sybase.com/help/index.jsp何时会被完全迁移到SAP Support上！
Sybase官方手册英文版有html和pdf两种格式，而中文版手册只有pdf一种格式。为了国内Sybase用户更方便、快捷地搜索Sybase常见产品的官方手册内容，特将中文版Sybase官方手册转为html格式！
Sybase产品官方手册中文版的html格式所有内容的版权归SAP公司所有！本博客站长是Sybase数据库的铁杆粉丝！

如有Sybase数据库技术问题需要咨询，请联系我！

  QQ :289965371
  Email:

以下官方手册为ASE 15.7 ESD#2中文版：

1. 新增功能公告 适用于 Windows、Linux 和 UNIX 的 Open Server 15.7 和 SDK 15.7
   
2. 新增功能摘要
   
3. 新增功能指南
   
4. ASE 15.7 发行公告
   
5. 配置指南（windows）
   
6. 安装指南（windows）
   
7. 参考手册：构件块
   
8. 参考手册：命令
   
9. 参考手册：过程
   
10. 参考手册：表
    
11. Transact-SQL® 用户指南
    
12. 系统管理指南，卷 1
    
13. 系统管理指南，卷 2
    
14. 性能和调优系列：基础知识
    
15. 性能和调优系列：锁定和并发控制
    
16. 性能和调优系列：监控表
    
17. 性能和调优系列：物理数据库调优
    
18. 性能和调优系列：查询处理和抽象计划
    
19. 性能和调优系列：使用 sp_sysmon 监控 Adaptive Server
    
20. 性能和调优系列：利用统计分析改进性能
    
21. 程序员参考 jConnect for JDBC 7.0.7
    
22. Adaptive Server Enterprise 中的 Java
    
23. 组件集成服务用户指南
    
24. Ribo 用户指南
    
25. 内存数据库用户指南
    
26. Sybase Control Center for Adaptive Server® Enterprise
    
27. 安全性管理指南
    
28. 实用程序指南
    

 

< 上一个 | 内容 | 下一步 >

sp_downgrade ‌

说明 （仅限 master 数据库）验证是否已准备好降级至早期的 15.0.x 版。同时 降级 Adaptive Server 15.0.2 已修改的系统目录更改。

语法 sp_downgrade @cmd = {'prepare' | 'downgrade' | 'help',}

@toversion = 'n'[, @verbose = 0 | 1][, @override = 0 | 1]

参数 prepare

先使用该参数来验证 Adaptive Server 15.0.2 是否为降级做好了准备。

downgrade

如果已做好准备，则在 prepare 参数之后用于继续执行降级至以前安 装的 15.x 版的 Adaptive Server 的操作。服务器必须在单用户模式下。

（以 - m 选项开头）

toversion

可以是 15.0 或 15.0.1，分别写作 “150”或 “15.0”以及 “1501”或 “15.0.1”

verbose

指定详细程度。有效选项为 0 （表示 “否”）或 1 （表示 “是”）。

override

指定是否跳过此时不可写的数据库。有效选项为 0 （表示 “否”）或

1 （表示 “是”）。

示例 示例 1 以下是运行 sp_downgrade 的输出示例。

00:0006:00000:00006:2011/06/29 02:16:44.35 server Preparing ASE downgrade from 15.7.0.0 to 15.5.0.0.

00:0006:00000:00006:29.06.11 02:16:440.37 server Starting downgrading ASE. 00:0006:00000:00006:2011/06/29 02:16:44.37 server Downgrade :Marking stored procedures to be recreated from text.

00:0006:00000:00006:2011/06/29 02:16:45.34 server Downgrade :Removing full logging modes from sysattributes.

00:0006:00000:00006:2011/06/29 02:16:45.34 server Downgrade :Downgrading data-only locked table rows.

00:0006:00000:00006:2011/06/29 02:16:45.34 server Downgrade :Removing full logging modes from sysattributes.

00:0006:00000:00006:2011/06/29 02:16:45.34 server Downgrade :Removing column sysoptions.number.

00:0006:00000:00006:2011/06/29 02:16:45.34 server Downgrade :Removing srvprincipal column from sysservers system table

00:0006:00000:00006:2011/06/29 02:16:45.34 server Downgrade :Removing 'automatic master key access' configuration parameter.

00:0006:00000:00006:2011/06/29 02:16:45.35 server Downgrade :Removing DualControl sysattribute rows

00:0006:00000:00006:2011/06/29 02:16:45.35 server Downgrade :Downgrading sysattributes system table.

00:0006:00000:00006:2011/06/29 02:16:45.37 server Downgrade :Downgrading

syscomments system table.

00:0006:00000:00006:2011/06/29 02:16:45.42 server Downgrade :Truncated role password, locked role and removed columns locksuid, lockreason, lockdate from syssrvroles

00:0006:00000:00006:2011/06/29 02:16:45.43 server Downgrade :Removing catalog changes for RSA Keypair Regeneration Period and Login Profile 00:0006:00000:00006:2011/06/29 02:16:45.43 server Downgrade :Turning on database downgrade indicator.

00:0006:00000:00006:2011/06/29 02:16:45.43 server Downgrade :Resetting database version indicator.

00:0006:00000:00006:29.06.11 02:16:450.43 server ASE downgrade completed.

示例 2 检查数据库是否做好降级准备：

1> sp_downgrade 'prepare','15.5',1

2> go

Downgrade from 15.7.0.0 to 15.5.0.0 (command:'prepare') Checking databases for downgrade readiness.

There are no errors which involve encrypted columns. sp_downgrade 'prepare' completed.

(return status = 0)

示例 3 将 Adaptive Server 从 15.7 版本降级到 15.5 版本：

1> sp_downgrade 'downgrade','15.5',1

2> go

Downgrade from 15.7.0.0 to 15.5.0.0 (command:'downgrade') Checking databases for downgrade readiness.

There are no errors which involve encrypted columns.

Executing downgrade step 2 [dbcc markprocs(@dbid)] for :

- Database:master (dbid:1)

sql comman is:dbcc markprocs(@dbid)

DBCC execution completed.If DBCC printed error messages, contact a user with System Administrator (SA) role.

- Database:tempdb (dbid:2)

sql comman is:dbcc markprocs(@dbid)

DBCC execution completed.If DBCC printed error messages, contact a user with System Administrator (SA) role.

- Database:model (dbid:3)

sql comman is:dbcc markprocs(@dbid)

DBCC execution completed.If DBCC printed error messages, contact a user with System Administrator (SA) role.

- Database:sybsystemdb (dbid:31513) sql comman is:dbcc markprocs(@dbid)

DBCC execution completed.If DBCC printed error messages, contact a user with System Administrator (SA) role.

- Database:sybsystemprocs (dbid:31514) sql comman is:dbcc markprocs(@dbid)

DBCC execution completed.If DBCC printed error messages, contact a user with System Administrator (SA) role.

Executing downgrade step 17 [delete sysattributes where class = 38] for :

- Database:master (dbid:1)

sql comman is:delete sysattributes where class = 38

Executing downgrade step 18 [declare @ret int select @ret = dol_downgrade_check(':DBNAME:', @toversid) print "Database :DBNAME:table downgrade status:%1!", @ret if @ret != 0 begin print "*** Tables in database ':DBNAME:'cannot be downgraded." print "*** See the server error log for details." select

@exec_error_count = @exec_error_count + 1 end] for :

- Database:master (dbid:1)

sql comman is:declare @ret int select @ret = dol_downgrade_check('master', @toversid) print "Database master table downgrade status:%1!", @ret if @ret != 0 begin print "*** Tables in database 'master' cannot be downgraded." print "*** See the server error log for details." select @exec_error_count = @exec_error_count + 1 end

Database master table downgrade status:0

- Database:tempdb (dbid:2)

sql comman is:declare @ret int select @ret = dol_downgrade_check('tempdb', @toversid) print "Database tempdb table downgrade status:%1!", @ret if @ret != 0 begin print "*** Tables in database 'tempdb' cannot be downgraded." print "*** See the server error log for details." select @exec_error_count = @exec_error_count + 1 end

Database tempdb table downgrade status:0

- Database:model (dbid:3)

sql comman is:declare @ret int select @ret = dol_downgrade_check('model', @toversid) print "Database model table downgrade status:%1!", @ret if @ret != 0 begin print "*** Tables in database 'model' cannot be downgraded." print "*** See the server error log for details." select @exec_error_count = @exec_error_count + 1 end

Database model table downgrade status:0

- Database:sybsystemdb (dbid:31513)

sql comman is:declare @ret int select @ret = dol_downgrade_check('sybsystemdb',

@toversid) print "Database sybsystemdb table downgrade status:%1!", @ret if @ret !=

0 begin print "*** Tables in database 'sybsystemdb' cannot be downgraded." print "*** See the server error log for details." select @exec_error_count =

@exec_error_count + 1 end

Database sybsystemdb table downgrade status:0

- Database:sybsystemprocs (dbid:31514)

sql comman is:declare @ret int select @ret = dol_downgrade_check('sybsystemprocs',

@toversid) print "Database sybsystemprocs table downgrade status:%1!", @ret if @ret

!= 0 begin print "*** Tables in database 'sybsystemprocs' cannot be downgraded." print "*** See the server error log for details." select @exec_error_count =

@exec_error_count + 1 end

Database sybsystemprocs table downgrade status:0

Executing downgrade step 19 [delete sysattributes where class = 38] for :

- Database:master (dbid:1)

sql comman is:delete sysattributes where class = 38

Executing downgrade step 20 [delete syscolumns where id = object_id('sysoptions') and name='number'] for :

- Database:master (dbid:1)

sql comman is:delete syscolumns where id = object_id('sysoptions') and name='number'

Executing downgrade step 21 [delete syscolumns where id = object_id('sysservers') and name = 'srvprincipal'] for :

- Database:master (dbid:1)

sql comman is:delete syscolumns where id = object_id('sysservers') and name = 'srvprincipal'

Executing downgrade step 22 [delete sysattributes where class = 503] for :

- Database:master (dbid:1)

sql comman is:delete sysconfigures where config = 503

Executing downgrade step 23 [delete sysattributes where class = 25 and attribute in (2, 3)] for :

- Database:master (dbid:1)

sql comman is:delete sysattributes where class = 25 and attribute in (2, 3)

Executing downgrade step 24 [update :DBNAME:..sysattributes set object_cinfo2 = null, object_datetime = null where object_cinfo2 is not null or object_datetime is not null delete :DBNAME:..syscolumns where id = 21 and name in ('object_cinfo2', 'object_datetime')] for :- Database:master (dbid:1)

sql comman is:update master..sysattributes set object_cinfo2 = null, object_datetime

= null where object_cinfo2 is not null or object_datetime is not null delete master..syscolumns where id = 21 and name in ('object_cinfo2', 'object_datetime')

- Database:tempdb (dbid:2)

sql comman is:update tempdb..sysattributes set object_cinfo2 = null, object_datetime

= null where object_cinfo2 is not null or object_datetime is not null delete

tempdb..syscolumns where id = 21 and name in ('object_cinfo2', 'object_datetime')

- Database:model (dbid:3)

sql comman is:update model..sysattributes set object_cinfo2 = null, object_datetime

= null where object_cinfo2 is not null or object_datetime is not null delete model..syscolumns where id = 21 and name in ('object_cinfo2', 'object_datetime')

- Database:sybsystemdb (dbid:31513)

sql comman is:update sybsystemdb..sysattributes set object_cinfo2 = null, object_datetime = null where object_cinfo2 is not null or object_datetime is not null delete sybsystemdb..syscolumns where id = 21 and name in ('object_cinfo2', 'object_datetime')

- Database:sybsystemprocs (dbid:31514)

sql comman is:update sybsystemprocs..sysattributes set object_cinfo2 = null, object_datetime = null where object_cinfo2 is not null or object_datetime is not null delete sybsystemprocs..syscolumns where id = 21 and name in ('object_cinfo2', 'object_datetime')

Executing downgrade step 25 [update :DBNAME:..syscomments set encrkeyid = null where encrkeyid is not null delete:DBNAME:..syscolumns where id = 6 and name = 'version' delete :DBNAME:..syscolumns where id = 6 and name ='encrkeyid'] for :

- Database:master (dbid:1)

sql comman is:update master..syscomments set encrkeyid = null where encrkeyid is not null delete master..syscolumns where id = 6 and name = 'version' delete master..syscolumns where id = 6 and name ='encrkeyid'

- Database:tempdb (dbid:2)

sql comman is:update tempdb..syscomments set encrkeyid = null where encrkeyid is not null delete tempdb..syscolumns where id = 6 and name = 'version' delete tempdb..syscolumns where id = 6 and name ='encrkeyid'

- Database:model (dbid:3)

sql comman is:update model..syscomments set encrkeyid = null where encrkeyid is not null delete model..syscolumns where id = 6 and name = 'version' delete model..syscolumns where id = 6 and name ='encrkeyid'

- Database:sybsystemdb (dbid:31513)

sql comman is:update sybsystemdb..syscomments set encrkeyid = null where encrkeyid is not null delete sybsystemdb..syscolumns where id = 6 and name = 'version' delete sybsystemdb..syscolumns where id = 6 and name ='encrkeyid'

- Database:sybsystemprocs (dbid:31514)

sql comman is:update sybsystemprocs..syscomments set encrkeyid = null where encrkeyid is not null delete sybsystemprocs..syscolumns where id = 6 and name = 'version' delete sybsystemprocs..syscolumns where id = 6 and name ='encrkeyid'

Executing downgrade step 26 [delete statistics syssrvroles(password) if exists (select 1 from syssrvroles where password is not null) begin print "Truncating password and locking following role(s)" select name from syssrvroles where password is not null update syssrvroles set password = null, status = (status | @lockrole)

where password is not null end update syscolumns set length = 30 where id = object_id('syssrvroles') and name = 'password' update syssrvroles set locksuid = null, lockreason = null, lockdate = null where locksuid is not null or lockreason is not null or lockdate is not null delete syscolumns where id = object_id('syssrvroles') and name in ('locksuid', 'lockreason', 'lockdate')] for :

- Database:master (dbid:1)

sql comman is:delete statistics syssrvroles(password) if exists (select 1 from syssrvroles where password is not null) begin print "Truncating password and locking following role(s)" select name from syssrvroles where password is not null update syssrvroles set password = null, status = (status | @lockrole) where password is not null end update syscolumns set length = 30 where id = object_id('syssrvroles') and name = 'password' update syssrvroles set locksuid = null, lockreason = null, lockdate

= null where locksuid is not null or lockreason is not null or lockdate is not null delete syscolumns where id = object_id('syssrvroles') and name in ('locksuid', 'lockreason', 'lockdate')

Truncating password and locking following role(s) name

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

doctor_role

Executing downgrade step 27 [delete sysattributes where class = 35 delete sysattributes where class = 39 update syslogins set lpid = null, crsuid = null where lpid is not null or crsuid is not null delete syscolumns where id = object_id('syslogins') and name in ('lpid', 'crsuid') delete syslogins where (status

& @lp_status) = @lp_status update syslogins set status = status & ~(@exempt_lock) where (status & @exempt_lock) = @exempt_lock] for :

- Database:master (dbid:1)

sql comman is:delete sysattributes where class = 35 delete sysattributes where class

= 39 update syslogins set lpid = null, crsuid = null where lpid is not null or crsuid is not null delete syscolumns where id = object_id('syslogins') and name in ('lpid', 'crsuid') delete syslogins where (status & @lp_status) = @lp_status update syslogins set status = status & ~(@exempt_lock) where (status & @exempt_lock) = @exempt_lock

Executing downgrade step 998 [declare @d int, @stat4 int select @stat4=convert(int, dbinfo_get('master','status4')) select @d=dbinfo_update(1, 'status4', 32 | @stat4)] for :

- Database:master (dbid:1)

sql comman is:declare @d int, @stat4 int select @stat4=convert(int, dbinfo_get('master','status4')) select @d=dbinfo_update(1, 'status4', 32 | @stat4)

Executing downgrade step 999 [declare @d int select @d=dbinfo_update(@dbid, 'ASEvers', 15500)] for :

- Database:master (dbid:1)

sql comman is:declare @d int select @d=dbinfo_update(@dbid, 'ASEvers', 15500)

- Database:tempdb (dbid:2)

sql comman is:declare @d int select @d=dbinfo_update(@dbid, 'ASEvers', 15500)

- Database:model (dbid:3)

sql comman is:declare @d int select @d=dbinfo_update(@dbid, 'ASEvers', 15500)

- Database:sybsystemdb (dbid:31513)

sql comman is:declare @d int select @d=dbinfo_update(@dbid, 'ASEvers', 15500)

- Database:sybsystemprocs (dbid:31514)

sql comman is:declare @d int select @d=dbinfo_update(@dbid, 'ASEvers', 15500)

(return status = 0)

用法 用来还原为以前安装的 Adaptive Server 15.0.x 版。目前不能还原为

Adaptive Server 15.0.2。

角色口令和 sp_downgrade

降级到 15.7 之前版本的 Adaptive Server 时，sp_downgrade 系统过程会内 部调用 sp_passwordpolicy [ prepare | downgrade ] 和要降级的 Adaptive Server 版本号。

当您执行 sp_downgrade 时， Adaptive Server 会执行下列任务：

• 截断角色口令并锁定角色。

• 删除 sysattributes 中类 35 下的新增属性。

• 删除 sysattributes 中的新增的类 35。

• 从 syssrvroles 中删除新的 locksuid、 lockreason 和 lockdate 列。

注释 将 Adaptive Server 15.7 版本降级到 15.0.2 之前版本时，角色和登 录口令都会降级。但是，降级到 15.0.2 版本时， Adaptive Server 会仅截 断和锁定角色口令。

有关降级角色口令的详细信息，请参见所用平台安装指南中的降级部分。

权限 对 sp_downgrade 的权限检查因您的细化权限设置而异。

细化权限已启用 在启用细化权限的情况下，您必须是具有 sa_serverprivs_role 和 sybase_ts_role

的用户。

细化权限已禁用 在禁用细化权限的情况下，您必须是同时具有 sso_role、 sa_role 和

sybase_ts_role 的用户。

< 上一个 | 内容 | 下一步 >