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.
The script below will reverse-engineer the logins, passwords and roles on the existing 12.5 server and create a sql script. The sql script will then be applied at the 15.0.2 server.
#!/bin/sh
if [ $# != 7 ]
then
echo "Usage: $0 <source> <source-login> <source-passwrd> <target> <target-login> <target-password> <interfaces file>"
exit 1
fi
SOURCE="$1"
SOURCE_USER="$2"
SOURCE_PWD="$3"
TARGET="$4"
TARGET_USER="$5"
TARGET_PWD="$6"
INTERFACES="$7"
echo "** Testing connectivity to servers"
isql -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -I ${INTERFACES} <<EOF > /tmp/$$.check
EOF
COUNT=`grep CT-LIBRARY /tmp/$$.check | wc -l`
if [ ${COUNT} -gt 0 ]
then
cat /tmp/$$.check
rm -f /tmp/$$.*
echo "** Could not connect to Source server"
exit 1
fi
isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -I ${INTERFACES} <<EOF > /tmp/$$.check
EOF
COUNT=`grep CT-LIBRARY /tmp/$$.check | wc -l`
if [ ${COUNT} -gt 0 ]
then
cat /tmp/$$.check
rm -f /tmp/$$.*
echo "** Could not connect to Target server"
exit 1
fi
rm -f /tmp/$$.check
echo "** Connectivity ok"
isql -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -I ${INTERFACES} -w800 -b <<EOF > logins.sql
set nocount on
go
declare lees cursor for
select suid,name,dbname,password,language,fullname
from master..syslogins
where suid > 2
order by suid
go
declare @suid int,
@name varchar(30),
@dbname varchar(30),
@password varbinary(30),
@language varchar(30),
@fullname varchar(30),
@value int
open lees
fetch lees into
@suid, @name, @dbname, @password, @language, @fullname
while @@sqlstatus = 0
begin
print "exec sp_addlogin '%1!','migrate123',null,null,'%2!'",
@name, @fullname
print "update syslogins set suid = %1! where name = '%2!'",
@suid, @name
if @dbname != "master"
print "update syslogins set dbname = '%1!' where name = '%2!'",
@dbname, @name
print "update syslogins set password = %1! where name = '%2!'",
@password, @name
if exists(
select 1
from master..sysattributes
where object = @suid
and object_type = "PS"
and attribute = 0
and object_cinfo = "login"
and class = 14)
begin
select @value = int_value
from master..sysattributes
where object = @suid
and object_type = "PS"
and attribute = 0
and object_cinfo = "login"
and class = 14
print "exec sp_modifylogin '%1!','passwd expiration','%2!'",
@name, @value
end
if exists(
select 1
from master..sysattributes
where object = @suid
and object_type = "PS"
and attribute = 1
and object_cinfo = "login"
and class = 14)
begin
select @value = int_value
from master..sysattributes
where object = @suid
and object_type = "PS"
and attribute = 1
and object_cinfo = "login"
and class = 14
print "exec sp_modifylogin '%1!','min passwd length','%2!'",
@name, @value
end
if exists(
select 1
from master..sysattributes
where object = @suid
and object_type = "PS"
and attribute = 2
and object_cinfo = "login"
and class = 14)
begin
select @value = int_value
from master..sysattributes
where object = @suid
and object_type = "PS"
and attribute = 2
and object_cinfo = "login"
and class = 14
print "exec sp_modifylogin '%1!','max failed_logins','%2!'",
@name, @value
end
print "go"
fetch lees into
@suid, @name, @dbname, @password, @language, @fullname
end
close lees
go
deallocate cursor lees
go
select "exec sp_adduser '" + name + "'"
from master..sysusers su
where su.suid > 2
and exists(
select 1
from master..syslogins sl
where sl.suid = su.suid)
go
print "go"
go
EOF
echo "** Script for logins created"
isql -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -I ${INTERFACES} -w800 -b <<EOF > roles.sql
set nocount on
go
declare lees cursor for
select srid, name, password, status
from master..syssrvroles
where srid >= 32
go
declare @srid int,
@name varchar(30),
@password varbinary(30),
@status smallint,
@value int
open lees
fetch lees into
@srid, @name, @password, @status
while @@sqlstatus = 0
begin
print "create role %1!",
@name
if @password != null
print "update syssrvroles set password = %1! where name = '%2!'",
@password, @name
if @status & 2 = 2
print "alter role %1! lock",
@name
if exists(
select 1
from master..sysattributes
where object = @srid
and object_type = "PS"
and attribute = 0
and object_cinfo = "role"
and class = 14)
begin
select @value = int_value
from master..sysattributes
where object = @srid
and object_type = "PS"
and attribute = 0
and object_cinfo = "role"
and class = 14
print "alter role %1! set passwd expiration %2!",
@name, @value
end
print "go"
fetch lees into
@srid, @name, @password, @status
end
close lees
go
deallocate cursor lees
go
declare lees cursor for
select suser_name(suid), role_name(srid), status
from master..sysloginroles
where suid > 2
go
declare @suser_name varchar(30),
@role_name varchar(30),
@status smallint
open lees
fetch lees into
@suser_name, @role_name, @status
while @@sqlstatus = 0
begin
print "grant role %1! to %2!",
@role_name, @suser_name
if @status = 1
print "exec sp_modifylogin '%1!','add default role','%2!'",
@suser_name, @role_name
print "go"
fetch lees into
@suser_name, @role_name, @status
end
close lees
go
deallocate cursor lees
go
select "grant role " + role_name(object_info1) + " to " + role_name(object)
from sysattributes
where object_type = "UR"
and attribute = 2
and object_cinfo = null
and class = 8
go
print "go"
go
EOF
echo "** Script for roles created"
echo "Press <ENTER> to continue (delete values at target server)" ; read OK
isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -I ${INTERFACES} <<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..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
EOF
echo "Press <ENTER> to continue (apply generated sql at target server)" ; read OK
isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -I ${INTERFACES} < logins.sql
isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -I ${INTERFACES} < roles.sql
isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -I ${INTERFACES} <<EOF
sp_configure "allow updates to system tables",0
go
EOF
rm -f logins.sql roles.sql
echo "Migration script done."
