Description
Legacy SQL Server policies use batch files to initiate backup and restore operations. These examples show you how to perform a variety of operations with batch files.
The following examples of batch files are available:
Script to perform a striped database backup and allow multiple internal buffers per stripe
Script to perform an operation and specify the user ID and password to use to SQL Server
Script to specify the maximum transfer size and block size for a backup
Script that uses environment variables to exclude instances and databases from backup
OPERATION BACKUP
DATABASE "BUSINESS"
SQLHOST "CADOO"
SQLINSTANCE "SECOND"
NBSERVER "CHISEL"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
ENDOPER TRUE
OPERATION BACKUP
DATABASE "BUSINESS"
SQLHOST "CADOO"
SQLINSTANCE "SECOND"
NBSERVER "CHISEL"
STRIPES 4
NUMBUFS 2
MAXTRANSFERSIZE 6
BLOCKSIZE 7
ENDOPER TRUE
OPERATION BACKUP
DATABASE "BUSINESS"
SQLHOST "CADOO"
SQLINSTANCE "SECOND"
NBSERVER "CHISEL"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
USERID JSMITH
PASSWORD my.Pwd
ENDOPER TRUE
OPERATION BACKUP
DATABASE "BUSINESS"
OBJECTTYPE DATABASE
SQLHOST "CADOO"
SQLINSTANCE "SECOND"
NBSERVER "CHISEL"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
STRIPES 5
ENDOPER TRUE
OPERATION BACKUP
DATABASE "RECREATION"
SQLHOST "CADOO"
SQLINSTANCE "SECOND"
NBSERVER "CHISEL"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
OBJECTTYPE TRXLOG
ENDOPER TRUE
OPERATION BACKUP
DATABASE "EDUCATION"
SQLHOST "CADOO"
SQLINSTANCE "SECOND"
NBSERVER "CHISEL"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
STRIPES 2
ENDOPER TRUE
OPERATION BACKUP
DATABASE "GOVERNANCE"
SQLHOST "CADOO"
SQLINSTANCE "SECOND"
NBSERVER "CHISEL"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
OBJECTYPE TRXLOG
ENDOPER TRUE
OPERATION BACKUP
DATABASE "SURVIVAL"
SQLHOST "CADOO"
SQLINSTANCE "SECOND"
NBSERVER "CHISEL"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
OBJECTYPE TRXLOG
ENDOPER TRUE
Script to perform multiple operations in sequence
This setting tells NetBackup to start the first three operations in parallel. After these are completed, NetBackup then begins the next set of 3. In this case, since there are five operations, the second batch set contains two operations.
BATCHSIZE 3
OPERATION BACKUP
DATABASE "BUSINESS"
OBJECTTYPE DATABASE
SQLHOST "CADOO"
SQLINSTANCE "SECOND"
NBSERVER "CHISEL"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
STRIPES 5
ENDOPER TRUE
OPERATION BACKUP
DATABASE "RECREATION"
SQLHOST "CADOO"
SQLINSTANCE "SECOND"
NBSERVER "CHISEL"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
OBJECTTYPE TRXLOG
ENDOPER TRUE
OPERATION BACKUP
DATABASE "EDUCATION"
SQLHOST "CADOO"
SQLINSTANCE "SECOND"
NBSERVER "CHISEL"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
STRIPES 2
ENDOPER TRUE
OPERATION BACKUP
DATABASE "GOVERNANCE"
SQLHOST "CADOO"
SQLINSTANCE "SECOND"
NBSERVER "CHISEL"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
OBJECTYPE TRXLOG
ENDOPER TRUE
OPERATION BACKUP
DATABASE "SURVIVAL"
SQLHOST "CADOO"
SQLINSTANCE "SECOND"
NBSERVER "CHISEL"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
OBJECTYPE TRXLOG
ENDOPER TRUE
OPERATION BACKUP
DATABASE "BUSINESS"
SQLHOST "CADOO"
SQLINSTANCE "SECOND"
NBSERVER "CHISEL"
MAXTRANSFERSIZE 4
BLOCKSIZE 6
ENDOPER TRUE
You can use SQLINSTANCE $ALL in your batch file to designate that all SQL Server instances on your host be backed up. For example, the following batch file backs up the master, model, and msdb databases. These databases are backed up on all instances of SQL Server on the host on which the batch file is run.
[ NOTE - SQLINSTANCE $ALL this parameter is not supported for SQL Clustered instances. The backup for clustered instances are configured using the Cluster virtual name ( CAS name ) and it is unique for each clustered instance ]
SQLINSTANCE $ALL
OPERATION BACKUP
DATABASE "master"
NBSERVER "BEARING"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
NUMBUFS 2
ENDOPER TRUE
OPERATION BACKUP
DATABASE "msdb"
NBSERVER "BEARING"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
NUMBUFS 2
ENDOPER TRUE
OPERATION BACKUP
DATABASE "model"
NBSERVER "BEARING"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
NUMBUFS 2
ENDOPER TRUE
To exclude SQL Server instances on your host from backup, create the Windows environmental variable NB_SQL_INSTANCE_EXCLUDE. Specify a list of instances names that you want to exclude. The list should consist of one or more names that are separated by semi-colons.
For example, use the following value to indicate that you want to exclude the default SQL Server instance and the instance named ABC-PRODUCTS from backup:
#DEFAULT#;ABC-PRODUCTS;
Note that the default SQL Server instance for the local host is designated as #default#.
You can also exclude individual databases from backup by creating a Windows environmental variable NB_SQL_DATABASE_EXCLUDE. For the value of the variable, specify a list of database names.
For example, consider the following batch file:
SQLINSTANCE $ALL
OPERATION BACKUP
DATABASE $ALL
NBSERVER "BEARING"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
NUMBUFS 2
ENDOPER TRUE
You can exclude the databases "master," "accounting," and "pubs" with the NB_SQL_DATABASE_EXCLUDE environmental variable. For the value of the variable, indicate the databases you want to exclude. Separate the database names with semi-colons.
The NB_SQL_DATABASE_EXCLUDE variable is applicable only for a batch file that has DATABASE $ALL. It performs the same function as the keyword and value pair EXCLUDE <database>. If both variables are used, they augment each other to determine which databases to exclude.
You can use batch files to initiate restore operations. The following examples of restore batch files are available:
NBIMAGE "cadoo.MSSQL7.CADOO\SECOND.db.pubs.~.7.001of001.20140628123631..C"
To find out which backups you can restore, look at the dbclient log file created when you did the backup or by use bplist.
OPERATION RESTORE
OBJECTTYPE DATABASE
DATABASE "pubs" # The following image is type: Full
NBIMAGE "cadoo.MSSQL7.CADOO\SECOND.db.pubs.~.7.001of001.20140628123631..C"
SQLHOST "CADOO"
SQLINSTANCE "SECOND"
NBSERVER "CHISEL"
BROWSECLIENT "CADOO"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
RESTOREOPTION REPLACE
RECOVEREDSTATE RECOVERED
ENDOPER TRUE
OPERATION RESTORE
OBJECTTYPE DATABASE
DATABASE "Northwind"
NBIMAGE "cadoo.MSSQL7.CADOO.db.Northwind.~.0.001of004.20140216151937..C"
STRIPES 004
MAXTRANSFERSIZE 6
BLOCKSIZE 7
SQLHOST "CADOO"
SQLINSTANCE "SECOND"
NBSERVER "CHISEL"
BROWSECLIENT "CADOO"
RECOVEREDSTATE RECOVERED
ENDOPER TRUE
OPERATION RESTORE
OBJECTTYPE FILEGROUP
DATABASE "DatabaseR"
OBJECTNAME "PRIMARY"
# The following image is type: Filegroup NBIMAGE "ca.MSSQL7.CA\SECOND.fg.DatabaseR.PRIMARY.7.001of001.20140701095634..C"
SQLHOST "CA"
SQLINSTANCE "SECOND"
NBSERVER "BOW"
BROWSECLIENT "CA"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
RESTOREOPTION REPLACE
RECOVEREDSTATE NOTRECOVERED
ENDOPER TRUE
OPERATION RESTORE
OBJECTTYPE FILEGROUP
DATABASE "DatabaseR"
OBJECTNAME "DBR_FG2"
# The following image is type: Filegroup NBIMAGE "ca.MSSQL7.CA\SECOND.fg.DatabaseR.DBR_FG2.7.001of001.20140701095425..C"
SQLHOST "CA"
SQLINSTANCE "SECOND"
NBSERVER "BOW"
BROWSECLIENT "CA"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
RESTOREOPTION REPLACE
RECOVEREDSTATE NOTRECOVERED
ENDOPER TRUE
OPERATION RESTORE
OBJECTTYPE FILE
DATABASE "DatabaseR"
OBJECTNAME "DBR_FG1_File1"
# The following image is type: File NBIMAGE "ca.MSSQL7.CA\SECOND.fil.DatabaseR.DBR_FG1_File1.7.001of001.20140701100824..C"
SQLHOST "CA"
SQLINSTANCE "SECOND"
NBSERVER "BOW"
BROWSECLIENT "CA"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
RESTOREOPTION REPLACE
RECOVEREDSTATE NOTRECOVERED
ENDOPER TRUE
OPERATION RESTORE
OBJECTTYPE FILE
DATABASE "DatabaseR"
OBJECTNAME "DBR_FG1_File2"
# The following image is type: File NBIMAGE "ca.MSSQL7.CA\SECOND.fil.DatabaseR.DBR_FG1_File2.7.001of001.20140701100908..C"
SQLHOST "CA"
SQLINSTANCE "SECOND"
NBSERVER "BOW"
BROWSECLIENT "CA"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
RESTOREOPTION REPLACE
RECOVEREDSTATE NOTRECOVERED
ENDOPER TRUE
OPERATION RESTORE
OBJECTTYPE FILE
DATABASE "DatabaseR"
OBJECTNAME "DBR_FG1_File3"
# The following image is type: File NBIMAGE "ca.MSSQL7.CA\SECOND.fil.DatabaseR.DBR_FG1_File3.7.001of001.20140701100953..C"
SQLHOST "CA"
SQLINSTANCE "SECOND"
NBSERVER "BOW"
BROWSECLIENT "CA"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
RESTOREOPTION REPLACE
RECOVEREDSTATE NOTRECOVERED
ENDOPER TRUE
OPERATION RESTORE
OBJECTTYPE TRXLOG
DATABASE "DatabaseR"
# The following image is type: transaction log NBIMAGE "ca.MSSQL7.CA\SECOND.trx.DatabaseR.~.7.001of001.20140701100030..C"
SQLHOST "CA"
SQLINSTANCE "SECOND"
NBSERVER "BOW"
BROWSECLIENT "CA"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
RESTOREOPTION REPLACE
RECOVEREDSTATE NOTRECOVERED
ENDOPER TRUE
OPERATION RESTORE
OBJECTTYPE TRXLOG
DATABASE "DatabaseR"
# The following image is type: transaction log NBIMAGE "ca.MSSQL7.CA\SECOND.trx.DatabaseR.~.7.001of001.20140701110015..C"
SQLHOST "CA"
SQLINSTANCE "SECOND"
NBSERVER "BOW"
BROWSECLIENT "CA"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
RESTOREOPTION REPLACE
RECOVEREDSTATE RECOVERED
ENDOPER TRUE
Note the following:
If STOPAT is not specified, then the database is restored to the date of the backup log.
You do not need to manually stage the restoration of the database backup and the associated log files. Create the script in the Restore Microsoft SQL Server Objects dialog box.
Since RECOVEREDSTATE was not specified, the database is restored to a recovered state following successful execution of this script.
OPERATION RESTORE
OBJECTTYPE TRXLOG
STOPAT 20140216/14:03:00
DATABASE Northwind
NBIMAGE "cadoo.MSSQL7.CADOO.trx.Northwind.~.0.001of001.20140216140321..C"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
SQLHOST "CADOO"
SQLINSTANCE "SECOND"
NBSERVER "CHISEL"
BROWSECLIENT "CADOO"
ENDOPER TRUE
OPERATION RESTORE
OBJECTTYPE DATABASE
DATABASE "DatabaseA"
# The following image is type: Full NBIMAGE "cadoo.MSSQL7.CADOO\SECOND.db.DatabaseA.~.7.001of001.20140701094227..C"
SQLHOST "CADOO"
SQLINSTANCE "SECOND"
NBSERVER "BOW"
BROWSECLIENT "CADOO"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
RESTOREOPTION REPLACE
RECOVEREDSTATE NOTRECOVERED
ENDOPER TRUE
OPERATION RESTORE
OBJECTTYPE DATABASE
DUMPOPTION INCREMENTAL
DATABASE "DatabaseA"
# The following image is type: Full database differential NBIMAGE "cadoo.MSSQL7.CADOO\SECOND.inc.DatabaseA.~.7.001of001.20140701103323..C"
SQLHOST "CADOO"
SQLINSTANCE "SECOND"
NBSERVER "BOW"
BROWSECLIENT "CADOO"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
RESTOREOPTION REPLACE
RECOVEREDSTATE NOTRECOVERED
ENDOPER TRUE
OPERATION RESTORE
OBJECTTYPE TRXLOG
DATABASE "DatabaseA"
# The following image is type: transaction log NBIMAGE "cadoo.MSSQL7.CADOO\SECOND.trx.DatabaseA.~.7.001of001.20140701090005..C"
SQLHOST "CADOO"
SQLINSTANCE "SECOND"
NBSERVER "BOW"
BROWSECLIENT "CADOO"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
RESTOREOPTION REPLACE
RECOVEREDSTATE NOTRECOVERED
ENDOPER TRUE
OPERATION RESTORE
OBJECTTYPE TRXLOG
DATABASE "DatabaseA"
# The following image is type: transaction log NBIMAGE "cadoo.MSSQL7.CADOO\SECOND.trx.DatabaseA.~.7.001of001.20140701100030..C"
SQLHOST "CADOO"
SQLINSTANCE "SECOND"
NBSERVER "BOW"
BROWSECLIENT "CADOO"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
RESTOREOPTION REPLACE
RECOVEREDSTATE NOTRECOVERED
ENDOPER TRUE
OPERATION RESTORE
OBJECTTYPE TRXLOG
DATABASE "DatabaseA"
# The following image is type: transaction log NBIMAGE "cadoo.MSSQL7.CADOO\SECOND.trx.DatabaseA.~.7.001of001.20140701110015..C"
SQLHOST "CADOO"
SQLINSTANCE "SECOND"
NBSERVER "BOW"
BROWSECLIENT "CADOO"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
RESTOREOPTION REPLACE
RECOVEREDSTATE NOTRECOVERED
ENDOPER TRUE
Was this content helpful?
Translated Content
Please note that this document is a translation from English, and may have been machine-translated. It is possible that updates have been made to the original version after this document was translated and published. Veritas does not guarantee the accuracy regarding the completeness of the translation. You may also refer to the English Version of this knowledge base article for up-to-date information.