View source for 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."

[[Category:ASE]]