Sybase database refresh step by step

In this article, we are going to see step by step procedure of Sybase database refresh.

Steps to perform refresh:

1. Prerequisites.
2. Backup and SCP
3. Load
4. Verification

Source Information:

Source Database Servername: LOCALHOST(Sybase server name)
IP: 192.168.152.130
Source Database name: r2schools

Target Information:

Tartget Database Servername: DEV01(Sybase server name)
IP: 192.168.152.135
Target Database name: r2dev

Step1: Prerequisites to perform Database Refresh:

1. Target database size should same or greater than the source database size.
2. User must have either sa role or oper_role
3. SSH Keys must be enabled.
Verify the database sizes on source databases server:

sp_helpdb r2schools
go

Verify the database sizes on target databases server:

sp_helpdb r2dev
go

Step2: Backup and scp the source database

a. Dump Database:
b. SCP from source to target server

a. Take the Dump of source Database ‘r2schools’:

dump database r2schools to /opt/sybase/r2schools_01_01_2019.dmp'

b. Copy above dump file to target server by using scp:

scp -p r2schools_01_01_2019.dmp r2schools@192.168.152.135:/opt/sybase/dump/

Step3:Load the database

In this step we will perform following steps on target database server.

1. BCP out
2. Load the database with source database server(production) dumps.
3. Bring database online
4. Delete the bcp out tables of target database(always use database..sys*****)
6. BCP in
7. Verify the Load operation

1. bcp out of tables: sysusers, sysalternates and sysprotects.

Run the below commands from linex shell:

bcp r2dev..sysusers out /opt/sybase/dump/sysusers_01_01_2019 -Usa -SDEV01 -Padmin@123 -c -Y
bcp r2dev..sysalternates out /opt/sybase/dump/sysalternates_01_01_2019 -Usa -SDEV01 -Padmin@123 -c -Y
bcp r2dev..sysprotects out /opt/sybase/dump/sysprotects_01_01_2019 -Usa -SDEV01 -Padmin@123 -c -Y

or

For 64 binary

bcp64 r2dev..sysusers out /opt/sybase/dump/sysusers_01_01_2019 -Usa -SDEV01 -Padmin@123 -c -Y
bcp64 r2dev..sysalternates out /opt/sybase/dump/sysalternates_01_01_2019 -Usa -SDEV01 -Padmin@123 -c -Y
bcp64 r2dev..sysprotects out /opt/sybase/dump/sysprotects_01_01_2019 -Usa -SDEV01 -Padmin@123 -c -Y

2. Load database with source(production) database dump(s).

 load database r2dev from '/opt/sybase/dump/r2schools_01_01_2019.dmp'

3. Bring database online

online database r2schools
go

4. Delete three tables from target database. Tables are sysusers, sysalternates, sysprotects.

use r2dev
go

Run the below command to remove system catalogs(tables). Otherwise, it will through error.

sp_configure "allow updates to system tables",1
go

delete r2dev..sysusers
go
delete r2dev..sysalternates
go
delete r2dev..sysprotects
go

5. BCP in

Exit from isql the console run the below commands from linex shell

bcp r2dev..sysusers out /opt/sybase/dump/sysusers_01_01_2019 -Usa -SDEV01 -Padmin@123 -c -Y
bcp r2dev..sysalternates out /opt/sybase/dump/sysalternates_01_01_2019 -Usa -SDEV01 -Padmin@123 -c -Y
bcp r2dev..sysprotects out /opt/sybase/dump/sysprotects_01_01_2019 -Usa -SDEV01 -Padmin@123 -c -Y

or
For 64 binary

bcp64 r2dev..sysusers out /opt/sybase/dump/sysusers_01_01_2019 -Usa -SDEV01 -Padmin@123 -c -Y
bcp64 r2dev..sysalternates out /opt/sybase/dump/sysalternates_01_01_2019 -Usa -SDEV01 -Padmin@123 -c -Y
bcp64 r2dev..sysprotects out /opt/sybase/dump/sysprotects_01_01_2019 -Usa -SDEV01 -Padmin@123 -c -Y

4. Verify the Refresh completed or not
Go to install directory: /opt/sap/ASE-16/install/

cat DEV01_BS | grep -i 'Load is complete'

For video tutorials, please visit below youtube and subscribe if it is helpful.