Restore optional parameters

Use the /BACKUPDESTination parameter to specify the location from where the backup is to be restored. The default is the value (if present) specified in the Data Protection for SQL Server preferences file (tdpsql.cfg). If no value is present, the backup is restored from IBM Storage® Protect server storage.

You can specify:

TSM: The backup is restored from IBM Storage Protect server storage. This option is the default if no value is specified in the Data Protection for SQL Server preferences file (tdpsql.cfg).
LOCAL: The backup is restored from the local shadow volumes.

Use the /BACKUPMETHod parameter to specify the manner in which the restore is completed. The default is the value (if present) specified in the Data Protection for SQL Server preferences file (tdpsql.cfg). If no value is present, the backup is restored with the legacy API.

You can specify:

LEGACY: The restore operation is completed with the legacy API. This option is the default if no value is specified in the Data Protection for SQL Server preferences file (tdpsql.cfg).
VSS: The restore operation is completed with VSS.

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 Storage 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 also increase storage use. Each buffer is the size that is specified in the /BUFFERSIze parameter.
The default value is the value that is specified by the buffers configurable option in the Data Protection for SQL Server configuration file. This value is initially 3.
If you specify /BUFFers, its value is used instead of the value that is 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.

The /BUFFERSIze parameter specifies the size of each Data Protection for SQL Server buffer that is specified by the /BUFFers parameter. The buffersizeinkb variable refers to the size of data buffers in KB. The number can be in the range 64 - 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 that is specified by the buffers configurable option in the Data Protection for SQL Server configuration file. This value is initially 1024.
If you specify /BUFFERSIze, its value is used instead of the value that is 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.

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 includes 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 quotation marks.
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.

Specifying the /DBOonly parameter prevents general users from accessing a restored database before it is determined to be ready for such access. This parameter ensures that the database option RESTRICTED USER is set after a restore operation.

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. Use /fromsqlserver for query FCM commands, but use /sqlserver for query SQL commands. The default value is the /sqlserver value or the value that is set in the Data Protection for SQL Server configuration file. If the two SQL Server names are different, you must use this parameter even if /fromsqlserver was a non-clustered default instance.

Use the /INSTANTRestore parameter to specify whether to use volume level snapshot or file level copy to restore a VSS backup that is stored on local shadow volumes. An IBM® Systems Storage SAN Volume Controller, DS8000®, the XIV® system, or IBM Storwize® V7000 storage system is required to run VSS instant restores.

You can specify:

Yes: Use volume level snapshot restore for a VSS backup that is stored on local shadow volumes if the backup exists on volumes that support it. This option is the default.
No: Use file-level copy to restore the files from a VSS backup that is stored on local shadow volumes. Bypassing volume-level copy means that SQL Server database files and log files are the only data overwritten on the source volumes.

When you are running VSS instant restore on IBM System Storage® DS8000 series and Storwize family, ensure that any previous background copies that involve the volumes you are restoring, complete before you initiate the VSS instant restore.

Set the /IncludeTSMVM to Yes to view all database backups that includes Virtual Environment backup SQL Server databases in the Databases view. The backup method is listed as TSMVM to distinguish these databases from the others that are listed.

Alternatively, open the Properties page from the Actions pane, and select Data Center Node to choose IncludeTSMVM to access databases from Virtual Environments in the Databases view.

Use the /INSTANTRestore parameter to specify whether to use volume level snapshot or file level copy to restore a VSS backup that is stored on local shadow volumes. An IBM Systems Storage SAN Volume Controller, DS8000, the XIV system, or IBM Storwize V7000 storage system is required to run VSS instant restores.

You can specify:

Yes: Use volume level snapshot restore for a VSS backup that is stored on local shadow volumes if the backup exists on volumes that support it. This option is the default.
No: Use file-level copy to restore the files from a VSS backup that is stored on local shadow volumes. Bypassing volume-level copy means that SQL Server database files and log files are the only data overwritten on the source volumes.

When you are running VSS instant restore on IBM System Storage DS8000 series and Storwize family, ensure that any previous background copies that involve the volumes you are restoring, complete before you initiate the VSS instant restore.

For restore operations, /INTO specifies the SQL Server database that you want a backup object that is restored into. This parameter is necessary only when the name of the SQL Server database to restore into is different from the backup object database name. Considerations:

When you specify /INTO, wildcards (*) might not be used in either the command dbname variable or the /INTO dbname variable.
There must be exactly one item in the /INTO dbname variable list in addition to in the command dbname list.
Make sure to use the /relocatedir parameter when you specify /INTO dbname.

If change data capture is enabled for an SQL Server database and database tables, change-data-capture records that recorded activity, such as insertions, deletions, and edits to the database tables can be retained when you restore the database. The /KEEPCdc parameter is necessary if you are restoring the database to a different database name on the same SQL Server instance or to a different SQL Server instance. When you run restore operations with the /KEEPCdc parameter, you must also set /RECOVery=Yes.

This parameter applies to the following types of legacy backups: full, copy-only full, differential, file, set, and group. For VSS restore operations, you do not need to maintain change data capture information.

Note: If you are restoring the database to its original location, that is the SQL Server instance and database name, change data capture records are retained automatically.

For more information about change data capture, see Microsoft documentation.

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 /LOGFile 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 off Data Protection for SQL Server logging activity. If you do not specify /LOGFile, log records are written to the default log file. The default log file is tdpsql.log.
When you use multiple simultaneous instances of Data Protection for SQL Server for operations, use the /LOGFile parameter to specify a different log file for each instance used. This function 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.

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.

This parameter is not valid for all backup types; does not work with DIFFFULL or LOG backup types. If the IBM Storage Protect server is configured to store backup data on removable media, the IBM Storage Protect server might send a message to indicate to Data Protection for SQL Server that the server is waiting for a required storage volume to be mounted. If that occurs, with this option, you can specify whether Data Protection for SQL Server backup, restore, and query TSM /fileinfo commands wait for the media mount or stop the current operation.

You can specify:

Yes: Wait for tape mounts (default for backup and restore).
No: Do not wait for tape mounts (default for query TSM /fileinfo).

Considerations:

If you use data striping, Data Protection for SQL Server cannot complete the operations unless it has concurrent access to the media for each stripe. Because of the way SQL Server distributes data among stripes, if any stripe does not have its media available, each of the stripes might eventually be either waiting for its own or another stripe's media to become available. In this case, it might become necessary to end the Data Protection for SQL Server command from a prolonged wait. This action can be completed by closing the Data Protection for SQL Server program (close the command prompt window or enter control-c).
For backup, 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 all of the data is transferred.
If you specify no and any removable media are required, Data Protection for SQL Server ends the command with an error message. The same outcome happens if the management class for meta objects requires removable media. For backup, since the meta objects are not created until after the data objects are complete, the command termination does not occur until after all of the database data is transferred.
If you do not specify /MOUNTWait with backup or restore, the default value is that specified in the mountwait configurable option in the Data Protection for SQL Server configuration file. This value is initially yes. Specifying this parameter does not change the value in the configuration file.
If you specify /MOUNTWait, but do not specify either yes, or no, the default, yes, is used.
If you do not specify /MOUNTWait with a query TSM /fileinfo request, the default value no is used.

For restore and deactivate operations, /OBJect specifies that only particular backup objects for the specified SQL Server databases and backup object type if specified are restored. 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 deactivate. 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. Considerations:

If you do not specify restore, only the active backup object is included in the restore.
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.

The /PARTial parameter restores only part of an SQL Server database. You can complete partial restores only on full database backup objects. The primary purpose of a partial restore is to retrieve lost or damaged data. A partial restore creates a subset of the SQL Server database. After the partial restore, differential database restores and transaction log restores can return the subset SQL Server database to a point where the required data exists or is undamaged. You can then copy the required data from the subset SQL Server database to the original SQL Server database. You can also use partial restores whenever you need a subset of an SQL Server database, such as for development or reporting purposes.

A partial restore always restores the entire backup object from the IBM Storage Protect server although only a portion of the restored object might be used to complete a recovery. The statistics that are displayed reflect the amount of data that is restored from the IBM Storage Protect server only, not the amount of data that is used by the SQL Server for database recovery.

Considerations:

You can specify the content of a partial restore with the files or groups parameters.
- The primary group is always included.
- SQL Server groups that are not restored are marked offline and are not accessible.
If you are restoring the subset SQL Server database to a location where it was backed up, you must use the /RELocate and /to parameters.
Microsoft™ Management Console (MMC) does not support the /RELocate and /to parameters. You must use the command-line interface when you are completing a partial restore that requires these parameters.
You can specify the /RECOVery parameter with /PARTial.

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.

Note: This parameter is not relevant for all query commands. It is applicable to the query TSM command only.

The /Quiet parameter omits displaying status information from the command. However, the information is appended to the IBM Storage Protect Snapshot activity log.

For restore operations, /RECOVery specifies whether you want to restore more SQL Server database that are not on a standby SQL Server. A restored database cannot be used until the /RECOVery=Yes parameter is administered to the database. You can specify:

Yes (default): Whenever you make a sequence of restores to an SQL Server database and the current restore is the final restore in the sequence, or is the only restore to an SQL Server database. The SQL Server detects that the restore operation is complete and ready for incomplete transactions to be rolled back.
No: Whenever you make a sequence of restores to an SQL Server database and the current restore is not the final restore in the sequence. Issue /RECOVery=no for all restore commands except the last one.

Considerations:

After the /RECOVery=yes parameter is administered, you cannot restore any more differential or log backups to the database.
You cannot specify /RECOVery for restore operations of file, group, or set backup objects. Data Protection for SQL Server forces such restores to /RECOVery=no.
For full restores that specify /groups or /files, unless you also specify /partial, you cannot specify /RECOVery. Without /partial, Data Protection for SQL Server forces such restores to /RECOVery=no.
Not specifying this option automatically rolls back incomplete transactions for the database.
When you specify yes and you are restoring several restore objects for the same database, only the final restore object for the database uses /RECOVery=yes; all others use /RECOVery=no. You can specify a list of logs without having to specify the final log in a separate command.

The following is a sample scenario:


Sequence of restore operations	Specify
full database	no
differential database	no
transaction log backup object	no
transaction log backup object	yes

Data Protection for SQL Server sorts the restore objects by database name, and, within database name, by backup time stamp from earliest to latest. A query TSM command also displays this order.
If a restore object fails, all subsequent restore objects for that database in a single restore command are skipped regardless of the /RECOVery or /STANDby settings.

For restore operations, the /RELocate and /TO parameters as a pair specify the new location of an SQL Server database file. You must use this parameter for every SQL Server database file that you are not restoring to its original drive, complete path, and file name. The logicalfilename variable specifies the logical file name of the SQL Server database file you want to relocate. The physicalfilename variable specifies the new physical Windows™ file name where you want to relocate the SQL Server database file. This parameter is available when you are restoring legacy backups only.

Considerations:

You cannot specify more than one database name as the value for the restore command when you are specifying /RELocate.
/RELocate and /TO can each take a list of values and can be specified more many times. However, as a pair, /RELocate and /TO must take the same number of values, and the values must be paired in order of appearance. For example,
```
/relocate=a,b,c /to=a¹,b¹,c¹ 
 
```
is valid, but not
```
/relocate=a,b,c /to=b¹,a¹
 
```
MMC does not support the /RELocate and /TO parameters. You must use the command-line interface when you are completing a partial restore that requires these parameters.
You can use the query command with the /fileinfo parameter to determine the logical file names and physical file names in the backup object.
If either logicalfilename or physicalfilename includes spaces, you must enclose it in double quotation marks.
For physicalfilename, include the complete drive, path, and file name of the new file.
The drive and path of the new physical file name must exist, but if the file does not yet exist, SQL Server creates it. Additionally, if the file does exist, you might be required to use the /replace parameter.
The wildcard (*) is not allowed in the values for either /RELocate or /TO.

The /RELOCATEDir parameter specifies the new destination locations in which to restore the backed up SQL Server databases, logs, and SQL Server full-text index files. FILESTREAM files are included for SQL Server 2008, SQL Server 2008 R2, or later versions. This parameter is available when you are restoring VSS backups or legacy backups.

The dbfiledir variable specifies the directory location of the SQL Server database you want to relocate. If the logfiledir and otherfiledir variables are not specified, the logs and SQL Server full-text index files are restored to the directory specified by dbfiledir.

The logfiledir variable specifies the directory location of the SQL Server log files you want to relocate. If the logfiledir variable is not specified, the SQL Server log files are restored to the directory specified by dbfiledir.

The otherfiledir variable specifies the directory location of the SQL Server full-text index files and FILESTREAM files (SQL Server 2008, SQL Server 2008 R2, or later versions). If the otherfiledir variable is not specified, the SQL Server full-text index files and FILESTREAM files (SQL Server 2008, SQL Server 2008 R2, or later versions) are restored to the directory specified by dbfiledir.

For restore operations, the /REPlace parameter specifies that you want existing SQL Server files to be overwritten when they otherwise would not be.

Restriction: This restore option is available only with legacy backups.

You can use this parameter in the following instances:

You are completing a full database restore, and one of the following statements is true:
- You are using the /into parameter, and the /into database exists on the SQL Server.
- The database exists on the SQL Server, and one of the following statements is also true:
  - The number of SQL Server files in the existing database differs from the number of SQL Server files in the full database backup object.
  - The names of one or more SQL Server files in the existing database are not the names of any of the SQL Server files in the full database backup object.
You are completing a file, group, or set restore, and one or more of the SQL Server files exist.

The /RESTOREDAte parameter specifies a date to which the database identified by dbname is to be recovered. The date value must be specified in the same date format that is defined in the Data Protection for SQL Server preferences file. If /RESTOREDAte is not specified but /RESTORETime is specified, the /RESTOREDAte value is the current date. The /RESTOREDAte parameter is only available for legacy restore operations. It can be specified only when you are restoring a full database backup. The /RESTORETime parameter cannot be used to restore file, group, and set backups.

The /RESTORETime parameter specifies the time of day to which the database identified by dbname is to be recovered. The time value must be specified in the same time format that is defined in the Data Protection for SQL Server preferences file. If /RESTORETime is not specified but /RESTOREDAte is specified, the /RESTORETime is the current time. The /RESTORETime parameter is only available for legacy restore operations. It can be specified only when you are restoring a full database backup. The /RESTORETime parameter cannot be used to restore file, group, and set backups.

This parameter specifies the authorization mode that is used when logging on to the SQL Server. The INTegrated value specifies Windows authentication. The user ID that 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 that is specified by the sqluserid parameter is the ID that you use to log on to the SQL Server. Any SQL Server user ID must have the SQL Server SYSADMIN fixed server role.

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 must be used.

Considerations:

The default value is the value that is specified by the SQL Server buffers configurable option in the Data Protection for SQL Server configuration file. This value is initially 0.
If you specify /SQLBUFFers, its value is used instead of the value that is 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.

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 KB. The number be in the range 64 - 4096. The default value is 1024.

Considerations:

The default value is the value that is specified by the SQL Server buffers configurable option in the Data Protection for SQL Server configuration file. This value is initially 1024.
If you specify /SQLBUFFERSIze, its value is used instead of the value that is 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.

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.

The /SQLSERVer parameter specifies the SQL Server that Data Protection for SQL Server logs on to. For restore operations, this server is the SQL Server that backup objects are restored to. However, if the backup objects were created from a different SQL Server name, you must use the /fromsqlserver parameter. Use /sqlserver for the query SQL and backup commands, but use /fromsqlserver for the query TSM and inactivate commands.

The sqlprotocol variable specifies the communication protocol to use. 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:

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.

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 that is specified by the SQL Server configurable option in the Data Protection for SQL Server configuration file. This value 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 failover 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

local-computername: The network computer name of the computer where the SQL Server and Data Protection for SQL Server is located. The TCP/IP host name might not always be the same.
instancename: The name that is given to the named instance of SQL Server that is specified during installation of the instance.
virtualservername: The name that is given to the clustered SQL Server specified during clustering service setup. This name is not the cluster or node name.

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 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.

Specifies that the restore is to a standby SQL Server, and specifies the name of an undo file.

Considerations:

You cannot specify more than one database name as the restore command value.
A standby SQL Server can be in read-only mode between restores and can accept more restore operations to its databases.
You can use the same undo file for a database for each restore to the database, but you cannot use a single undo file for more than one database.
The undofilename variable can include a fully qualified path. However, if a fully qualified path is not specified, the undo file is created in the directory that is specified by the %TEMP% environment variable.
If undofilename includes spaces, you must enclose it in double quotation marks.
If the specified undo file does not exist, SQL Server creates it. If the file exists but was not used for the same SQL Server database, SQL Server overwrites it.
If you do not specify either /recovery or /STANDby, the default is /recovery=yes.

For restore operations, /STOPAT specifies the point in time that you restore an SQL Server database to. Only transaction logs that are written before the point in time is applied to the SQL Server database. The datetime variable specifies both the date and time, separated by a space. Use any valid date and time format that is accepted by SQL Server.

Considerations:

This parameter applies only to transaction log restores, but the base restore that the transaction logs apply to must be a full database restore. You cannot restore file, group, and set restores to a point in time.
You cannot also specify /recovery=no or /standby with the /STOPATparameter.
Because datetime includes a space, you must enclose it in double quotation marks.
If the restore operation with the /STOPAT parameter does not encounter a transaction in the restored transaction log that has a time stamp equal to or greater than the specified point in time, the SQL Server database remains in an unrecovered state, even if you also specify /recovery=yes.

The /STOPATMark parameter specifies a named point in time to restore a database to. You can specify a time after a specified point in time if you specify the /AFTER option. Only transaction log records written up to and including the named transaction (which can be found at or after the specified point in time) are applied to the SQL Server database. The markname variable specifies the name of an SQL Server transaction. The SQL Server transaction might be a local transaction or a distributed transaction. If it is a distributed transaction name, the named mark exists in the transaction log of each SQL Server database in the distributed transaction.

markname is the transaction name, not the description that follows the MARK keyword in an SQL BEGIN TRANSACTION or BEGIN DISTRIBUTED TRANSACTION statement.

The datetime variable specifies both the date and time, separated by a space. Use any valid date and time format that is accepted by SQL Server.

Considerations:

This parameter applies only to transaction log restores. The base restore that the transaction logs apply to must be a full database restore. You cannot restore file, group, and set restores to a mark.
You can use the same named mark for several SQL Server transactions.
If you do not specify /AFTER, the restore stops at the first mark it encounters with the specified name.
If you specify /AFTER, the restore stops at the first mark it encounters with the specified name after the specified date and time.
If markname includes spaces, you must enclose it in double quotation marks.
You cannot use a Data Protection for SQL Server restore command with /STOPATMark and also specify /recovery=no or /standby.
If the restore operation with /STOPATMark does not encounter a transaction in the restored transaction log to stop at, the SQL Server database remains in an unrecovered state, even if you also specify /recovery=yes.

This parameter specifies a named point in time to restore a database to. You can specify a later point in time if you specify the /AFTER option. Only transaction log records written before and not including the named transaction (which can be found at or after the specified point in time) are applied to the SQL Server database. The markname variable specifies the name of an SQL Server transaction. The SQL Server transaction might be a local transaction or a distributed transaction. If it is a distributed transaction name, the named mark exists in the transaction log of each SQL Server database in the distributed transaction.

markname is the transaction name, not the description that follows the MARK keyword in an SQL BEGIN TRANSACTION or BEGIN DISTRIBUTED TRANSACTION statement.

The datetime variable specifies both the date and time, separated by a space. Use any valid date and time format that is accepted by SQL Server.

Considerations:

This parameter applies only to transaction log restores. The base restore that the transaction logs apply to must be a full database restore. You cannot restore file, group, and set restores to a mark.
You can use the same named mark for several SQL Server transactions.
If you do not specify /AFTER, the restore stops before the first mark it encounters with the specified name.
If you specify /AFTER, the restore stops before the first mark it encounters with the specified name, or after the specified date and time.
If markname includes spaces, you must enclose it in double quotation marks.
You cannot use a Data Protection for SQL Server restore command with /STOPBEFOREMark and also specify /recovery=no or /standby.
If the restore operation with /STOPBEFOREMark does not encounter a transaction in the restored transaction log to stop before, the SQL Server database remains in an unrecovered state, even if you also specify /recovery=yes.

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. For restore, the value is the same value that is used in the backup operation.
If you specify /STRIPes but not numstripes, the stored value is used.
Restore must use the same number of stripes as were used for backup. You can determine the number of data stripes that are 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 Storage 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 that you require.
When you use data striping, you must use IBM Storage 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 theIBM Storage Protect server TXNGROUPMAX option in the dsmserv.opt file. SQL Server allows a maximum of 64 data stripes.

The /tsmnode parameter specifies the IBM Storage Protect node name that Data Protection for SQL Server uses to log on to the IBM Storage Protect server. This parameter identifies which IBM Storage 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 Storage Protect options file. You must specify the node name in the options file. Otherwise, you can change PASSWORDACCESS to PROMPT to use the /TSMNODe parameter. For more information about the IBM Storage 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 node name option in the IBM Storage Protect options file. Specifying this parameter does not change the value in the options file.

The /TSMOPTFile parameter specifies the IBM Storage Protect options file to use. This operation is similar to selecting an IBM Storage Protect server from the server list in the GUI.

The IBM Storage Protect options file contains the configuration values for the IBM Storage Protect API. For more information about the IBM Storage 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 quotation marks.
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.

The /TSMPassword parameter specifies the IBM Storage Protect password that Data Protection for SQL Server uses to log on to the IBM Storage Protect server. This parameter and the option PASSWORDACCESS in the IBM Storage Protect options file interact in the following ways:


/TSMPassword	PASSWORDACCESS in IBM Storage 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

The /VerifyOnly parameter specifies that a database restore action reads backup data to verify the integrity of the data only; it does not save the backup to disk or overwrite the current database of that name on the SQL server. Before you restore a backup, you can use this parameter to evaluate whether the backup volume is complete and can be read.

If this parameter is not specified, the restore action both verifies the integrity of the backup and also saves the backup to disk on the SQL server.

Restriction: The /VerifyOnly parameter is available only for legacy database backups. This parameter is only a command optional parameter, and it cannot be set as a configuration option.

To verify a backup done with multiple stripes, set the Verify Only parameter to Yes and ensure that the number of stripes that are used for the restore is equal to or greater than the number of stripes that are used for the backup you are verifying. If it is not, the Restore VerifyOnly operation terminates with an error.