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.
- /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.
- /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=sqlserver-connection
- The /sqlserver parameter
specifies the SQL Server that Data Protection for SQL Server logs on
to. The sqlserver-connection comprises the sqlprotocol and
sqlservername. The sqlprotocol variable specifies the
communication protocol to use and with this variable, you can specify an
sqlservername. You can check the SQL connection by using the SQL Server
Configuration Manager tool (under SQL Server Native Client Configuration client protocols). You can
choose from the following protocols:
Table 1. SQL Server connection protocols Protocol Name Description Example Usage (with sqlserver-connection details) lpc Shared Memory /sqlserver=lpc:<servername>\<instancename> np Named Pipes /sqlserver=np:<servername>\pipe You can optionally specify a specific named pipe instance. 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] via Virtual Interface Adapter /sqlserver=via:<virtualservername>[\<instancename>] Attention: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.- For tcp protocols only, you have the option of defining a port. If you do not define a port, the default port value is the SQL default port 1433.
- For the via protocol, SQL Server supports this protocol only through SQL Server 2008 R2.
- To enable Data Protection for SQL Server to communicate with AlwaysOn Availability Group (AAG) instances, it is not possible to connect to the SQL Server using AAG listeners. For backup and restore operations, you must use the local SQL Server instance name (or instance name and port number) to communicate with the AAG. For AAG (or non-AAG instances), you can also specify non-default port numbers.
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.
- If the SQL Server is not the default instance or is a member of a fail-over cluster, you must specify the name.
- 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 on which the SQL Server and Data Protection for SQL Server reside. The TCP/IP host name may not always be the same.
- instancename
- The name given to the named instance of the SQL Server that is specified during installation of the instance.
- virtualservername
- The name given to the clustered SQL Server that is specified during clustering service setup. This name 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.