Last modified on 17 September 2008, at 16:55

Migrate master database from ASE 12.5 to 15.0.2

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."