Automate last SQL Server database restore to a different server

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

############