Migrate master database from ASE 12.5 to 15.0

Migration of the master database from a 12.5 server into a 15.0 server can be done by extracting the required information from the old server and inserting it into the new 15.0 server.

Below you will find a script that does this for you.

#!/bin/sh

if [ $# != 6 ]
then
  echo "Usage: $0 <source 12.5 server> <source-login> <source-passwrd> <target 15.0 server> <target-login> <target-password>"
  exit 1
fi

SOURCE="$1"
SOURCE_USER="$2"
SOURCE_PWD="$3"
TARGET="$4"
TARGET_USER="$5"
TARGET_PWD="$6"

isql -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} <<EOF
use tempdb
go
create view my_syslogins
as
select * from master..syslogins where suid > 2
go
create view my_remotelogins
as
select * from master..sysattributes where object_type = "EL"
go
create view my_userroles
as
select * from master..sysattributes where object_type = "UR"
go
create view my_sysloginroles
as
select * from master..sysloginroles where suid > 2
go
create view my_sysroles
as
select * from master..sysroles where id >= 32
go
create view my_sysservers
as
select * from master..sysservers
where srvname != "SYB_BACKUP"
and srvclass != 0 -- local server
go
create view my_syssrvroles
as
select * from master..syssrvroles where srid >= 32
go
create view my_sysusers_1
as
select * from master..sysusers su
where su.suid > 2
and exists(select 1 from master..syslogins sl where   sl.suid = su.suid)
go
create view my_sysusers_2
as
select su.* from master..sysusers su, master..sysroles sr
where su.uid = sr.lrid and sr.id >= 32
go
EOF

echo "Press <ENTER> to continue" ; read OK

bcp tempdb..my_syslogins out syslogins.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -n
bcp master..sysalternates out sysalternates.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -n
bcp master..sysremotelogins out sysremotelogins.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -n
bcp tempdb..my_remotelogins out remotelogins.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -c
bcp tempdb..my_userroles out userroles.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -c
bcp tempdb..my_sysloginroles out sysloginroles.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -n
bcp tempdb..my_sysroles out sysroles.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -n
bcp tempdb..my_sysservers out sysservers.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -n
bcp tempdb..my_syssrvroles out syssrvroles.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -n
bcp tempdb..my_sysusers_1 out sysusers_1.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -n
bcp tempdb..my_sysusers_2 out sysusers_2.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -n

echo "Press <ENTER> to continue" ; read OK

isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} <<EOF
sp_configure "allow updates to system tables",1
go
delete master..syslogins where suid > 2
go
delete master..sysalternates
go
delete master..sysremotelogins
go
delete master..sysattributes where object_type in ("EL","UR")
go
delete master..sysloginroles where suid > 2
go
delete master..sysroles where id >= 32
go
delete master..sysservers
where srvname != "SYB_BACKUP"
and srvclass != 0 -- local server
go
delete master..syssrvroles where srid >= 32
go
delete master..sysusers from master..sysusers su
where su.suid > 2
and not exists(select 1 from master..syslogins sl where sl.suid = su.suid)
go
delete master..sysusers from master..sysusers su, master..sysroles sr
where su.uid = sr.lrid
and sr.id >= 32
go
create table tempdb..my_sysusers_2(
suid int not null,
uid int not null,
gid int not null,
name sysname not null,
environ varchar(255) null)
go
EOF

echo "Press <ENTER> to continue" ; read OK

bcp master..syslogins in syslogins.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -n
bcp master..sysalternates in sysalternates.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -n
bcp master..sysremotelogins in sysremotelogins.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -n
bcp master..sysattributes in remotelogins.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -c
bcp master..sysattributes in userroles.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -c
bcp master..sysloginroles in sysloginroles.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -n
bcp master..sysroles in sysroles.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -n
bcp master..sysservers in sysservers.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -n
bcp master..syssrvroles in syssrvroles.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -n
bcp master..sysusers in sysusers_1.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -n
bcp tempdb..my_sysusers_2 in sysusers_2.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -n

echo "Press <ENTER> to continue" ; read OK

isql -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} <<EOF
use tempdb
go
drop view my_syslogins, my_remotelogins, my_userroles, my_sysloginroles, my_sysroles, my_sysservers, my_syssrvroles,
my_sysusers_1, my_sysusers_2
go
EOF

echo "Press <ENTER> to continue" ; read OK

isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} <<EOF
use master
go
declare @offset int
if exists(select 1 from master..sysusers m, tempdb..my_sysusers_2 t
  where m.uid = t.uid)
  select @offset = (select max(uid) from master..sysusers)
  - (select min(uid) from tempdb..my_sysusers_2)
  + 1
else
  select @offset = 0

insert into master..sysusers
select suid,uid + @offset,gid + @offset, name, environ
from tempdb..my_sysusers_2
go
drop table tempdb..my_sysusers_2
go
sp_configure "allow updates to system tables",0
go
EOF

echo "Press <ENTER> to continue" ; read OK

rm -f syslogins.dat sysalternates.dat sysremotelogins.dat remotelogins.dat userroles.dat sysloginroles.dat
rm -f sysroles.dat sysservers.dat syssrvroles.dat sysusers_1.dat sysusers_2.dat

isql -U${SOURCE_USER} -b -P"${SOURCE_PWD}" -S${SOURCE} <<EOF > /var/tmp/modifylogin
set nocount on
go
select "exec sp_modifylogin '" + rtrim(suser_name(object)) + "','passwd expiration','" +
 convert(varchar(30),int_value) + "'"
 from master..sysattributes where object_type = "PS" and attribute = 0 and object_cinfo = "login"
 and class = 14
go
select "exec sp_modifylogin '" + rtrim(suser_name(object)) + "','min passwd length','" +
 convert(varchar(30),int_value) + "'"
 from master..sysattributes where object_type = "PS" and attribute = 1 and object_cinfo = "login"
 and class = 14
go
select "exec sp_modifylogin '" + rtrim(suser_name(object)) + "','max failed_logins','" +
 convert(varchar(30),int_value) + "'"
 from master..sysattributes where object_type = "PS" and object_cinfo = "login" and attribute = 2
 and class = 14
go
EOF

echo "go" >> /var/tmp/modifylogin

isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} < /var/tmp/modifylogin

rm /var/tmp/modifylogin

That should just about cover most of the data.