How to manually create a SQL database restore script when having issues creating the restore script using NetBackup Microsoft SQL client GUI

If you encountered difficulties when browsing the SQL database Backup History from the GUI, follow the procedure below to manually create the restore script:

1. Edit a copy of the restore script file rest.bch located in the following path. This file is installed by default when the client software was installed.

    <install_path>\VERITAS\NetBackup\DbExt\MsSql\rest.bch

   Here are the contents of the rest.bch script:

  #***************************************************************************
  #* $VRTScprght: Copyright 1993 - 2003 VERITAS Software Corporation, All Rights Reserved
  #***************************************************************************
  #
  OPERATION RESTORE
  OBJECTTYPE DATABASE
  DATABASE "pubs"
  NBIMAGE "juneberry.MSSQL7.JUNEBERRY\INSTANCEY.db.pubs.~.0.001of001.20020507082642..C"
  SQLHOST "JUNEBERRY"
  SQLINSTANCE "INSTANCEY"
  NBSERVER "JUNEBERRY"
  MAXTRANSFERSIZE 0
  BLOCKSIZE 0
  RESTOREOPTION REPLACE
  RECOVEREDSTATE NOTRECOVERED
  ENDOPER TRUE

2. Modify the value of the following parameters:  (DATABASE, NBIMAGE, SQLINSTANCE, NBSERVER, BLOCKSIZE) with the desired value from your environment to guarantee a successful restore
 

2.1. DATABASE - Replace this value with the database name being restored.      

   

 

2.2. NBIMAGE - Browse for the SQL backup image(s) to be restored and copy the entire image name into the script, replacing the <image_name> with the name of the image to be restored.

 

To do this, gather the Hostname of the original Microsoft SQL Server (in the example below, SERVER was the original server name), open a DOS command prompt and execute the following from the <install_path>\Veritas\NetBackup\bin directory:

        bplist -C SERVER -t 15 -s <date> -e <date> -R /

     This will generate a list of images which will look like the following:

        server.MSSQL7.SERVER.db.pubs.~.0.001of001.20021129160216..C:\
        server.MSSQL7.SERVER.db.Northwind.~.0.001of001.20021129160159..C:\
        server.MSSQL7.SERVER.db.pubs.~.0.001of001.20021122160934..C:\
        server.MSSQL7.SERVER.trx.Northwind.~.0.001of001.20021122160917..C:\
        server.MSSQL7.SERVER1.db.pubs.~.0.001of001.20021115160303..C:\
        server.MSSQL7.SERVER1.inc.Northwind.~.0.001of001.20021115160246..C:\
     

The image name is composed of a variety of information drawn from the master server, including the server name, the database name, and the date and time of the backup. The .db before the database name indicates a full database backup, the .inc indicates a cumulative incremental, and the .trx  indicates a transaction log backup. The final string of numbers is the date and time, in the following format: YYYYMMDDhhmmss. For example, in the first image in the list (server.MSSQL7.SERVER.db.pubs.~.0.001of001.20021129160216..C:\), the image was created on November 29, 2002, at 4:02:16 PM.

     

     NOTE: Be sure to eliminate the trailing backslash from the image name or the restore will fail.

 

2.3. BLOCKSIZE - It is important to use the same blocksize when it is backed up. Search for the BLOCKSIZE entry from the original script and update the above script with that value, i.e. 0 or 7.

 

      NOTE: The default value for this option is 7.

 

2.4. SQLINSTANCE - Replace this value with name of the instance. If a named instance is not defined, use the value "DEFAULT."

 

2.5. NBSERVER - Replace the value with the master server hostname.

 

The modified script will look like this:

 

   OPERATION RESTORE

   OBJECTTYPE DATABASE

   DATABASE "Northwind"

   NBIMAGE "server.MSSQL7.SERVER1.db.Northwind.~.0.001of001.20021129160159..C"

   SQLHOST "SERVER"

   SQLINSTANCE "DEFAULT"

   NBSERVER "JUNEBERRY"

   MAXTRANSFERSIZE 0

   BLOCKSIZE 0

   RESTOREOPTION REPLACE

   RECOVEREDSTATE NOTRECOVERED

   ENDOPER TRUE

 

This script, when executed, restores the Northwind database which was backed up on November 29th, 2002, at 4:01:59 PM. The RECOVEREDSTATE     NOTRECOVERED indicates that other restore operations will be run after this (i.e. an incremental or differential still needs to be applied). Change this line to RECOVEREDSTATE RECOVERED if you wish to run no more restore operations against this database.

 

Please note that the modified values need to be inside the quotation marks. Once the script has been modified, save the batch file with a .bch extension, for example, Northwindrestore.bch.

3. From the NetBackup MSSQL Client GUI, click Actions | Batch Files. Select the .bch file saved and select Start.