Command Line Interface - Configuration - SQL Server iDataAgent

Configuration Backup Restore
Table of Contents

Log On to the CommServe

To run command line operations, you must first login to the CommServe.

From Command prompt, navigate to <Software_Installation_Directory>/Base and run the following command:

qlogin -cs <commserve name> -u <user name>

For example, to log on to CommServe 'server1' with username 'user1':

qlogin -cs server1 -u user1

Instance Configuration

Creating an Instance

  1. Download the CreateSQL_Instance_Template.xml file and save it on the computer from where the command will be executed.
  2. Execute the following command from the <Software_Installation_Directory>/Base folder after substituting the parameters values.

qoperation execute -af CreateSQL_Instance_Template.xml -appName 'SQL Server' -clientName xxxxx -instanceName xxxxx

Modifying an Instance

  1. Download the ModifySQL_Instance_Template.xml file and save it on the computer from where the command will be executed.
  2. Execute the following command from the <Software_Installation_Directory>/Base folder after substituting the parameters values.

qoperation execute -af ModifySQL_Instance_Template.xml -appName 'SQL Server' -clientName xxxxx -instanceName xxxxx

Getting Instance Properties

  1. Download the GetSQL_Instance_Properties_Template.xml  file and save it on the computer from where the command will be executed.
  2. Execute the following command from the <Software_Installation_Directory>/Base folder after substituting the parameters values.

qoperation execute -af GetSQL_Instance_Properties_Template.xml -appName 'SQL Server' -clientName xxxxx -instanceName xxxxx

Deleting an Instance

  1. Download the DeleteSQL_Instance_Template.xml file and save it on the computer from where the command will be executed.
  2. Execute the following command from the <Software_Installation_Directory>/Base folder after substituting the parameters values.

qoperation execute -af DeleteSQL_Instance_Template.xml -appName 'SQL Server' -clientName xxxxx -instanceName xxxxx

Available Parameters for Instance Configuration

The following table displays all the parameters you can use with the commands mentioned in the above sections. To add a parameter to your command, use the following syntax: (A example is provided at the end of the table.)

qoperation execute -af <template XML file> -<parameter name> <value>

Parameter Description of Parameter Values
description A general description of the instance if needed.
clientName Name of the client computer.
appName Name of the application. In this case it would be 'SQL Server'.
instanceName Name of the SQL instance. If you are specifying an instance other than the default instance, the name must be entered along with the client name, e.g. client1\instance1.
vDITimeOut The time the system must wait for the SQL server to become ready. Time value is in seconds.
useVss Option to enable/disable VSS.

Valid values are True/False.

overrideGlobalAuthentication Option to override Global Authentication.

Valid values are True/False.

useLocalSystemAccount Option to use Local Administrator account.

Valid values are 0/1.

userName Name of the database administrator who has permissions to perform jobs.
password Password for the database user
propertyLevel Properties to be retrieved for the instance.

Valid values are:

  • ListOnly - List the instance names
  • BasicProperties - Retrieves the basic properties, such as description, version, etc.,
  • ExtendedProperties - Retrieves all the properties for the instance.
  • AllProperties - Retrieves all the properties for the instance.

The following example shows how to add a parameter for a command:

Enable VSS To enable VSS for the instance, add the 'useVSS' parameter to the following command:

qoperation execute -af ModifySQL_Instance_Template.xml -appName 'SQL Server' -clientName client1 -instanceName client1\instance1 -useVSS true

Subclient Configuration

Create Subclient

  1. Download the create_subclient_template.xml file and save it on the computer from where the command will be executed.
  2. Execute the following command from the <Software_Installation_Directory>/Base folder after substituting the parameters values.

qoperation execute –af create_subclient_template.xml -appName 'SQL Server' -clientName xxxxx -instanceName xxxxx –subclientName xxxxx -dataBackupStoragePolicy/storagePolicyName xxxxx -logBackupStoragePolicy/storagePolicyName xxxxx

Modify Subclient

  1. Download the modify_subclient_template.xml file and save it on the computer from where the command will be executed.
  2. Execute the following command from the <Software_Installation_Directory>/Base folder after substituting the parameters values.

qoperation execute –af  modify_subclient_template.xml -appName 'SQL Server' -clientName xxxxx -instanceName xxxxx –subclientName xxxxx

Get Subclient Properties

  1. Download the get_subclient_properties_template.xml file and save it on the computer from where the command will be executed.
  2. Execute the following command from the <Software_Installation_Directory>/Base folder after substituting the parameters values.

qoperation execute –af get_subclient_properties_template.xml -appName 'SQL Server' -clientName xxxxx -instanceName xxxxx –subclientName xxxxx

Delete Subclient

  1. Download the delete_subclient_template.xml file and save it on the computer from where the command will be executed.
  2. Execute the following command from the <Software_Installation_Directory>/Base folder after substituting the parameters values.

qoperation execute –af delete_subclient_template.xml -appName 'SQL Server' -clientName xxxxx -instanceName xxxxx -subclientName xxxxx

Available Parameters for Subclient Configuration

The following table displays all the parameters you can use with the commands mentioned in the above sections. To add a parameter to your command, use the following syntax: (Some examples are provided at the end of the table.)

qoperation execute -af <template XML file> -<parameter name> <value>

Parameter Description of Parameter Values
appName Name of the application. In this case it would be 'SQL Server'.
backupsetName Name of the backupset. In this case, it would be 'defaultBackupSet'.
clientName Name of the client computer.
instanceName Name of the SQL instance. If you are specifying an instance other than the default instance, the name must be entered along with the client name, e.g. client1\instance1.
subclientName Name of the SQL subclient.
enableBackup Option to enable backup of Subclient.

Valid values are True/False.

encryptionFlag Option to set the encryption points during backups. Valid values are:
  • ENC_MEDIA_ONLY, to encrypt the backup data after transmission and prior to storage on the media.
  • ENC_NETWORK_AND_MEDIA, to encrypt the backup data before transmission. The data is stored encrypted on the media.
  • ENC_NETWORK_ONLY, to encrypt the backup data for transmission and then decrypt the data prior to storage on the media.
  • ENC_NONE, to disable data encryption.
readBuffersize This is used for tuning SQL Server performance.

Valid values are 64, 128, 256, 512, 1024, 2048 and 4096.

onDemandSubClient Use this option during creation of a subclient.

Valid values are True/False.

networkAgents Number of Network Agents.
softwareCompression Option to enable compression on the Client or MediaAgent computer. Valid values are:
  • ON_CLIENT, to enable software compression on the client.
  • ON_MEDIAAGENT, to enable software compression on the MediaAgent.
  • OFF, to disable software compression.
throttleNetworkBandwidth Enhancing backup performance by reducing network bandwidth overhead.

Valid values are 0/1.

storagePolicyName Name of Storage Policy to be associated for Backup.
enableDeduplication Option to enable deduplication on subclient.

Valid values are True/False.

generateSignature A component of deduplication performed on the client or MediaAgent computer. Valid values are:
  • ON_CLIENT, to enable signature generation on the client.
  • ON_MEDIA_AGENT, to enable signature generation on the MediaAgent.
  • OFF, to disable signature generation.
backupRules Option to set Backup conversion rules. Valid values are:
  • CONVERT_WITH_FILE_FILEGROUPS_NOT_ADDED, to convert log backups to full if a log backup was performed using other software with file or file group not added.
  • CONVERT_WITH_FILE_FILEGROUPS_ADDED, to convert log backups to full if a log backup was performed using other software with file or file group added.
  • DONOT_CONVERT_WITH_FILE_FILEGROUPS_NOT_ADDED, do not convert log backups to full if a log backup was performed using other software with file or file groups are not added.
  • DONOT_CONVERT_WITH_FILE_FILEGROUPS_ADDED, do not convert log backups to full if a log backup was performed using other software with file or file groups are added.
  • CONVERT_DISABLED, to disable backup conversion rules.
bufferCount Number of data blocks to use during backups to improve performance.
maxTransferSize Maximum number of bytes to transfer at a time to tune performance.

Valid values in bytes are 65536, 131072, 262144, 524288, 1048576, 2097152, and 4194304.

numberOfBackupStreams Number of Backup Streams.
numberOfTransactionLogStreams Number of Transaction Log Streams.
runPostBackup Option to run a process after backup completes.

Valid values are Yes/No.

postBackupCommand Path to the post process script that will be run after the backup.
preBackupCommand Path to the pre process script that will be run before the backup.
runAs Option to specify the user name who has permissions to run the pre/post process scripts. Valid values are:
  • USE_IMPERSONATION, to specify a user with enough privileges to run the scripts. When using this value, you also need to provide the user credentials:

    -prepostUserName/userName <user name> -prepostUserName/password <password>

  • USE_LOCAL_SYS_ADMIN, to use the administrator account to run the scripts.
sqlSubclientType Type of SQL subclient. Valid values are:
  • DATABASE, to back up groups of specific databases.
  • FILE_FILEGROUP, to back up portions of data from large databases.
contentOperationType Modification type. Valid values are
  • ADD, to add content to the subclient
  • OVERWRITE, to overwrite contents to the subclient.
  • DELETE, to delete the contents from the subclient.
disableLogConsistencyCheck Option to disable log consistency check.

Valid values are True/False.

The following examples show how to add a parameter for a command:

Enable Backup To enable backup for a subclient, add the 'enableBackup' parameter to the following command:

qoperation execute –af  modify_subclient_template.xml -appName 'SQL Server' -clientName client1 -instanceName client1\instance1 –subclientName subclient1 -enableBackup true

Setting a Storage Policy To assign a storage policy to a subclient, add the 'storagePolicyName' parameter to the following command:

qoperation execute –af modify_storage_policy.xml -appName 'SQL Server' -clientName client1 -instanceName client1\instance1 –subclientName subclient1 -dataBackupStoragePolicy/storagePolicyName SPdata -logBackupStoragePolicy/storagePolicyName SPlog

Creating an On-Demand Database Subclient To create an on demand database subclient, add the 'onDemandSubClient' and 'sqlSubclientType' parameters to the following command:

qoperation execute –af create_subclient_template.xml –appName ‘SQL Server’ -clientName client1 -instanceName client1\instance1 –subclientName subclient1 –onDemandSubClient true –sqlSubclientType DATABASE –dataBackupStoragePolicy/storagePolicyName SPData –logBackupStoragePolicy/storagePolicyName SPLog

Creating an On-Demand File/FileGroup Subclient To create an on demand file/filegroup subclient, add the 'onDemandSubClient' and 'sqlSubclientType' parameters to the following command:

qoperation execute –af create_subclient_template.xml –appName ‘SQL Server’ -clientName client1 -instanceName client1\instance1 –subclientName subclient1 –onDemandSubClient true –sqlSubclientType FILE_FILEGROUP –dataBackupStoragePolicy/storagePolicyName SPData –logBackupStoragePolicy/storagePolicyName SPLog

Adding Content to a Subclient To add content to a subclient, add the 'contentOperationType' parameter to the following command:

qoperation execute -af modify_subclient_content.xml -appName 'SQL Server' -clientName client1 -instanceName client1\instance1 –subclientName subclient1 -sqlSubclientType DATABASE -mssqlDbContent/databaseName DB1 -contentOperationType ADD

Overwriting Content to a Subclient To overwrite content to a subclient, add the 'contentOperationType' parameter to the following command:

qoperation execute -af modify_subclient_content.xml -appName 'SQL Server' -clientName client1 -instanceName client1\instance1 –subclientName subclient1 -sqlSubClientType DATABASE -mssqlDbContent/databaseName DB1 -contentOperationType OVERWRITE

Specifying Databases for On-Demand Backup

On-Demand backups allow content to be specified as an external input at the time of initiating a data protection operation. Whereas traditional backups operations are performed on subclients, which have fixed content configured prior to performing the operation. On Demand backup allows you the flexibility of specifying content each time you perform a backup operation.

Use the following procedure to configure and perform an On-Demand backup for Database subclients:

  1. Download the create_ondemand_DBsub_template.xml file and save it on the computer from where the command will be executed.
  2. Execute the following command from the <Software_Installation_Directory>/Base folder after substituting the parameters values.

    qoperation execute –af create_ondemand_DBsub_template.xml –clientName client1 –instanceName client1\instance1 –subclientName subclient1 -dataBackupStoragePolicy/storagePolicyName SPdata -logBackupStoragePolicy/storagePolicyName SPlog -sqlSubclientType DATABASE -onDemandSubClient true

    For information on the parameters used in this command, see Available Parameters for Subclient Configuration.

  3. Create a database list file (as a text file) for the databases you plan to backup. This database list file must be located on the Client computer, for which subclient needs to be created.

    You may specify any databases to be backed up using the Database File List. There must be one entry per line in the file.

  4. Download the backup_template.xml file and save it on the computer from where the command will be executed.
  5. Execute the following command from the <Software_Installation_Directory>/Base folder after substituting the parameters values.

    qoperation execute -af c:\backup_template.xml -clientName client1 -instanceName client1\instance1 -subclientName subclient1 –ondemandinputfile c:\DatabaseFile.txt –backupLevel FULL

Specifying File and File Groups for On-Demand Backup

On-Demand backup allows content to be specified as an external input at the time of initiating a data protection operation. Whereas traditional backups operations are performed on subclients, which have fixed content configured prior to performing the operation. On Demand backup allows you the flexibility of specifying content each time you perform a backup operation.

Use the following procedure to configure and perform an On-Demand backup for File and File Group subclients:

  1. Download the create_ondemand_FFGsub_template.xml file and save it on the computer from where the command will be executed.
  2. Execute the following command from the <Software_Installation_Directory>/Base folder after substituting the parameters values.

    qoperation execute –af create_ondemand_FFGsub_template.xml –clientName client1 –instanceName client1\instance1 –subclientName subclient1 -dataBackupStoragePolicy/storagePolicyName SPdata -logBackupStoragePolicy/storagePolicyName SPlog -sqlSubclientType FILE_FILEGROUP -onDemandSubClient true

    For information on the parameters used in this command, see Available Parameters for Subclient Configuration.

  3. Create a file and file group list file (as a text file) for the files you plan to backup in the following format:

    DatabaseName<\tab>FileGroupName<\tab>LogicalFileName

    You may specify any files belonging to the same database only. There must be one entry per line in the file.

  4. Download the backup_template.xml file and save it on the computer from where the command will be executed.
  5. Execute the following command from the <Software_Installation_Directory>/Base folder after substituting the parameters values.

    qoperation execute -af c:\backup_template.xml -clientName client1 -instanceName client1\instance1 -subclientName subclient1 –ondemandinputfile c:\FileList.txt –backupLevel FULL

Perform Command Line Operations from SQL Management Studio

Besides using the operating system's command line interface, you can also perform the command line operations from SQL Management Studio interface by integrating the commands within the SQL scripts. Registration of stored procedure and execution of commands should be done from SQL Server version 2008 or higher.

Use the following steps to run command line operations from SQL Management Studio:

  1. Execute the query to register the stored procedure to a specific SQL database (for example, msdb) in SQL Management Studio.

    In order to do this, make sure the trustworthy database property for the SQL database is set to ON.

  2. Log in to the CommServe using the SQL scripts. For example,

    You can login to the CommServe using an encrypted or open password.

    • For encrypted password, run a save as script operation from the CommCell Console and view the .bat file to retrieve the encrypted password.
    • Open password can be provided as shown below:

      @input = N'qlogin -u "admin" -clp "admin"',

  3. Execute the command line operation using SQL scripts.

    Examples:

    Creating an Instance

    DECLARE @return_value int,
    @response nvarchar(max)
    EXEC @return_value = [dbo].[cv_cmdshell]
    @input = N'qlist instance -c client1 -a Q_MSSQL',
    @response = @response OUTPUT
    IF RTRIM(@response) LIKE N'client1\instance1%'
    Print 'Instance already exists'
    ELSE
    Print 'Creating instance client1\instance1'
    EXEC @return_value = [dbo].[cv_cmdshell]
    @input = N'qoperation execute -af e:\MyFolder\createSQL_Template.xml -instanceName client1\instance1 -clientName client1',
    @response = @response OUTPUT

    where, e:\MyFolder is the location where the template XML is saved.

    Performing a Full Backup

    DECLARE @return_value int,
    @response nvarchar(max)
    EXEC @return_value = [dbo].[cv_cmdshell]
    @input = N'qoperation execute -af e:\MyFolder\full.xml -subClientName subclient1 -clientName client1 -instanceName client1\instance1 -ondemandinputfile C:\MyTest\myDBsContent.txt',
    @response = @response OUTPUT

    where, myDBsContent.txt is the content file that list the databases to be backed up and

    e:\MyFolder is the location where the template XML is saved.

    Performing a Restore

    DECLARE @return_value int,
    @response nvarchar(max) EXEC
    @return_value = [dbo].[cv_cmdshell]
    @input = N'qoperation execute -af e:\MyFolder\restore_template.xml -clientName client1 -instanceName client1\instance1 -toTimeValue 2011-11-28 15:40:00 -restoreSource DB1 -database DB1'
    @response = @response OUTPUT

    where

    e:\MyFolder is the location where the template XML is saved and the –toTimeValue is the backup finish date.

Unregistering the SQL Database

You can un-register the stored procedure from the SQL database by executing the below query:

Log Off from the CommServe

Once you have completed the command line operations, you can logout from the CommServe using the following command:

qlogout -cs commserve

For example, to log out from the CommServe 'Server1'.

qlogout -cs Server1