Configuring SQL Server Agent Using the Command Line Interface

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

    If you want to use a local account on the destination client for impersonation, then you must use the instanceProperties/mssqlInstance/overrideHigherLevelSettings/userAccount/username element and set it to the local account.

    Example

    qoperation execute -af ModifySQL_Instance_Template.xml -appName 'SQL Server' -clientName example_client -instanceName Instance001 -instanceProperties/mssqlInstance/overrideHigherLevelSettings/userAccount/username exampleuser

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 -content/excludePath *pattern* -fsExcludeFilterOperationType ADD

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.

  • USE_STORAGE_POLICY_SETTINGS, use the software compression options defined on the storage policy.

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

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.

subclientRecoveryType

Option to back up specific databases by adding the databases to a user-defined subclient. This option must be used in conjunction with disableAutoDiscovery set to FALSE when creating a new subclient. Valid values are:

  • FULL, to back up the databases where the recovery model is set to Full.

  • SIMPLE, to back up the databases where the recovery model is set to Simple.

  • SYSTEM, to back up the SQL Server system databases, such as master, msdb, and model.

disableAutoDiscovery

Option to enable or disable subclient-type auto discovery.

Valid values are True/False.

useBlockLevelBackupWithOptimizedRecovery

Option to enable or disable BlockLevelBackup with Optimized Recovery.

Valid values are True/False.

content/excludePath

Type the database naming pattern based on which you want to filter the databases.

fsExcludeFilterOperationType

The filter operation that you want to perform. Valid values are:

ADD - To add a new filter

DELETE - To remove the filter

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

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

Adding filters to exclude databases

To add a database naming pattern for filtering databases, use the following command:

qoperation execute -af modify_subclient_content.xml -appName 'SQL Server' -clientName clientname -instanceName instancename -subclientName subclientname -content/excludePath *pattern* -fsExcludeFilterOperationType ADD

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

    Note

    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

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. DECLARE @i_FullDLLPath NVARCHAR(MAX)SET @i_FullDLLPath = 'C:\ContentStore\Base\dbclr.dll'
    --INPUT REQUERED. Enter dbclr.ll PATH. eg: <software_install_path>\Base\dbclr.dll
    DECLARE @dbName nvarchar(255)SET @dbName ='msdb'
    --INPUT REQUERED. Give DATABASAE name where this procedure is needed.
    DECLARE @o_retVal INTEGER;
    DECLARE @sqlQuery NVARCHAR(MAX)
    SET @sqlQuery = 'use ' + @dbName + CHAR(10) SET @sqlQuery = @sqlQuery + 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[cv_cmdshell]'') AND type IN (N''P'', N''PC'')) ' + CHAR(10)SET @sqlQuery = @sqlQuery + ' DROP PROCEDURE [dbo].[cv_cmdshell]' + CHAR(10)EXECUTE sp_executesql @sqlQuery
    SET @o_retVal = @@ERROR
    IF @o_retVal <> 0 GOTO CX_EXIT
    SET @sqlQuery = 'use ' + @dbName + CHAR(10)SET @sqlQuery = @sqlQuery + 'IF NOT EXISTS (SELECT name FROM sys.assemblies asms WHERE asms.name = N''dbCLR'') ' + CHAR(10)SET @sqlQuery = @sqlQuery + 'CREATE ASSEMBLY dbCLR FROM N''' + @i_FullDLLPath + ''' WITH PERMISSION_SET = UNSAFE ' + CHAR(10)EXECUTE sp_executesql @sqlQuery
    SET @o_retVal = @@ERROR
    IF @o_retVal <> 0 GOTO CX_EXIT
    SET @sqlQuery = 'CREATE PROCEDURE [dbo].[cv_cmdshell] (@input NVARCHAR(MAX), @response NVARCHAR(MAX) OUTPUT) ' + CHAR(10)
    SET @sqlQuery = @sqlQuery + 'WITH EXECUTE AS CALLER ' + CHAR(10)
    SET @sqlQuery = @sqlQuery + 'AS ' + CHAR(10)
    SET @sqlQuery = @sqlQuery + 'EXTERNAL NAME [dbCLR].[StoredProcedures].[cv_cmdshell] ' + CHAR(10)set @sqlQuery = 'use ' + @dbName+ CHAR(10) + 'EXECUTE ('' '+ @sqlQuery +' '')'
    EXECUTE sp_executesql @sqlQuery
    SET @o_retVal = @@ERROR
    IF @o_retVal <> 0 GOTO CX_EXIT
    CX_EXIT:
    SELECT @o_retVal

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

    USE [msdb]
    GO
    DECLARE @return_value int,@response nvarchar(max)
    EXEC @return_value = [dbo].[cv_cmdshell]@input = N'qlogin -u "commandline" -ps "3d4b14baf641d429e2b3782c1ed5d0a64" -cs "commserve.company.com"',@response = @response OUTPUT
    SELECT 'Return Value' = @return_value
    SELECT 'Response' = @response
    If @return_value =0
    Print 'Login was successful for user commandline'
    GO

    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"',
  4. 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:

DECLARE @dbName nvarchar(255)SET @dbName ='msdb'
--INPUT REQUERED. Give DATABASAE name where this procedure is needed.
DECLARE @dropAssembly int = 1 -- 1 = true , 0 = false
DECLARE @o_retVal INTEGER;
DECLARE @sqlQuery NVARCHAR(MAX)
SET @sqlQuery = 'use ' + @dbName + CHAR(10)
SET @sqlQuery = @sqlQuery + 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[cv_cmdshell]'') AND type IN (N''P'', N''PC'')) ' + CHAR(10)
SET @sqlQuery = @sqlQuery + ' DROP PROCEDURE [dbo].[cv_cmdshell]' + CHAR(10)
EXECUTE sp_executesql @sqlQuery
IF @dropAssembly = 1
BEGIN set @sqlQuery = 'use ' + @dbName + CHAR(10)
SET @sqlQuery = @sqlQuery + 'IF EXISTS (SELECT name FROM sys.assemblies asms WHERE asms.name = N''dbCLR'') ' + CHAR(10)
SET @sqlQuery = @sqlQuery + ' DROP ASSEMBLY [dbCLR] ' + CHAR(10)
EXECUTE sp_executesql @sqlQuery
END

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

Loading...