Migrate master database from ASE 12.5 to 15.0.2
From SybaseWiki
Migration of the master database from a 12.5 server into a 15.0.2 server can be done by extracting the required information from the old server and inserting it into the new 15.0.2 server.
Below you will find a script that does this for you.
#!/bin/sh
if [ $# != 6 ]
then
echo "Usage: $0 <source> <source-login> <source-passwrd> <target> <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
if exists(
select 1 from sysobjects where name = "my_syslogins")
drop view my_syslogins
go
create view my_syslogins
as
select *,null as lastlogindate,getdate() as crdate,null as locksuid,null as lockreason,null as lockdate
from master..syslogins where suid > 2
go
if exists(
select 1 from sysobjects where name = "my_remotelogins")
drop view my_remotelogins
go
create view my_remotelogins
as
select * from master..sysattributes where object_type = "EL"
go
if exists(
select 1 from sysobjects where name = "my_userroles")
drop view my_userroles
go
create view my_userroles
as
select * from master..sysattributes where object_type = "UR"
go
if exists(
select 1 from sysobjects where name = "my_sysloginroles")
drop view my_sysloginroles
go
create view my_sysloginroles
as
select * from master..sysloginroles where suid > 2
go
if exists(
select 1 from sysobjects where name = "my_sysroles")
drop view my_sysroles
go
create view my_sysroles
as
select * from master..sysroles where id >= 32
go
if exists(
select 1 from sysobjects where name = "my_sysservers")
drop view my_sysservers
go
create view my_sysservers
as
select *,null as srvstatus2
from master..sysservers
where srvname != "SYB_BACKUP"
and srvname not like "%_migrate"
and srvclass != 0 -- local server
go
if exists(
select 1 from sysobjects where name = "my_syssrvroles")
drop view my_syssrvroles
go
create view my_syssrvroles
as
select * from master..syssrvroles where srid >= 32
go
if exists(
select 1 from sysobjects where name = "my_sysusers_1")
drop view my_sysusers_1
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
if exists(
select 1 from sysobjects where name = "my_sysusers_2")
drop view my_sysusers_2
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} -c
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 srvname not like "%_migrate"
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
if exists (select 1 from tempdb..sysobjects where name = "my_sysusers_2")
drop table tempdb..my_sysusers_2
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} -c
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 \
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
cat /var/tmp/modifylogin
isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} < /var/tmp/modifylogin
rm /var/tmp/modifylogin
