http://www.petersap.nl/SybaseWiki/index.php?title=Migrate_master_database_from_ASE_12.5_to_15.0.2&feed=atom&action=history
Migrate master database from ASE 12.5 to 15.0.2 - Revision history
2024-03-29T08:29:38Z
Revision history for this page on the wiki
MediaWiki 1.24.2
http://www.petersap.nl/SybaseWiki/index.php?title=Migrate_master_database_from_ASE_12.5_to_15.0.2&diff=1821&oldid=prev
Psap at 15:55, 17 September 2008
2008-09-17T15:55:32Z
<p></p>
<a href="http://www.petersap.nl/SybaseWiki/index.php?title=Migrate_master_database_from_ASE_12.5_to_15.0.2&diff=1821&oldid=1783">Show changes</a>
Psap
http://www.petersap.nl/SybaseWiki/index.php?title=Migrate_master_database_from_ASE_12.5_to_15.0.2&diff=1783&oldid=prev
Psap at 14:15, 8 February 2008
2008-02-08T14:15:07Z
<p></p>
<table class='diff diff-contentalign-left'>
<col class='diff-marker' />
<col class='diff-content' />
<col class='diff-marker' />
<col class='diff-content' />
<tr style='vertical-align: top;'>
<td colspan='2' style="background-color: white; color:black; text-align: center;">← Older revision</td>
<td colspan='2' style="background-color: white; color:black; text-align: center;">Revision as of 14:15, 8 February 2008</td>
</tr><tr><td colspan="2" class="diff-lineno">Line 285:</td>
<td colspan="2" class="diff-lineno">Line 285:</td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>   </div></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>   </div></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>  rm /var/tmp/modifylogin</div></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>  rm /var/tmp/modifylogin</div></td></tr>
<tr><td colspan="2"> </td><td class='diff-marker'>+</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"></ins></div></td></tr>
<tr><td colspan="2"> </td><td class='diff-marker'>+</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">[[Category:ASE]]</ins></div></td></tr>
<!-- diff cache key website-mw_:diff:version:1.11a:oldid:1782:newid:1783 -->
</table>
Psap
http://www.petersap.nl/SybaseWiki/index.php?title=Migrate_master_database_from_ASE_12.5_to_15.0.2&diff=1782&oldid=prev
Psap at 14:14, 8 February 2008
2008-02-08T14:14:14Z
<p></p>
<p><b>New page</b></p><div>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. <br />
<br />
Below you will find a script that does this for you.<br />
<br />
#!/bin/sh<br />
<br />
if [ $# != 6 ]<br />
then<br />
echo "Usage: $0 <source> <source-login> <source-passwrd> <target> <target-login> <target-password>"<br />
exit 1<br />
fi<br />
<br />
SOURCE="$1"<br />
SOURCE_USER="$2"<br />
SOURCE_PWD="$3"<br />
TARGET="$4"<br />
TARGET_USER="$5"<br />
TARGET_PWD="$6"<br />
<br />
isql -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} <<EOF<br />
use tempdb<br />
go<br />
if exists(<br />
select 1 from sysobjects where name = "my_syslogins")<br />
drop view my_syslogins<br />
go<br />
create view my_syslogins<br />
as<br />
select *,null as lastlogindate,getdate() as crdate,null as locksuid,null as lockreason,null as lockdate<br />
from master..syslogins where suid > 2<br />
go<br />
if exists(<br />
select 1 from sysobjects where name = "my_remotelogins")<br />
drop view my_remotelogins<br />
go<br />
create view my_remotelogins<br />
as<br />
select * from master..sysattributes where object_type = "EL"<br />
go<br />
if exists(<br />
select 1 from sysobjects where name = "my_userroles")<br />
drop view my_userroles<br />
go<br />
create view my_userroles<br />
as<br />
select * from master..sysattributes where object_type = "UR"<br />
go<br />
if exists(<br />
select 1 from sysobjects where name = "my_sysloginroles")<br />
drop view my_sysloginroles<br />
go<br />
create view my_sysloginroles<br />
as<br />
select * from master..sysloginroles where suid > 2<br />
go<br />
if exists(<br />
select 1 from sysobjects where name = "my_sysroles")<br />
drop view my_sysroles<br />
go<br />
create view my_sysroles<br />
as<br />
select * from master..sysroles where id >= 32<br />
go<br />
if exists(<br />
select 1 from sysobjects where name = "my_sysservers")<br />
drop view my_sysservers<br />
go<br />
create view my_sysservers<br />
as<br />
select *,null as srvstatus2<br />
from master..sysservers <br />
where srvname != "SYB_BACKUP"<br />
and srvname not like "%_migrate"<br />
and srvclass != 0 -- local server<br />
go<br />
if exists(<br />
select 1 from sysobjects where name = "my_syssrvroles")<br />
drop view my_syssrvroles<br />
go<br />
create view my_syssrvroles<br />
as<br />
select * from master..syssrvroles where srid >= 32<br />
go<br />
if exists(<br />
select 1 from sysobjects where name = "my_sysusers_1")<br />
drop view my_sysusers_1<br />
go<br />
create view my_sysusers_1<br />
as<br />
select *<br />
from master..sysusers su<br />
where su.suid > 2<br />
and exists(<br />
select 1<br />
from master..syslogins sl<br />
where sl.suid = su.suid)<br />
go<br />
if exists(<br />
select 1 from sysobjects where name = "my_sysusers_2")<br />
drop view my_sysusers_2<br />
go<br />
create view my_sysusers_2<br />
as<br />
select su.*<br />
from master..sysusers su,<br />
master..sysroles sr<br />
where su.uid = sr.lrid<br />
and sr.id >= 32<br />
go<br />
EOF<br />
<br />
echo "Press <ENTER> to continue" ; read OK<br />
<br />
bcp tempdb..my_syslogins out syslogins.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -c<br />
bcp master..sysalternates out sysalternates.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -n<br />
bcp master..sysremotelogins out sysremotelogins.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -n<br />
bcp tempdb..my_remotelogins out remotelogins.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -c<br />
bcp tempdb..my_userroles out userroles.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -c<br />
bcp tempdb..my_sysloginroles out sysloginroles.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -n<br />
bcp tempdb..my_sysroles out sysroles.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -n<br />
bcp tempdb..my_sysservers out sysservers.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -n<br />
bcp tempdb..my_syssrvroles out syssrvroles.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -n<br />
bcp tempdb..my_sysusers_1 out sysusers_1.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -n<br />
bcp tempdb..my_sysusers_2 out sysusers_2.dat -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -n<br />
<br />
echo "Press <ENTER> to continue" ; read OK<br />
<br />
isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} <<EOF<br />
sp_configure "allow updates to system tables",1<br />
go<br />
<br />
delete master..syslogins<br />
where suid > 2<br />
go<br />
delete master..sysalternates<br />
go<br />
delete master..sysremotelogins<br />
go<br />
delete master..sysattributes where object_type in ("EL","UR")<br />
go<br />
delete master..sysloginroles where suid > 2<br />
go<br />
delete master..sysroles where id >= 32<br />
go<br />
delete master..sysservers<br />
where srvname != "SYB_BACKUP"<br />
and srvname not like "%_migrate"<br />
and srvclass != 0 -- local server<br />
go<br />
delete master..syssrvroles where srid >= 32<br />
go<br />
delete master..sysusers<br />
from master..sysusers su<br />
where su.suid > 2<br />
and not exists(<br />
select 1<br />
from master..syslogins sl<br />
where sl.suid = su.suid)<br />
go<br />
delete master..sysusers<br />
from master..sysusers su,<br />
master..sysroles sr<br />
where su.uid = sr.lrid<br />
and sr.id >= 32<br />
go<br />
if exists (select 1 from tempdb..sysobjects where name = "my_sysusers_2")<br />
drop table tempdb..my_sysusers_2<br />
go<br />
create table tempdb..my_sysusers_2(<br />
suid int not null,<br />
uid int not null,<br />
gid int not null,<br />
name sysname not null,<br />
environ varchar(255) null)<br />
go<br />
EOF<br />
<br />
echo "Press <ENTER> to continue" ; read OK<br />
<br />
bcp master..syslogins in syslogins.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -c<br />
bcp master..sysalternates in sysalternates.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -n<br />
bcp master..sysremotelogins in sysremotelogins.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -n<br />
bcp master..sysattributes in remotelogins.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -c<br />
bcp master..sysattributes in userroles.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -c<br />
bcp master..sysloginroles in sysloginroles.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -n<br />
bcp master..sysroles in sysroles.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -n<br />
bcp master..sysservers in sysservers.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -n<br />
bcp master..syssrvroles in syssrvroles.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -n<br />
bcp master..sysusers in sysusers_1.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -n<br />
bcp tempdb..my_sysusers_2 in sysusers_2.dat -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -n<br />
<br />
echo "Press <ENTER> to continue" ; read OK<br />
<br />
isql -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} <<EOF<br />
use tempdb<br />
go<br />
drop view my_syslogins, my_remotelogins, my_userroles, my_sysloginroles,<br />
my_sysroles, my_sysservers, my_syssrvroles, my_sysusers_1,<br />
my_sysusers_2<br />
go<br />
EOF<br />
<br />
echo "Press <ENTER> to continue" ; read OK<br />
<br />
isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} <<EOF<br />
use master<br />
go<br />
declare @offset int<br />
<br />
if exists(<br />
select 1<br />
from master..sysusers m,<br />
tempdb..my_sysusers_2 t<br />
where m.uid = t.uid)<br />
select @offset = (select max(uid)<br />
from master..sysusers)<br />
- (select min(uid)<br />
from tempdb..my_sysusers_2)<br />
+ 1<br />
else<br />
select @offset = 0<br />
<br />
insert into master..sysusers<br />
select suid,<br />
uid + @offset,<br />
gid + @offset,<br />
name,<br />
environ<br />
from tempdb..my_sysusers_2<br />
go<br />
drop table tempdb..my_sysusers_2<br />
go<br />
sp_configure "allow updates to system tables",0<br />
go<br />
EOF<br />
<br />
echo "Press <ENTER> to continue" ; read OK<br />
<br />
rm -f syslogins.dat sysalternates.dat sysremotelogins.dat remotelogins.dat userroles.dat sysloginroles.dat \<br />
sysroles.dat sysservers.dat syssrvroles.dat sysusers_1.dat sysusers_2.dat<br />
<br />
isql -U${SOURCE_USER} -b -P"${SOURCE_PWD}" -S${SOURCE} <<EOF > /var/tmp/modifylogin<br />
set nocount on<br />
go<br />
select "exec sp_modifylogin '" +<br />
rtrim(suser_name(object)) +<br />
"','passwd expiration','" +<br />
convert(varchar(30),int_value) +<br />
"'"<br />
from master..sysattributes<br />
where object_type = "PS"<br />
and attribute = 0<br />
and object_cinfo = "login"<br />
and class = 14<br />
go<br />
select "exec sp_modifylogin '" +<br />
rtrim(suser_name(object)) +<br />
"','min passwd length','" +<br />
convert(varchar(30),int_value) +<br />
"'"<br />
from master..sysattributes<br />
where object_type = "PS"<br />
and attribute = 1<br />
and object_cinfo = "login"<br />
and class = 14<br />
go<br />
select "exec sp_modifylogin '" +<br />
rtrim(suser_name(object)) +<br />
"','max failed_logins','" +<br />
convert(varchar(30),int_value) +<br />
"'"<br />
from master..sysattributes<br />
where object_type = "PS"<br />
and object_cinfo = "login"<br />
and attribute = 2<br />
and class = 14<br />
go<br />
EOF<br />
<br />
echo "go" >> /var/tmp/modifylogin<br />
<br />
cat /var/tmp/modifylogin<br />
<br />
isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} < /var/tmp/modifylogin<br />
<br />
rm /var/tmp/modifylogin</div>
Psap