Is there a way to dynamically copy a live database to a duplicate database without having to stop the Sybase16 engine

[Former Member]

Hi I would like to know if there is a way to dynamically copy a live database to a duplicate database without having to stop the Sybase16 engine. There is a need to have a copy of the production database in another (training) database, I don't want to stop the Sybase engine to copy database1 to database2. Is there a tool or script which will allow me to copy one to another automatically

cheers John

[former_member187136]

Hi John,

Is the above solutions worked out ?

Regards

Kiran Kumar A

[former_member187136]

Database refreshing is a process by which a Sybase ASE database is overwritten or refreshed from an existing backup:

In general, it is more often that a backup of the current production database is taken and a copy of the same database in a development or testing environment is refreshed from this backup.

The database to be refreshed needs to be the same size or greater size as the source database with data and log segments created preferably in the image of the source database (otherwise data and log segments could mix upon refresh).

The process of refreshing has 3 Steps:

1. 1. Bcp Out of Target System Tables
2. 2. Loading the prod/existing image from the existing backup
3. 3. Deleting the loaded system tables(not all the tables)
4. 3. BCP in the tables which are taken in the previous step

Steps:

Check the device sizes in the source DB and create accordingly in the target machine:

sp_helpdb <DB_Name>

go

bcp <<DBNAME>>..sysusers out  sysusers.txt –S<<SERVER NAME>> -U<User_Name>    -c

bcp <<DBNAME>>..sysalternates out sysalternates.txt –S<<SERVER NAME>> -U<User_Name>    -c

bcp <<DBNAME>>..sysusages  out  sysusages.txt –S<<SERVER NAME>> -U<User_Name>    -c

bcp <<DBNAME>>..syslogins out  syslogins.txt –S<<SERVER NAME>> -U<User_Name> -c

select spid,dbid,db_name(dbid),user_name(uid),hostname,program_name,physical_io,cpu,status from master..sysprocesses where db_name(dbid) = <<DBNAME>>

Kill If Any Processes is reporting or put the server as single user mode(at your risk):

select "kill " + convert(varchar(10),spid),cmd,status

from master..sysprocesses

where db_name(dbid) = <<DBNAME>>

use master

go

load database <<DBNAME>>  from  “Dump path1"

stripe on “Dump path12”…..

go

online database <<DBNAME>>

go

use  <<DBNAME>>

go

sp_configure "allow updates",1

go

delete from <<DBNAME>>..sysusers

delete from <<DBNAME>>..sysalternates

delete from <<DBNAME>>..sysusages

delete from <<DBNAME>>..syslogins

go

bcp <<DBNAME>>..sysusers  out  sysusers.txt –S<<SERVER NAME>> -U<User_Name>    -c

bcp <<DBNAME>>..sysalternates out sysalternates.txt –S<<SERVER NAME>> -U<User_Name>    -c

bcp <<DBNAME>>..sysusages  out  sysusages.txt –S<<SERVER NAME>> -U<User_Name>    -c

bcp <<DBNAME>>..syslogins  out  syslogins.txt –S<<SERVER NAME>> -U<User_Name>    -c

select count(*) from <<DBNAME>>..sysusers

select count(*)  from <<DBNAME>>..sysalternates

go

sp_configure "allow updates",0

go

==================================

Sample Load Database Script:

load Database <DB_Name> from 'compress::/<Full_Dump_Path>'

stripe on 'compress::/<Full_Dump_Path>'

go

=======================================

Thanks & Regards

  Kiran K Adharapuram

Product Support Engineer

[former_member187136]

John,

Its very Simple schedule a backup in the off business hours and make sure you transfer to the target machine(through ftp or scp)

BCP out the necessary systems to sync the users and logins, and load the database from the copied dump.

Bring the DB online in the target database.

Regards

Kiran Kumar A

[former_member188958]

The usual method would be to use DUMP DATABASE to make a backup of the production database, then use CREATE DATABASE FOR LOAD to create the training database and then LOAD DATABASE to load the backup into the new training database.