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 Tivoli® Storage Manager 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 Tivoli Storage Manager 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, the /BACKUPDESTination parameter is not supported 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 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, the /BACKUPMETHod parameter is not supported 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 Tivoli Storage Manager 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 Tivoli Storage Manager. This value is the default.
No: Prevent database backups to Tivoli Storage Manager 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

The /logprune parameter prunes the Data Protection for SQL Server activity log and specifies how many days of entries are saved. By default, log pruning is enabled and completed once daily when Data Protection for SQL Server is run; however, this option allows you to disable log pruning or explicitly request a prune of the log for one command run even if the log file has already been pruned for the day. The numdays variable represents the number of days to save log entries. By default, 60 days of log entries are saved in the prune process.

Considerations:

If you specify numdays, it can range from 0 to 9999. A value of 0 deletes all entries in the Data Protection for SQL Server activity log file except for the current command entries.
If you specify /logprune, 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.
Changes to the value of the TIMEformat or DATEformat parameter can result in an unintentional pruning of the Data Protection for SQL Server log file. If you are running a command that may prune the log file and the value of the TIMEformat or DATEformat parameter has changed, perform one of the following to prevent undesired 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 Tivoli Storage Manager server is configured to store backup data on removable media, it is possible that the Tivoli Storage Manager 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 Tivoli Storage Manager 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. It is not supported 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 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 determines how many buffers should be used.

Considerations:

The default value is the value specified by the SQL 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 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 the MMC, 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, backup compression is only supported on the Enterprise Edition. For SQL Server 2008 R2, backup compression is supported on Standard, Enterprise, and Datacenter editions.

/SQLPassword=sqlpasswordname

This parameter specifies the SQL 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 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

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. You can specify one of the following protocols:

lpc: Use Shared Memory protocol.
np: Use Named Pipes protocol.
tcp: Use Transmission Control protocol.
via: Use Virtual Interface Architecture protocol.

If no protocol is specified, 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) These are 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 Tivoli Storage Manager 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 user id for this password must both be configured for SQL Server authentication.
The SQL 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 a Tivoli Storage Manager 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 Tivoli Storage Manager 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 Tivoli Storage Manager server TXNGROUPMAX option in the dsmserv.opt file.

/TSMNODe=tsmnodename

The /tsmnode parameter specifies the Tivoli Storage Manager node name that Data Protection for SQL Server uses to log on to the Tivoli Storage Manager server. This identifies which Tivoli Storage Manager 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 Tivoli Storage Manager 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 Tivoli Storage Manager 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 Tivoli Storage Manager options file. Specifying this parameter does not change the value in the options file.

/TSMOPTFile=dsmoptfilename

The /tsmoptfile parameter specifies the Tivoli Storage Manager options file to use. This is similar to selecting a Tivoli Storage Manager server from the server list in the GUI. The Tivoli Storage Manager options file contains the configuration values for the Tivoli Storage Manager API. For more information about the Tivoli Storage Manager 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 Tivoli Storage Manager password that Data Protection for SQL Server uses to log on to the Tivoli Storage Manager server. This parameter and the option PASSWORDACCESS in the Tivoli Storage Manager options file interact in the following ways:

/tsmpassword	PASSWORDACCESS in Tivoli Storage Manager 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.