Query optional parameters

Optional parameters follow the query command and positional parameters.

The following are detailed descriptions of each of the optional parameters:
/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.
/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.
/COMPATibilityinfo
For query operations, this parameter displays information related to the compatibility of a backup object with an SQL Server. Certain SQL Server configuration options must be compatible before you can restore a backup object to an SQL Server. When you specify this parameter, Data Protection for SQL Server configuration information is listed to help determine if a backup object is correct for an SQL Server, or to help in problem determination.
Considerations:
  • You cannot specify this parameter with the types parameter on a query TSM command.
  • Compatible generally means identical. However, if you use a binary sort order for both the SQL Server and the backup object, the code pages may be different, although the interpretation of individual character values may result in different characters being displayed or printed.
/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.
  • If you specify /configfile but not configfilename, the default value tdpsql.cfg is used.
/FROMSQLSERVer=sqlservername
For restore, the /fromsqlserver parameter specifies the SQL Server that backup objects were backed up from. This parameter is necessary only when the name of the SQL Server to restore to, as determined by the /sqlserver parameter, is different from the name of the SQL Server that the backup objects were created from. The default value is the /sqlserver value or the value set in the Data Protection for SQL Server configuration file.
Considerations:
  • If the two SQL Server names are different, you must use this parameter even if /fromsqlserver was a non-clustered default instance.
  • After you restore an SQL Server database to a different SQL Server, the logins of the SQL Server database may not match the logins for the different SQL Server. If appropriate, you can use the SQL stored procedure SP_CHANGE_USERS_LOGIN to find and correct such SQL Server login mismatches.
/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 specified file 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. Depending on the option that you set for the /LOGPrune parameter, a certain number of days of data are saved. By default, 60 days of log entries are saved. The option No can be entered to disable log pruning.

Regardless of the option that you set 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 no, the log file is not pruned.
  • If you do not specify /LOGPrune, the default value is that specified by the logprune configurable option in the configuration file. The default value is 60.
  • If you specify /LOGPrune, its 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.
/OBJect=*|objectname,...
For restore and inactivate operations, /object specifies that only particular backup objects for the specified SQL Server databases and backup object type (if specified) be restored or inactivated. For query operations, /object includes particular objects and object types in the display. The objectname variable specifies the names of the backup objects you want to restore or inactivate. The object name uniquely identifies each backup object and is created by Data Protection for SQL Server. Use query to view the names of backup objects. You can use * as a wildcard character in objectname to replace zero or more characters for each occurrence. Specifying only the wildcard character indicates all backup objects of the specified SQL Server databases and backup object type.
/QUERYNode=DP | ALWAYSON | BOTH
Specify whether you want to query standard databases from SQL Server 2012 and later versions that are backed up from a standard Data Protection for SQL Server node, the AlwaysOn node, or both nodes. This parameter is ignored for availability databases because the availability databases are always backed up under the AlwaysOn node.
/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 will use to log on to the SQL Server. Any SQL Server user id must have the SQL Server SYSADMIN fixed server role.
/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.
  • 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.
/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
Related reference:
Set command
Set command