cancel
Showing results for 
Search instead for 
Did you mean: 

Automate last SQL Server database restore to a different server

Dje_be
Level 3

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 :)

1 ACCEPTED SOLUTION

Accepted Solutions

infocos
Not applicable
Partner Accredited Certified

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

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

View solution in original post

3 REPLIES 3

infocos
Not applicable
Partner Accredited Certified

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

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

Dje_be
Level 3

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