(master database only) Validates readiness for downgrade to an earlier 15.0.x release. Also downgrades the system catalog changes that Adaptive Server 15.0.2 modified.
sp_downgrade @cmd = {'prepare' | 'downgrade' | 'help',}
@toversion = 'n'[, @verbose = 0 | 1][, @override = 0 | 1]
is use first to validate readiness of Adaptive Server 15.0.2 for downgrade.
is used after prepare parameter when ready to proceed with the act of downgrading to a previously installed 15.x version of Adaptive Server. Server must be in single user mode. (started with -m option)
can be 15.0 or 15.0.1 written “150” or “15.0”, “1501” or “15.0.1”
specifies verbosity. Valid options are 0 (for no) or 1 (for yes).
specifies whether to skip databases that are not writable at this time. Valid options are 0 (for no) or 1 (for yes).
This is an example of the 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.
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)
Downgrades Adaptive Server 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)
Use to revert to the previously installed Adaptive Server 15.0.x release. At this time it is not possible to revert to Adaptive Server 15.0.2.
When downgrading to a version of Adaptive Server earlier than 15.7, the sp_downgrade system procedure internally calls sp_passwordpolicy [ prepare | downgrade ] along with the Adaptive Server version number to downgrade.
When you execute sp_downgrade, Adaptive 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.
When you downgrade Adaptive Server 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, Adaptive 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.
