Backup optional parameters

Optional parameters follow the backup command and positional parameters.

/AAGName=AlwaysOn Availability Group name
When you backup a database list or all databases with the wildcard character, *, and specify the /AAGName parameter, only databases from the availability group that you specify are backed up.
/ADJUSTKBtsmestimate=numkb
The /adjustkbtsmestimate parameter specifies the number of kilobytes to add to the size of the backup estimate generated by the SQL Server. The numkb variable refers to the number of kilobytes to add. The number can range from 0 to 9999. The default is 0. Increasing the number of kilobytes may be necessary when the backup estimate (generated by the SQL Server) may be too low as the disk storage pool has cache enabled. For example, if maintenance is performed on the production server during a Data Protection for SQL Server backup, the size of transaction logs can increase beyond the original backup estimate and cause the backup to fail. Use this parameter to customize the number of kilobytes in the backup estimate and avoid possible backup failures.
/ADJUSTPERcenttsmestimate=numpercent
The /adjustpercenttsmestimate parameter specifies the percentage number to add to the size of the backup estimate. The numpercent variable refers to the percentage number to add. The number can range from 0 to 99. The default is 0. Increasing the percentage estimate may be necessary when the backup estimate (generated by the SQL Server) may be too low as the disk storage pool has cache enabled. For example, if maintenance is performed on the production server during a Data Protection for SQL Server backup, the size of transaction logs can increase beyond the original backup estimate and cause the backup to fail. Use this parameter to customize the percentage in the backup estimate and avoid possible backup failures.
/ALWAYSONPriority
Use this parameter to specify that a local availability database is backed up only if it has the highest backup priority among the availability replicas that are working properly on SQL Server 2012 and later versions. You can use this parameter at the command-line interface or as part of a scheduled backup.
/BACKUPDESTination=TSM|LOCAL|BOTH
Use the /BACKUPDESTination parameter to specify the location where the backup is stored.
You can specify:
TSM
The backup is stored on IBM Spectrum Protect™ server storage only. This option is the default.
LOCAL
The backup is stored on local shadow volumes only. This is only valid when the /BACKUPMETHod parameter specifies VSS.
BOTH
The backup is stored on IBM Spectrum Protect server storage and local shadow volumes. This is only valid when the /BACKUPMETHod parameter specifies VSS.
The /BACKUPDESTination parameter is valid only when using the full or copyfull positional parameters. The full and copyfull backups can be saved to TSM server storage, local storage, or both. The /BACKUPDESTination parameter is used to choose among options. The log, diff, file, and group backups can be stored only to TSM server storage. In this scenario, you cannot issue the /BACKUPDESTination parameter because TSM is the only viable option.
/BACKUPMETHod=LEGACY|VSS
Use the /BACKUPMETHod parameter to specify the manner in which the backup is performed.
You can specify:
LEGACY
The backup is performed with the legacy API. This backup is the SQL Server streaming backup and restore API as used in previous versions of Data Protection for SQL Server. This option is the default value.
VSS
The backup is performed with VSS.
The /BACKUPMETHod parameter is valid only when using the full or copyfull positional parameters. The full and copyfull backups can be performed using VSS or legacy operations. The /BACKUPMETHod parameter is used to choose between the options. The log, diff, file, and group backups can only be performed using legacy operations. In this scenario, you cannot issue the /BACKUPMETHod parameter because legacy is the only viable option.
/BUFFers=numbuffers
The /buffers parameter specifies the number of data buffers used for each data stripe to transfer data between Data Protection for SQL Server and the IBM Spectrum Protect API. The numbuffers variable refers to the number of data buffers to use. The number can range from 2 to 8. The default is 3.
Considerations:
  • You can improve throughput by increasing the number of buffers, but you will also increase storage use. Each buffer is the size specified in the /buffersize parameter.
  • The default value is the value specified by the buffers configurable option in the Data Protection for SQL Server configuration file. This is initially 3.
  • If you specify /buffers, its value is used instead of the value stored in the Data Protection for SQL Server configuration file. Specifying this parameter does not change the value in the configuration file.
  • If you specify /buffers but not numbuffers, the default value 3 is used.
/BUFFERSIze=buffersizeinkb
The /buffersize parameter specifies the size of each Data Protection for SQL Server buffer specified by the /buffers parameter. The buffersizeinkb variable refers to the size of data buffers in kilobytes. The number can range from 64 to 8192. The default is 1024.
Considerations:
  • Though increasing the number of buffers can improve throughput, it also increases storage use as determined by this parameter.
  • The default value is the value specified by the buffers configurable option in the Data Protection for SQL Server configuration file. This is initially 1024.
  • If you specify /buffersize, its value is used instead of the value stored in the Data Protection for SQL Server configuration file. Specifying this parameter does not change the value in the configuration file.
  • If you specify /buffersize but not buffersizeinkb, the default value 1024 is used.
/CONFIGfile=configfilename
The /configfile parameter specifies the name of the Data Protection for SQL Server configuration file, which contains the values for the Data Protection for SQL Server configurable options.
Considerations:
  • configfilename can include a fully qualified path. If configfilename does not include a path, it uses the directory where Data Protection for SQL Server is installed.
  • If configfilename includes spaces, place it in double quotes.
  • If you do not specify /configfile, the default value is tdpsql.cfg.
/ENABLEREPlacementchars=No|Yes
The /ENABLEREPlacementchars parameter enables SQL Server databases that have backslash (\) or colon (:) characters in the database name to be backed up. The maximum length of the database name is 128 characters. This parameter applies only to Data Protection for SQL Server version 7.1.1 and later versions.
You can specify the following values:
Yes
Enable Data Protection for SQL Server to process backslash (\) or colon (:) characters in a database name, and back up the database to IBM Spectrum Protect. This value is the default.
No
Prevent database backups to IBM Spectrum Protect if a user-defined string is substituted for a backslash (\) or colon (:) character in the database name.
/EXCLUDEdb=dblist
The /excludedb parameter specifies the name of the databases to exclude from the backup operation. This parameter is available for all VSS and legacy backup types.
/EXCLUDEALwaysondbs
Use this parameter to exclude all AlwaysOn Availability Databases from the backup operation. If you want to exclude specific databases, use the /excludedb parameter.
/EXCLUDESTandarddbs
Use this parameter to exclude all standard databases from the backup operation. If you want to exclude specific databases, use the /excludedb parameter.
/LOGFile=logfilename
The /logfile parameter specifies the name of the activity log that is generated by Data Protection for SQL Server. This activity log records significant events such as completed commands and error messages. The Data Protection for SQL Server activity log is distinct from the SQL Server error log. The logfilename variable identifies the name to be used for the activity log generated by Data Protection for SQL Server.
Considerations:
  • If the log file that you specify does not exist, it is created. If it does exist, new log entries are appended to the file.
  • The file name can include a fully-qualified path; however, if you specify no path, the file is written to the directory where Data Protection for SQL Server is installed.
  • You cannot turn Data Protection for SQL Server activity logging off. If you do not specify /logfile, log records are written to the default log file. The default log file is tdpsql.log.
  • When using multiple simultaneous instances of Data Protection for SQL Server to perform operations, use the /logfile parameter to specify a different log file for each instance used. This directs logging for each instance to a different log file and prevents interspersed log file records. Failure to specify a different log file for each instance can result in unreadable log files.
LOGPrune=numdays|No

When you prune log data, you can discard some of the generated logs according to detailed filtering criteria that you set. In the configuration file, the default value for the LOGPrune is that specified by the logprune configurable option. The default value is 60, which means 60 days of log entries are saved. The option No can be specified to disable log pruning.

Regardless of the option that is set in the configuration file for this parameter, you can explicitly request log pruning at any time.

Considerations:
  • For numdays, the range is 0 to 9999. A value of 0 deletes all entries in the activity log file except for the current command entries.
  • If you specify the LOGPrune parameter, that value is used instead of the value that is stored in the configuration file. Specifying this parameter does not change the value in the configuration file.
  • You can specify LOGPrune without specifying numdays or no; in this case, the default value, 60, is used.
  • Changes to the value of the TIMEformat or DATEformat parameter can result in an unwanted pruning of the log file. If you are running a command that might prune the log file, and the value of the TIMEformat or DATEformat parameter is changed, complete one of the following to prevent unintentional pruning of the log file:
    • Make a copy of the existing log file.
    • Specify a new log file with the LOGFile parameter or logfile setting.
/MOUNTWait=Yes|No
The /mountwait parameter is used to specify whether Data Protection for SQL Server waits for removable media to mount, such as tapes or CDs, or stops the current operation. This parameter is not valid for all backup types; the parameter does not work with DIFFFULL or LOG backup types. If the IBM Spectrum Protect server is configured to store backup data on removable media, it is possible that the IBM Spectrum Protect server might indicate to Data Protection for SQL Server that it is waiting for a required storage volume to be mounted. If that occurs, this option allows you to specify whether to wait for the media mount or stop the current operation. You can specify:
Yes
Wait for tape mounts (default).
No
Do not wait for tape mounts.
Considerations:
  • If you use data striping for legacy operations, Data Protection for SQL Server cannot complete waiting until the initial media for stripes are available, although Data Protection for SQL Server starts to use each stripe as its media becomes available. Because of the way SQL Server distributes data among stripes, if any stripe does not have its media available, each of the stripes may eventually be either waiting for its own or another stripe's media to become available. In this case, it may become necessary to terminate the Data Protection for SQL Server command from a prolonged wait. This can be done only by terminating the Data Protection for SQL Server program (close the command prompt window or enter control-c).
  • If the management class for meta objects also requires removable media, Data Protection for SQL Server waits for that volume, but because meta objects are not created until after the data objects are complete, the wait occurs after the data is transferred.
  • If you specify no and any removable media are required, Data Protection for SQL Server terminates the command with an error message. This is also true if the management class for meta objects requires removable media. Since the meta objects are not created until after the data objects are complete, the command termination does not occur until after the database data is transferred.
  • If you do not specify /mountwait, the default value is that specified in the mountwait configurable option in the Data Protection for SQL Server configuration file. This is initially yes. Specifying this parameter does not change the value in the configuration file.
/OFFLOAD
Specify this parameter to perform the backup of files to IBM Spectrum Protect on the machine specified by the remotedsmagentnode instead of the local machine. This parameter is valid when the following parameters and options are set: /backupmethod=VSS and /backupdestination=TSM. Note that this parameter requires a VSS provider that supports transportable shadow copies. You cannot specify this parameter with the default Windows VSS System Provider.
/SQLAUTHentication=INTegrated | SQLuserid
This parameter specifies the authorization mode used when logging on to the SQL Server. The integrated value specifies Windows authentication. The user id you use to log on to Windows is the same id you will use to log on to the SQL Server. This is the default value.

Use the sqluserid value to specify SQL Server user id authorization. The user id specified by the /sqluserid parameter is the id you use to log on to the SQL Server. Any SQL Server user id must have the SQL Server SYSADMIN fixed server role.

/SQLBUFFers=numsqlbuffers
The /sqlbuffers parameter specifies the total number of data buffers SQL Server uses to transfer data between SQL Server and Data Protection for SQL Server. The numsqlbuffers variable refers to the number of data buffers to use. The number can range from 0 to 999. The initial value is 0. When /sqlbuffers is set to 0, SQL Server determines how many buffers should be used.
Considerations:
  • The default value is the value specified by the SQL Server buffers configurable option in the Data Protection for SQL Server configuration file. This is initially 0.
  • If you specify /sqlbuffers, its value is used instead of the value stored in the Data Protection for SQL Server configuration file. Specifying this parameter does not change the value in the configuration file.
  • If you specify /sqlbuffers but not numsqlbuffers, the default value 0 is used.
/SQLBUFFERSIze=sqlbuffersizeinkb
The /sqlbuffersize parameter specifies the size of each buffer (specified by the /sqlbuffers parameter) SQL Server uses to transfer data to Data Protection for SQL Server. The sqlbuffersizeinkb variable refers to the size of data buffers in kilobytes. The number can range from 64 to 4096. The default is 1024.
Considerations:
  • The default value is the value specified by the SQL Server buffers configurable option in the Data Protection for SQL Server configuration file. This is initially 1024.
  • If you specify /sqlbuffersize, its value is used instead of the value stored in the Data Protection for SQL Server configuration file. Specifying this parameter does not change the value in the configuration file.
  • If you specify /sqlbuffersize but not sqlbuffersizeinkb, the default value 1024 is used.
/SQLCHECKSum=No|Yes
The /SQLCHECKSum parameter is used to verify the integrity of a legacy database backup. Integrity checking is a process that validates the values in a file or configuration for unexpected changes. Values are verified between the current state and the baseline state.
You can specify the following values:
No
Do not enable integrity checking for a legacy database backup. This value is the default.
Yes
Enable integrity checking for a legacy database backup.

In the Performance Properties window of Microsoft Management Console, you can enable or disable the checksum option for all your legacy databases at once. You can override the global setting, and temporarily enable or disable the checksum option for a database backup, by setting this SQLCHECKSum parameter value to Yes or No.

/SQLCOMPression=No|Yes
The /SQLCOMPression parameter specifies whether SQL compression is applied. If you do not specify /SQLCOMPression, the Nodefault value is used. This parameter is only applicable on systems running SQL Server 2008 and later versions. For SQL Server 2008,you can run backup compression only on the Enterprise Edition. For SQL Server 2008 R2, you can run backup compression on Standard, Enterprise, and Datacenter editions.
/SQLPassword=sqlpasswordname
This parameter specifies the SQL Server password that Data Protection for SQL Server uses to log on to the SQL Server that objects are backed up from or restored to.
Considerations:
  • Using this parameter means that you are using SQL Server authentication. The SQL Server and the SQL Server user id for this password must both be configured for SQL Server authentication.
  • If you do not specify /sqlpassword, the default value is blank (" ").
  • If you specify /sqlpassword but not sqlpasswordname, the default is also blank (" ").
  • This parameter is ignored if you use the /sqlauth=integrated parameter with it.
/SQLSERVer=sqlprotocol:sqlservername, port
The /sqlserver parameter specifies the SQL Server that Data Protection for SQL Server logs on to. The sqlprotocol variable specifies the communication protocol to use. With this variable, you can specify an sqlservername and, in the case of the tcp protocol, you can also define a port. You can choose from the following protocols:
Table 1.
Protocol Name Description Example Usage
lpc Shared Memory /sqlserver=lpc: <servername>\<instancename>
np Named Pipes /sqlserver=np:<servername>\pipe
Note: You can optionally specify a specific named pipe. For example, /sqlserver=np: \\hostname\pipe\pipe name

By default, the pipe name is sql\query. If you connect to a named instance, the pipe name is typically in the following format: \\<servername>\pipe\MSSQL$<instancename>\SQL\query

tcp Transmission Control /sqlserver=tcp:<servername>[\<instancename>],<port>
Note: For tcp protocols only, you have the option of defining a port. If you do not define a port, the default port value is 1443.
via
Note: SQL Server only supports this protocol through SQL Server 2008 R2.
 Virtual Interface Architecture /sqlserver=via:<virtualservername>[\instancename]
If you do not specify a protocol, Data Protection for SQL Server logs on to the SQL Server according to the first protocol that becomes available.
Considerations:
  • The default value is the value specified by the SQL Server configurable option in the Data Protection for SQL Server configuration file. This is initially the local computer name.
  • If you specify /sqlserver but not sqlservername, the local computer name is used.
  • The following two shortcuts are accepted as the local computer name: . (local) That is, a period or the word local within parentheses.
  • If the SQL Server is a member of a fail-over cluster, the CLUSTERNODE option in the IBM Spectrum Protect options file must have the value YES.
  • You must specify the name if the SQL Server is not the default instance or is a member of a fail-over cluster.
  • The format of sqlservername depends on what type of instance it is and whether it is clustered or not:
    Format Instance? Clustered? Name required?
    local-computername default no no
    local-computername\instancename named no yes
    virtualservername default yes yes
    virtualservername\instancename named yes yes
localcomputername
The network computer name of the computer the SQL Server and Data Protection for SQL Server reside on. The TCP/IP host name may not always be the same.
instancename
The name given to the named instance of SQL Server specified during installation of the instance.
virtualservername
The name given to the clustered SQL Server specified during clustering service setup. This is not the cluster or node name.
/SQLUSer=sqlusername
The /sqluser parameter specifies the name that Data Protection for SQL Server uses to log on to the SQL Server.
Considerations:
  • Using this parameter means that you are using SQL Server authentication. The SQL Server and the SQL Server user id for this password must both be configured for SQL Server authentication.
  • The SQL Server user id must have the SQL Server SYSADMIN fixed server role.
  • If you do not specify /sqluser, the default is sa.
  • If you specify /sqluser but not sqlusername, the default is also sa.
  • This parameter is ignored if you use the /sqlauth=integrated parameter with it.
/STRIPes=numstripes
The /stripes parameter specifies the number of data stripes to use in a backup or restore operation. The numstripes variable can range from 1 to 64.
Considerations:
  • If you do not specify /stripes, the default value is that specified in the Data Protection for SQL Server configuration file. The initial value is 1.
  • If you specify /stripes but not numstripes, the stored value is used.
  • You may use up to the number used to create the backup. You can determine the number of data stripes used to create a backup object with the Data Protection for SQL Server command: query tsm dbname backup_object
  • You must use the MAXNUMMP parameter on an IBM Spectrum Protect REGISTER NODE or UPDATE NODE command to allow a node to use multiple sessions to store data on removable media (which requires you to allocate multiple mount points to that node). The MAXNUMMP value must be equal to or greater than the maximum number of stripes you desire.
  • When you use data striping, you should use IBM Spectrum Protect server file space collocation to try to keep each stripe on a different storage volume.
  • The maximum number of data stripes you can use is one less than the value of the IBM Spectrum Protect server TXNGROUPMAX option in the dsmserv.opt file.
/TSMNODe=tsmnodename
The /tsmnode parameter specifies the IBM Spectrum Protect node name that Data Protection for SQL Server uses to log on to the IBM Spectrum Protect server. This identifies which IBM Spectrum Protect client is requesting services. You can also store the node name in the options file. The command line parameter overrides the value in the options file.
Considerations:
  • You cannot use the /tsmnode parameter if PASSWORDACCESS GENERATE is specified in the IBM Spectrum Protect options file. You must specify the nodename in the options file. Otherwise, you can change PASSWORDACCESS to PROMPT to utilize the /tsmnode parameter. For more information about the IBM Spectrum Protect options file, see Creating and modifying the client system-options file .
  • If you do not specify /tsmnode, the default value is that specified by the nodename option in the IBM Spectrum Protect options file. Specifying this parameter does not change the value in the options file.
/TSMOPTFile=dsmoptfilename
The /tsmoptfile parameter specifies the IBM Spectrum Protect options file to use. This is similar to selecting an IBM Spectrum Protect server from the server list in the GUI. The IBM Spectrum Protect options file contains the configuration values for the IBM Spectrum Protect API. For more information about the IBM Spectrum Protect options file, see Creating and modifying the client system-options file .
Considerations:
  • The tsmoptfilename variable can include a fully qualified path. If you do not include a path, the directory where Data Protection for SQL Server is installed is used.
  • If tsmoptfilename includes spaces, you must enclose it in double quotes.
  • If you do not specify /tsmoptfile, the default value is dsm.opt.
  • If you specify /tsmoptfile but not tsmoptfilename, the default is also dsm.opt.
/TSMPassword=tsmpasswordname
The /tsmpassword parameter specifies the IBM Spectrum Protect password that Data Protection for SQL Server uses to log on to the IBM Spectrum Protect server. This parameter and the option PASSWORDACCESS in the IBM Spectrum Protect options file interact in the following ways:
/tsmpassword PASSWORDACCESS in IBM Spectrum Protect options file Password already stored in registry? Result
specified generate yes /tsmpassword ignored
specified generate no /tsmpassword used and stored
specified prompt — /tsmpassword used
not specified prompt — user is prompted
/USEALWAYSONnode
Specify this parameter to back up standard databases on SQL Server 2012 and later versions by using the AlwaysOn node. By setting this parameter, you can back up all availability databases and standard databases under a single node to help you to manage your database backups more easily. By default, SQL Server 2012 and later version availability databases are backed up to the AlwaysOn node.