How to performed database refresh step by step in SYABSE ASE |Steps invloved in database dump and load |step by step database refresh in SYABSE ASE

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 bcp out tables
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 -n

bcp Devdb..sysalternates out ‘/var/opt/sybase/DUMP/ASEDEV02/Devdbsysalternates.out’ -Usa -SASEDEV02 -PSybase123 -c -n

bcp Devdb..sysprotects out ‘/var/opt/sybase/DUMP/ASEDEV02/Devdbsysprotects.out’ -Usa -SASEDEV02 -PSybase123 -c -n

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
go
delete from Devdb..sysprotects
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 -n

bcp Devdb..sysalternates in ‘/var/opt/sybase/DUMP/ASEDEV02/Devdbsysalternates.out’ -Usa -SASEDEV02 -PSybase123 -c -n

bcp Devdb..sysprotects in ‘/var/opt/sybase/DUMP/ASEDEV02/Devdbsysprotects.out’ -Usa -SASEDEV02 -PSybase123 -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 errorlog

cat errorlog_ASEDEV02_BACKUP | grep -i ‘LOAD is complete’

Sybase ASE Replication Setup click here