Migrate master database from ASE 12.5 to 15.0.2

From SybaseWiki
Revision as of 15:15, 8 February 2008 by Psap (Talk | contribs)

Jump to: navigation, search

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