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>

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

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>

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

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 the "Subclient Configuration" section on this page.

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