We are going to perform the database refresh from prod to test by following the below steps.
Production server instance name :-ASEPPRD01
Source database name:-PRD
Test server instance name:-ASETST01
Target database name:-TST
Steps Involved in Database refresh:
Dump Database:
SCP
BCP (out)
LOAD database
Online database
Delete bcp out tables
BCP (in)
1) Dump Database
Dump database is used to make backup copy of the entire database.
Login source database server(ASEPPRD01)
isql -Usa -SASEPRD01 -X -w4000
password:
dump database PRD to '/sybase/dump/ASEPRD01/PRD22072022.dmp'
go
2)SCP
This command is used to copy files from source server ASEPRD01 to target Server ASETST01.
Login to the PRD host
cd /sybase/dump/ASEPRD01
scp PRD22072022.dmp sybatst@10.15.8.11:/sybase/dump/ASETST01/
3)BCP out
This is OS level command used take copy of below system tables sysusers,sysalternates and sysprotects in order to sync the user alias and permission etc.
Login to target database server(ASETST01)
bcp TST..sysusers out ‘/sybase/BCP/ASETST01/TST_sysusers.out’ -Usa -SASETST01 -c -n
bcp TST..sysalternates out ‘/sybase/BCP/ASETST01/TST_sysalternates.out’ -Usa -SASETST01 -c -n
bcp TST..sysprotects out ‘/sybase/BCP/ASETST01/TST_sysprotects.out’ -Usa -SASETST01 -c -n
4)Load database
login to the TST server using isql
isql -Usa -SASETST01 -X -w2000
password:
use master
go
load database TST from "/sybase/dump/ASETST01/PRD22072022.dmp"
go
Online database TST
go
use TST
go
5)Delete bcp out tables
Once database is online then delete entries from below table so that we can perform bcp in operation.
use TST
go
select @@servername,getdate()
go
delete from TST..sysusers
go
delete from TST..sysprotects
go
delete from TST..sysalternates
go
6)BCP in
bcp TST..sysusers in '/sybase/BCP/ASETST01/TST_sysusers.out' -Usa -SASETST01 -c -n
bcp TST..sysalternates in '/sybase/BCP/ASETST01/TST_sysalternates.out' -Usa -SASETST01 -c -n
bcp TST..sysprotects in '/sybase/BCP/ASETST01/TST_sysprotects.out' -Usa -SASETST01 -c -n
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 log
cd $SYBASE/ASE*/install
cat ASETST01_BS.log | grep -i 'LOAD is complete'