cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted

Automate last SQL Server database restore to a different server

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 Smiley Happy

1 Solution

Accepted Solutions
Highlighted
Accepted Solution!

sql auto restore script, just for your reference.

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
Highlighted
Accepted Solution!

sql auto restore script, just for your reference.

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

Thank you

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 Smiley Happy 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