05-24-2012 08:55 AM
Hello,
Is it possible to script an automated database restore from one server to another ?
I generated a move template as following:
OPERATION RESTORE
OBJECTTYPE DATABASE
RESTORETYPE MOVE
#
# Replace the database name in the following line with the name of the database that you
# want to move to. Also remove the hash mark <#> which precedes the keyword <DATABASE>.
#
DATABASE "dbname"
#
# Replace the file path <C:\Temp\dbname.mdf>
# with a new file path. Also remove the hash mark <#> which precedes the keyword <TO>.
# The target of the MOVE keyword must be "dbname".
MOVE "dbname"
TO "C:\Temp\dbname.mdf"
#
#
# Replace the file path <C:\Temp\dbname_log.ldf>
# with a new file path. Also remove the hash mark <#> which precedes the keyword <TO>.
# The target of the MOVE keyword must be "dbname_log".
MOVE "dbname_log"
TO "C:\Temp\dbname_log.ldf"
#
# The following image is type: Full
NBIMAGE "servername.MSSQL7.servername\instancename.db.dbname.~.7.001of001.20120523200309..C"
SQLHOST "servername"
NBSERVER "nbservername"
BROWSECLIENT "servername"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
RESTOREOPTION NOREPLACE
RECOVEREDSTATE NOTRECOVERED
NUMBUFS 2
ENDOPER TRUE
How can I specify to use as NBIMAGE, the last existing full backup ? The goal is to have job refreshing on daily basis a database from the latest available full backup.
Going a bit further, what if I would like to script a restore to a specific point in time (i.e.: restore the database1 from server X until 1.00AM) ?
Thank you very much in advance,
Best regards
Jerome
NB: Netbackup version 7.1.0.3 :)
Solved! Go to Solution.
05-24-2012 09:33 AM
1. install cygwin
2. vi sqlautorestore.sh
3. edit sqlautorestore.bat, then add the bat to windows schedule
sh C:\sqlautorestore\sqlautorestore.sh
3. contents of sqlautorestore.sh . just for your reference.
############
#!/bin/bash
PATH=/bin:/cygdrive/c/"Program Files"/veritas/netbackup/bin:$PATH;export PATH
workdir=$(cd "$(dirname "$0")"; pwd)
nbudir="C:\Program Files\Veritas\NetBackup\bin"
sourceclient=orig_client
destclient=restore_client
nbuserver=nbumaster
restorescript=${workdir}/restorescript
mkdir -p ${workdir}/tmp
rm -rf $restorescript*
dblist=${workdir}/tmp/dblist
logfile=${workdir}/`date +%Y%m%d%H%M%S`.log
bplistout=${workdir}/tmp/bplist_out
bplistimage=${workdir}/tmp/bplist_image
dbname=finance
echo "------env info------" > $logfile
echo "scripts work dir: ${workdir}" >> $logfile
echo "NBU bin dir: ${nbudir}" >> $logfile
echo "original client: ${sourceclient}" >> $logfile
echo "restore to the client: ${destclient}" >> $logfile
echo "backup server: ${nbuserver}" >> $logfile
# define the starttime and endtime to query the backup image
starttime=`TZ=aaa24 date +%Y-%m-%d" "%H:%M:%S`
endtime=`date +%Y-%m-%d" "%H:%M:%S`
echo "time to restore : between $starttime and $endtime " >> $logfile
# genarate the bplist output
echo "" >> $logfile
echo "query the backup info using below command: " >> $logfile
echo "bplist -C $sourceclient -t 15 -R -s $starttime -e $endtime / | grep ${dbname} | uniq" >> $logfile
bplist -C $sourceclient -t 15 -R -s $starttime -e $endtime / | grep ${dbname} | uniq > ${bplistout}
# generate database name to restore
cat ${bplistout} |awk -F":" '{print $1}' |awk -F"." '{print $5}'| grep "${dbname}" | sort |uniq > $dblist
echo "" >> $logfile
echo "database name to restore: `cat ${dblist}`" >> $logfile
# reserve the image name
cat ${bplistout} | awk -F":" '{print $1}' > ${bplistimage}
cat $dblist |while read dbname
do
echo "" >> $logfile
echo "analyse backup infomation for database ${dbname}......" >> $logfile
# output the image infomation and sort by date
cat ${bplistimage} |grep "\.db\." | sort -t . -k 9 > ${workdir}/tmp/imagelist_${dbname}_db.txt
cat ${bplistimage} |grep "\.trx\." | sort -t . -k 9 > ${workdir}/tmp/imagelist_${dbname}_trx.txt
echo "" >> $logfile
echo "the database ${dbname} image to restore: " >> $logfile
echo "`cat ${workdir}/tmp/imagelist_${dbname}_db.txt | tail -1`" >> $logfile
row_no_db=`cat ${workdir}/tmp/imagelist_${dbname}_db.txt | wc -l`
echo "" >> $logfile
echo "the database ${dbname} LOG image to restore: " >> $logfile
echo "`cat ${workdir}/tmp/imagelist_${dbname}_trx.txt | tail -1`" >> $logfile
row_no_trx=`cat ${workdir}/tmp/imagelist_${dbname}_trx.txt | wc -l`
# if null, the echo no database backup
if [ ${row_no_db} -eq 0 ]; then
echo "" >> $logfile
echo "database $dbname have no backup information, so can not to restore." >> $logfile
elif [ ${row_no_trx} -eq 0 ]; then
echo "" >> $logfile
echo "just have database full backup, no log backup, only restore database" >> $logfile
dbimage=`cat ${workdir}/tmp/imagelist_${dbname}_db.txt |tail -1|awk '{print $1}'`
echo "OPERATION RESTORE " >> ${restorescript}_${dbname}.bch
echo "OBJECTTYPE DATABASE " >> ${restorescript}_${dbname}.bch
echo "RESTORETYPE MOVE" >> ${restorescript}_${dbname}.bch
echo "DATABASE \"$dbname\"" >> ${restorescript}_${dbname}.bch
echo "MOVE \"${dbname}_Data\"" >> ${restorescript}_${dbname}.bch
echo "TO \"F:\\sqldata\\${dbname}\\${dbname}_Data.MDF\"" >> ${restorescript}_${dbname}.bch
echo "MOVE \"${dbname}_Log\"" >> ${restorescript}_${dbname}.bch
echo "TO \"F:\\sqldata\\${dbname}\\${dbname}_Log.LDF\"" >> ${restorescript}_${dbname}.bch
echo "NBIMAGE \"$dbimage\"" >> ${restorescript}_${dbname}.bch
echo "SQLHOST \"$destclient\"" >> ${restorescript}_${dbname}.bch
echo "NBSERVER \"$nbuserver\"" >> ${restorescript}_${dbname}.bch
echo "BROWSECLIENT \"$sourceclient\"" >> ${restorescript}_${dbname}.bch
echo "MAXTRANSFERSIZE 6 " >> ${restorescript}_${dbname}.bch
echo "BLOCKSIZE 7 " >> ${restorescript}_${dbname}.bch
echo "RESTOREOPTION REPLACE " >> ${restorescript}_${dbname}.bch
echo "RECOVEREDSTATE RECOVERED " >> ${restorescript}_${dbname}.bch
echo "NUMBUFS 2 " >> ${restorescript}_${dbname}.bch
echo "ENDOPER TRUE" >> ${restorescript}_${dbname}.bch
echo >> ${restorescript}_${dbname}.bch
else
echo "" >> $logfile
echo "have database and log backup, restore database first, then recover log." >> $logfile
# check last log timestamp to restore
stopat_temp=`tail -1 ${workdir}/tmp/imagelist_${dbname}_trx.txt |awk -F"." '{print $9}'`
stopatdatetime=`echo ${stopat_temp:0:8}`"/"`echo ${stopat_temp:8:2}`":"`echo ${stopat_temp:10:2}`":"`echo ${stopat_temp:12:2}`
stopat_backupimage=`tail -1 ${workdir}/tmp/imagelist_${dbname}_trx.txt`
dbimage=`cat ${workdir}/tmp/imagelist_${dbname}_db.txt |tail -1|awk '{print $1}'`
echo "" >> $logfile
echo "generate database restore script......" >> $logfile
echo "OPERATION RESTORE " >> ${restorescript}_${dbname}.bch
echo "OBJECTTYPE FILE " >> ${restorescript}_${dbname}.bch
echo "OBJECTNAME " >> ${restorescript}_${dbname}.bch
echo "RESTORETYPE MOVE" >> ${restorescript}_${dbname}.bch
echo "DATABASE \"$dbname\"" >> ${restorescript}_${dbname}.bch
echo "MOVE \"${dbname}_Data\"" >> ${restorescript}_${dbname}.bch
echo "TO \"F:\\sqldata\\${dbname}\\${dbname}_Data.MDF\"" >> ${restorescript}_${dbname}.bch
echo "MOVE \"${dbname}_Log\"" >> ${restorescript}_${dbname}.bch
echo "TO \"F:\\sqldata\\${dbname}\\${dbname}_Log.LDF\"" >> ${restorescript}_${dbname}.bch
echo "NBIMAGE \"$dbimage\"" >> ${restorescript}_${dbname}.bch
echo "SQLHOST \"$destclient\"" >> ${restorescript}_${dbname}.bch
echo "NBSERVER \"$nbuserver\"" >> ${restorescript}_${dbname}.bch
echo "BROWSECLIENT \"$sourceclient\"" >> ${restorescript}_${dbname}.bch
echo "MAXTRANSFERSIZE 6 " >> ${restorescript}_${dbname}.bch
echo "BLOCKSIZE 7 " >> ${restorescript}_${dbname}.bch
echo "RESTOREOPTION REPLACE " >> ${restorescript}_${dbname}.bch
echo "RECOVEREDSTATE NOTRECOVERED " >> ${restorescript}_${dbname}.bch
echo "NUMBUFS 2 " >> ${restorescript}_${dbname}.bch
echo "ENDOPER TRUE" >> ${restorescript}_${dbname}.bch
echo >> ${restorescript}_${dbname}.bch
echo "" >> $logfile
echo "generate log recovery script......" >> $logfile
trximage=`cat ${workdir}/tmp/imagelist_${dbname}_trx.txt |tail -1|awk '{print $1}'`
echo "OPERATION RESTORE" >> ${restorescript}_${dbname}.bch
echo "OBJECTTYPE TRXLOG" >> ${restorescript}_${dbname}.bch
echo "DATABASE \"$dbname\"" >> ${restorescript}_${dbname}.bch
echo "NBIMAGE \"$trximage\"" >> ${restorescript}_${dbname}.bch
echo "SQLHOST \"$destclient\"" >> ${restorescript}_${dbname}.bch
echo "NBSERVER \"$nbuserver\"" >> ${restorescript}_${dbname}.bch
echo "BROWSECLIENT \"$sourceclient\"" >> ${restorescript}_${dbname}.bch
echo "MAXTRANSFERSIZE 6 " >> ${restorescript}_${dbname}.bch
echo "BLOCKSIZE 7 " >> ${restorescript}_${dbname}.bch
echo "RESTOREOPTION REPLACE " >> ${restorescript}_${dbname}.bch
echo "STOPAT $stopatdatetime" >> ${restorescript}_${dbname}.bch
trximage_time=`echo $trximage|awk -F"." '{print $9}'`
stopat_time=`echo $stopat_backupimage|awk -F"." '{print $9}'`
if test "${trximage_time}" = "${stopat_time}" ; then
echo "RECOVEREDSTATE RECOVERED" >> ${restorescript}_${dbname}.bch
else
echo "RECOVEREDSTATE NOTRECOVERED" >> ${restorescript}_${dbname}.bch
fi
echo "NUMBUFS 2 " >> ${restorescript}_${dbname}.bch
echo "ENDOPER TRUE" >> ${restorescript}_${dbname}.bch
echo >> ${restorescript}_${dbname}.bch
fi
done
echo "" >> $logfile
echo "-----begin restore database......-----" >> $logfile
cat ${dblist} | while read dbname
do
echo "C:\\\"Program Files\"\\Veritas\\NetBackup\\bin\\dbbackex -f C:\\sqlautorestore\\${dbname}_restore\\restorescript_${dbname}.bch" >> $logfile
dbbackex -f C:\\sqlautorestore\\${dbname}_restore\\restorescript_${dbname}.bch -np
done
echo "" >> $logfile
echo "after this script run over, check the restore progress through Netbackup MS SQL Client." >> $logfile
echo "if restore have some problems, check the logs on $dest_client C:\Program Files\Veritas\NetBackup\logs\ dbclient bphdb bpbkar." >> $logfile
echo "" >> $logfile
############
05-24-2012 09:26 AM
05-24-2012 09:33 AM
1. install cygwin
2. vi sqlautorestore.sh
3. edit sqlautorestore.bat, then add the bat to windows schedule
sh C:\sqlautorestore\sqlautorestore.sh
3. contents of sqlautorestore.sh . just for your reference.
############
#!/bin/bash
PATH=/bin:/cygdrive/c/"Program Files"/veritas/netbackup/bin:$PATH;export PATH
workdir=$(cd "$(dirname "$0")"; pwd)
nbudir="C:\Program Files\Veritas\NetBackup\bin"
sourceclient=orig_client
destclient=restore_client
nbuserver=nbumaster
restorescript=${workdir}/restorescript
mkdir -p ${workdir}/tmp
rm -rf $restorescript*
dblist=${workdir}/tmp/dblist
logfile=${workdir}/`date +%Y%m%d%H%M%S`.log
bplistout=${workdir}/tmp/bplist_out
bplistimage=${workdir}/tmp/bplist_image
dbname=finance
echo "------env info------" > $logfile
echo "scripts work dir: ${workdir}" >> $logfile
echo "NBU bin dir: ${nbudir}" >> $logfile
echo "original client: ${sourceclient}" >> $logfile
echo "restore to the client: ${destclient}" >> $logfile
echo "backup server: ${nbuserver}" >> $logfile
# define the starttime and endtime to query the backup image
starttime=`TZ=aaa24 date +%Y-%m-%d" "%H:%M:%S`
endtime=`date +%Y-%m-%d" "%H:%M:%S`
echo "time to restore : between $starttime and $endtime " >> $logfile
# genarate the bplist output
echo "" >> $logfile
echo "query the backup info using below command: " >> $logfile
echo "bplist -C $sourceclient -t 15 -R -s $starttime -e $endtime / | grep ${dbname} | uniq" >> $logfile
bplist -C $sourceclient -t 15 -R -s $starttime -e $endtime / | grep ${dbname} | uniq > ${bplistout}
# generate database name to restore
cat ${bplistout} |awk -F":" '{print $1}' |awk -F"." '{print $5}'| grep "${dbname}" | sort |uniq > $dblist
echo "" >> $logfile
echo "database name to restore: `cat ${dblist}`" >> $logfile
# reserve the image name
cat ${bplistout} | awk -F":" '{print $1}' > ${bplistimage}
cat $dblist |while read dbname
do
echo "" >> $logfile
echo "analyse backup infomation for database ${dbname}......" >> $logfile
# output the image infomation and sort by date
cat ${bplistimage} |grep "\.db\." | sort -t . -k 9 > ${workdir}/tmp/imagelist_${dbname}_db.txt
cat ${bplistimage} |grep "\.trx\." | sort -t . -k 9 > ${workdir}/tmp/imagelist_${dbname}_trx.txt
echo "" >> $logfile
echo "the database ${dbname} image to restore: " >> $logfile
echo "`cat ${workdir}/tmp/imagelist_${dbname}_db.txt | tail -1`" >> $logfile
row_no_db=`cat ${workdir}/tmp/imagelist_${dbname}_db.txt | wc -l`
echo "" >> $logfile
echo "the database ${dbname} LOG image to restore: " >> $logfile
echo "`cat ${workdir}/tmp/imagelist_${dbname}_trx.txt | tail -1`" >> $logfile
row_no_trx=`cat ${workdir}/tmp/imagelist_${dbname}_trx.txt | wc -l`
# if null, the echo no database backup
if [ ${row_no_db} -eq 0 ]; then
echo "" >> $logfile
echo "database $dbname have no backup information, so can not to restore." >> $logfile
elif [ ${row_no_trx} -eq 0 ]; then
echo "" >> $logfile
echo "just have database full backup, no log backup, only restore database" >> $logfile
dbimage=`cat ${workdir}/tmp/imagelist_${dbname}_db.txt |tail -1|awk '{print $1}'`
echo "OPERATION RESTORE " >> ${restorescript}_${dbname}.bch
echo "OBJECTTYPE DATABASE " >> ${restorescript}_${dbname}.bch
echo "RESTORETYPE MOVE" >> ${restorescript}_${dbname}.bch
echo "DATABASE \"$dbname\"" >> ${restorescript}_${dbname}.bch
echo "MOVE \"${dbname}_Data\"" >> ${restorescript}_${dbname}.bch
echo "TO \"F:\\sqldata\\${dbname}\\${dbname}_Data.MDF\"" >> ${restorescript}_${dbname}.bch
echo "MOVE \"${dbname}_Log\"" >> ${restorescript}_${dbname}.bch
echo "TO \"F:\\sqldata\\${dbname}\\${dbname}_Log.LDF\"" >> ${restorescript}_${dbname}.bch
echo "NBIMAGE \"$dbimage\"" >> ${restorescript}_${dbname}.bch
echo "SQLHOST \"$destclient\"" >> ${restorescript}_${dbname}.bch
echo "NBSERVER \"$nbuserver\"" >> ${restorescript}_${dbname}.bch
echo "BROWSECLIENT \"$sourceclient\"" >> ${restorescript}_${dbname}.bch
echo "MAXTRANSFERSIZE 6 " >> ${restorescript}_${dbname}.bch
echo "BLOCKSIZE 7 " >> ${restorescript}_${dbname}.bch
echo "RESTOREOPTION REPLACE " >> ${restorescript}_${dbname}.bch
echo "RECOVEREDSTATE RECOVERED " >> ${restorescript}_${dbname}.bch
echo "NUMBUFS 2 " >> ${restorescript}_${dbname}.bch
echo "ENDOPER TRUE" >> ${restorescript}_${dbname}.bch
echo >> ${restorescript}_${dbname}.bch
else
echo "" >> $logfile
echo "have database and log backup, restore database first, then recover log." >> $logfile
# check last log timestamp to restore
stopat_temp=`tail -1 ${workdir}/tmp/imagelist_${dbname}_trx.txt |awk -F"." '{print $9}'`
stopatdatetime=`echo ${stopat_temp:0:8}`"/"`echo ${stopat_temp:8:2}`":"`echo ${stopat_temp:10:2}`":"`echo ${stopat_temp:12:2}`
stopat_backupimage=`tail -1 ${workdir}/tmp/imagelist_${dbname}_trx.txt`
dbimage=`cat ${workdir}/tmp/imagelist_${dbname}_db.txt |tail -1|awk '{print $1}'`
echo "" >> $logfile
echo "generate database restore script......" >> $logfile
echo "OPERATION RESTORE " >> ${restorescript}_${dbname}.bch
echo "OBJECTTYPE FILE " >> ${restorescript}_${dbname}.bch
echo "OBJECTNAME " >> ${restorescript}_${dbname}.bch
echo "RESTORETYPE MOVE" >> ${restorescript}_${dbname}.bch
echo "DATABASE \"$dbname\"" >> ${restorescript}_${dbname}.bch
echo "MOVE \"${dbname}_Data\"" >> ${restorescript}_${dbname}.bch
echo "TO \"F:\\sqldata\\${dbname}\\${dbname}_Data.MDF\"" >> ${restorescript}_${dbname}.bch
echo "MOVE \"${dbname}_Log\"" >> ${restorescript}_${dbname}.bch
echo "TO \"F:\\sqldata\\${dbname}\\${dbname}_Log.LDF\"" >> ${restorescript}_${dbname}.bch
echo "NBIMAGE \"$dbimage\"" >> ${restorescript}_${dbname}.bch
echo "SQLHOST \"$destclient\"" >> ${restorescript}_${dbname}.bch
echo "NBSERVER \"$nbuserver\"" >> ${restorescript}_${dbname}.bch
echo "BROWSECLIENT \"$sourceclient\"" >> ${restorescript}_${dbname}.bch
echo "MAXTRANSFERSIZE 6 " >> ${restorescript}_${dbname}.bch
echo "BLOCKSIZE 7 " >> ${restorescript}_${dbname}.bch
echo "RESTOREOPTION REPLACE " >> ${restorescript}_${dbname}.bch
echo "RECOVEREDSTATE NOTRECOVERED " >> ${restorescript}_${dbname}.bch
echo "NUMBUFS 2 " >> ${restorescript}_${dbname}.bch
echo "ENDOPER TRUE" >> ${restorescript}_${dbname}.bch
echo >> ${restorescript}_${dbname}.bch
echo "" >> $logfile
echo "generate log recovery script......" >> $logfile
trximage=`cat ${workdir}/tmp/imagelist_${dbname}_trx.txt |tail -1|awk '{print $1}'`
echo "OPERATION RESTORE" >> ${restorescript}_${dbname}.bch
echo "OBJECTTYPE TRXLOG" >> ${restorescript}_${dbname}.bch
echo "DATABASE \"$dbname\"" >> ${restorescript}_${dbname}.bch
echo "NBIMAGE \"$trximage\"" >> ${restorescript}_${dbname}.bch
echo "SQLHOST \"$destclient\"" >> ${restorescript}_${dbname}.bch
echo "NBSERVER \"$nbuserver\"" >> ${restorescript}_${dbname}.bch
echo "BROWSECLIENT \"$sourceclient\"" >> ${restorescript}_${dbname}.bch
echo "MAXTRANSFERSIZE 6 " >> ${restorescript}_${dbname}.bch
echo "BLOCKSIZE 7 " >> ${restorescript}_${dbname}.bch
echo "RESTOREOPTION REPLACE " >> ${restorescript}_${dbname}.bch
echo "STOPAT $stopatdatetime" >> ${restorescript}_${dbname}.bch
trximage_time=`echo $trximage|awk -F"." '{print $9}'`
stopat_time=`echo $stopat_backupimage|awk -F"." '{print $9}'`
if test "${trximage_time}" = "${stopat_time}" ; then
echo "RECOVEREDSTATE RECOVERED" >> ${restorescript}_${dbname}.bch
else
echo "RECOVEREDSTATE NOTRECOVERED" >> ${restorescript}_${dbname}.bch
fi
echo "NUMBUFS 2 " >> ${restorescript}_${dbname}.bch
echo "ENDOPER TRUE" >> ${restorescript}_${dbname}.bch
echo >> ${restorescript}_${dbname}.bch
fi
done
echo "" >> $logfile
echo "-----begin restore database......-----" >> $logfile
cat ${dblist} | while read dbname
do
echo "C:\\\"Program Files\"\\Veritas\\NetBackup\\bin\\dbbackex -f C:\\sqlautorestore\\${dbname}_restore\\restorescript_${dbname}.bch" >> $logfile
dbbackex -f C:\\sqlautorestore\\${dbname}_restore\\restorescript_${dbname}.bch -np
done
echo "" >> $logfile
echo "after this script run over, check the restore progress through Netbackup MS SQL Client." >> $logfile
echo "if restore have some problems, check the logs on $dest_client C:\Program Files\Veritas\NetBackup\logs\ dbclient bphdb bpbkar." >> $logfile
echo "" >> $logfile
############
05-25-2012 02:33 AM
Yogesh9881, thanks for the links. I already browsed them and it was not usefull for what I was looking for.
infocos, thank you for the script! I wasn't expecting so much :) I need to go deeper in bplist command in fact and I will probably re-use your script and translate it to powershell to avoid the need to install cygwin on my servers.
Best regards,
Jerome