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 -t ‘|’

/* -c -t ‘|’ this delimitor  is needed inorder avoide common library error */

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

bcp Devdb..sysprotects out ‘/var/opt/sybase/DUMP/ASEDEV02/Devdbsysprotects.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 master

go

sp_configure “allow updates”,1

go

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 -t ‘|’

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

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s