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
-
Download the CreateSQL_Instance_Template.xml file and save it on the computer from where the command will be executed.
-
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
-
Download the ModifySQL_Instance_Template.xml file and save it on the computer from where the command will be executed.
-
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
-
Download the GetSQL_Instance_Properties_Template.xml file and save it on the computer from where the command will be executed.
-
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
-
Download the DeleteSQL_Instance_Template.xml file and save it on the computer from where the command will be executed.
-
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:
|
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
-
Download the create_subclient_template.xml file and save it on the computer from where the command will be executed.
-
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
-
Download the modify_subclient_template.xml file and save it on the computer from where the command will be executed.
-
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
-
Download the get_subclient_properties_template.xml file and save it on the computer from where the command will be executed.
-
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
-
Download the delete_subclient_template.xml file and save it on the computer from where the command will be executed.
-
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:
|
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:
|
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:
|
backupRules |
Option to set Backup conversion rules. Valid values are:
|
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:
|
sqlSubclientType |
Type of SQL subclient. Valid values are:
|
contentOperationType |
Modification type. Valid values are
|
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:
|
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 |
backupStagingDir |
Option to specify a backup staging directory to back up the SQL Analysis Services databases. |
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:
-
Download the create_ondemand_DBsub_template.xml file and save it on the computer from where the command will be executed.
-
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.
-
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.
-
Download the backup_template.xml file and save it on the computer from where the command will be executed.
-
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:
-
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.
-
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 -
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'
GOYou 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"',
-
-
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