sp_downgrade

(master database only) Validates readiness for downgrade to a version of SAP ASE earlier than the current version you are running. Also downgrades the system catalog changes that were modified with the current version of SAP ASE.

Syntax

sp_downgrade @cmd = {'prepare' | 'downgrade' | 'help',}
	@toversion = 'n'[, @verbose = 0 | 1][, @override = 0 | 1]

Parameters

prepare – is used first to validate readiness of SAP ASE 15.0.2 for downgrade.
downgrade – is used after prepare parameter when ready to proceed with the act of downgrading to a previously installed 15.x version of SAP ASE. Server must be in single user mode. (started with -m option)
@toversion – can be 15.0, 15.0.1, or 15.0.2. The format can be written as “150” or “15.0”, “1501” or “15.0.1”
@verbose – specifies verbosity. Valid options are 0 (for no) or 1 (for yes).
@override – specifies whether to skip databases that are not writable at this time. Valid options are 0 (for no) or 1 (for yes).

Examples

Example 1 – Shows output from running 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:2011/06/29 02:16:44.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:2011/06/29 02:16:45.43 server  ASE downgrade completed.

Example 2 – Checks the databases for downgrade readiness:

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)

Example 3 – Downgrades SAP ASE from version 15.7 to 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 sysconfigures where config = 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)

Usage

Use to revert to the previously installed SAP ASE 15.0.x release.

When downgrading to a version of SAP ASE earlier than 15.7, the sp_downgrade system procedure internally calls sp_passwordpolicy [ prepare | downgrade ] along with the SAP ASE version number to downgrade.

When you execute sp_downgrade, the SAP ASE server performs these tasks:

Truncates role passwords and locks roles.
Removes newly added attributes in sysattributes under class 35.
Removes newly added class 35 in sysattributes.
Removes the new locksuid, lockreason, and lockdate columns from syssrvroles.

Note: When you downgrade SAP ASE version 15.7 to a pre-15.0.2 version, both role and login passwords are downgraded. When downgrading to version 15.0.2, however, the SAP ASE server truncates and locks only role passwords.

For more information about downgrading role passwords, see the downgrade section of the installation guide for your platform.

Permissions

The permission checks for sp_downgrade differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, you must be a user with sa_serverprivs_role and sybase_ts_role.
Disabled	With granular permissions disabled, you must be a user with sso_role and sa_role and sybase_ts_role.