Wrapper script to refresh test database with production data

Shell Programming and Scripting
1

Hi All,

I'm new here as well as to UNIX.

I need to write a wrapper script to refresh test database with production data. A set of tables are given as well as a particular data range. The script should prompt the user for both production and test logon credentials.

Pl. help me with this.

Thanks in advance.
Lini

2

What database?

Are you on a SAN?

If the db is small you can export the db to tape or across your network, then import it. We need more information.

3

thank you for replying...

Am on Sybase database(12.5). I have the following script ready. The requirement mentioned user should be prompted for source and target logon credentials.

#!/bin/sh
# Shell Script to do a complete refresh of table data from ODS schema
# PROMPTS FOR THE PRODUCTION AND QA SERVER NAME USER NAME AND PASSWORD
echo "Enter the production Server name or ip address"
read prod_server_nm
echo "Enter the production user name"
read prod_user_nm
echo "Enter the production Server password "
read prod_pwd
echo "Enter the QA Server name or ip address"
read QA_server_nm
echo "Enter the QA user name"
read QA_user_nm
echo "Enter the QA Server password "
read QA_pwd

#od_table_nm.txt is the file name in which the table names will be stored for ODS schema
var=`cat od_table_nm.txt`
for table_nm in $var; do
echo $table_nm
delete_table()
{
isql -S $QA_server_nm -U $QA_user_nm -P $QA_pwd<< EOF
use odstest
go
if exists ( select 1 from sysobjects where type = 'U' and name = $table_nm)
delete * from odstest..$table_nm
go
EOF
}
bcpout_data()
{
bcp odstest..$table_nm out ./$table_nm -S $prod_server_nm -U $prod_user_nm -c -e ./$table_nm.bcp_out_err_file.$$ <<!
$prod_pwd
!
}
bcpin_data()
{
echo "Loading the data into $table_nm table................"
bcp odstest..$table_nm in ./$table_nm -S $QA_server_nm -U $QA_user_nm -c -e ./$table_nm.bcp_in_err_file <<!
$QA_pwd
!
}

bcpout_data
bcpin_data
rm -f $table_nm
done