Consider the following scenario,In this case we are going to performed database refresh from prod to dev check the below steps.
Production server instance name :-ASEPPROD01
Database name:-Prodb
Development server instance name:-ASEDEV02
Database name:-Devdb
Steps Involved in Database refresh:
Dump Database:
SCP
BCP (out)
LOAD Database
Online Database
Delete sysusers and sysalternates
BCP (in)
1)Dump Database: Dump database is used to make backup copy of the entire database.
dump database Prodb to ‘/var/opt/sybase/dump/ASEPPROD01/Prodb01022017.dmp’
go
2)SCP: The scp command used to copy files dump files from source server ASEPPROD01 to target Server ASEDEV02.The scp command uses ssh for data transfer.
scp -p ‘Prod0001:/var/opt/sybase/dump/ASEPPROD01/Prodb01022017.dmp’ ‘Dev0001:/var/opt/sybase/dump/ASEDEV02/’
3)This is OS level command used take copy of below system tabele sysusers,sysalternates and sysprotects in order to sync the user alias and permission etc
bcp Devdb..sysusers out ‘/var/opt/sybase/DUMP/ASEDEV02/Devdbsysusers.out’ -Usa -SASEDEV02 -PSybase123 -c -t ‘<|>’
bcp Devdb..sysalternates out ‘/var/opt/sybase/DUMP/ASEDEV02/Devdbsysalternates.out’ -Usa -SASEDEV02 -PSybase123 -c -t ‘<|>’
4)LOAD Database
Now login to server isql -Usa -SASEDEV02 -PSybase123 -w2000
use master
go
load database Devdb from “/var/opt/sybase/dump/ASEDEV02/Prodb01022017.dmp”
go
use Devdb
go
select @@servername,getdate()
go
Online Database Devdb
go
5)Once database is online then delete entries from below table so that we can performed bcp in operation
use Devdb
go
delete from Devdb..sysusers >1
go
delete from Devdb..sysalternates
go
6)Performed bcp in
bcp Devdb..sysusers in ‘/var/opt/sybase/DUMP/ASEDEV02/Devdbsysusers.out’ -Usa -SASEDEV02 -PSybase123 -c -t ‘<|>’ -b1
bcp Devdb..sysalternates in ‘/var/opt/sybase/DUMP/ASEDEV02/Devdbsysalternates.out’ -Usa -SASEDEV02 -PSybase123 -c -t ‘<|>’
7)How to check db refresh completed successfully.
i)MDA table master..monOpenDatabases
Select DBID,BackupInProgress,LastBackupFailed,TransactionLogFull,DBName,BackupStartTime,LastCheckpointTime,LastTranLogDumpTime from master..monOpenDatabases
ii)check backup errorlog
cat errorlog_ASEDEV02_BACKUP | grep -i ‘LOAD is complete’
Excellent Document
LikeLike
please upload installation and patch upgrade docs
LikeLike
In general we use condition for sysusers suid>1 , is that okay if not used
LikeLike
Very useful and informative one
LikeLike
Hi, What is the target dtabase(Dev) in this case does not have sufficient space and needs to be extended? I am newbie to Sybase and trying to understand the refresh. Also say the databases are having data and log devices that either should be added or extended, does the order in which both are extended matters? For example if in Prod database sp_helpdb shows data1,log1,data2,log2,data1,data1,data2,log1,log2 and at Dev, its data1,log1,data2,log2,data1, can we extend data and log in any order at Dev or it should be like first data and then index or vice versa? If so why order is important?
LikeLike
Hello Mate ,
Yes you need to extend the target DB space if its not matching to your source one.
Yes you ca extend data and log in any order but size should be matched.
Order is not Imp you need to have Data +log size is equal to size of source database.
if you want to learn more in details I am going to published details on YouTube channel SimplyTech4U
LikeLike