The term automation, as it applies to Data Protection for Microsoft SQL, means that you can run commands from the command line, create scripts, schedule tasks, and use the graphical user interface to start tasks based on scripts and schedules that you create.
The software supports running tasks from both the command-line interface or Microsoft Windows PowerShell command prompt (Version 3.0 and later). You can also use the Automate tab in the Management Console.
Automating tasks
You can use the Automate view to work with commands. You can save a command and run the command at a scheduled time.
About this task
You can use the Automate view to create, save, store, and schedule commands.
Open the Automate view by selecting a workload that you want to work with and clicking Automate. An integrated command line is available in the task window.
You can use the interface to enter PowerShell cmdlets or command-line interface commands. The output is displayed in the main window.
Procedure
1. Change PowerShell to Command Line.
2. Type a command in the details pane and click the Execute icon to run the command. You can also run a saved task by clicking the Open icon, selecting the command file, and clicking the Execute icon.
The commands can be entered with or without specifying tdpsqlc. For
example, for each selected workload instance, you can enter a single command or multiple commands, such as:
q tsm q sql
3. Click the Save icon and follow the prompts to save a command for future use.
4. To schedule a command, click the Schedule this command icon to open the scheduling wizard. Follow the prompts in the wizard to create a schedule for the command.
5. The output of the command is displayed in the results pane. The output can be saved or sent to an email address.
What to do next
You can automate commands from the Protect, Recover, Schedule, and Task List views in the Management Console:
1. Start the Management Console and select the SQL Server instance in the tree view.
2. Click the tab for the task you want to do (Protect or Recover).
3. Automate the command by using one of the following methods:
Result pane
Select the item for your task in the result pane, and select Run Scheduledin the toolbar menu. Click the appropriate task in the Actionpane. When the schedule wizard starts, enter the information for each prompt to create a scheduled task.
Task List pane
When a task is submitted, it displays in the task list pane. Select the appropriate task, then click Schedule command script in the task list toolbar. When the schedule wizard starts, enter the information for each prompt to create a scheduled task.
You can also right-click a task in the Task List pane and click Copy.
Then, click the Automate tab and paste the command in the field.
Scheduling
The guidelines assist when planning scheduled operations.
Refer to the following guidelines when defining a Tivoli Storage Manager schedule:
v If you want to use the Tivoli Storage Manager server scheduling mode, you must ensure that the Data Protection for SQL Server option file has the
tcpclientaddressand tcpclientport options specified. If you want to run more than one scheduler service, use the same tcpclientaddress. However, in
addition to different node names, use different values for tcpclientport. An example of running more than one scheduler service is when you are scheduling Data Protection for SQL Server and the Windows backup client.
Server-prompted scheduling is supported only when TCP/IP communication is being used. By default, Data Protection for SQL Server uses the client polling schedule mode.
v If any changes that affect the scheduler are made to the Data Protection for SQL Server options file, the scheduler must be restarted in order to pick up the changes. An example of this is the Tivoli Storage Manager server address, the schedule mode, or the client TCP address or port. This can be done by issuing the following commands:
net stop "Data Protection for SQL Scheduler"
net start "Data Protection for SQL Scheduler"
If you are running the scheduler service in a cluster environment, use the Cluster Administrator to stop and restart your scheduler service. Do not use the net stop and net start commands.
v The default Tivoli Storage Manager scheduler log file (dsmsched.log) contains status information for the Tivoli Storage Manager scheduler. In this example, the file is located in this path:
d:\Program Files\Tivoli\TSM\TDPSql\dsmsched.log
You can override this file name by specifying the schedlogname option in the Data Protection for SQL Server options file.
v Data Protection for SQL Server creates its own log file with statistics about the backed up database objects when the /logfile parameter is specified during the tdpsqlccommand. In the sample file (sqlfull.smp), the log file is sqlsch.log.
This file is different from the Tivoli Storage Manager scheduler log file and must also be different from the file to which the tdpsqlc command output is
redirected. In the previous example, this file is sqlfull.log.
Output from scheduled commands are sent to the scheduler log file
(dsmsched.log). After scheduled work is performed, check the log to ensure the work completed successfully.
When a scheduled command is processed, the scheduler log might contain the following entry:
Scheduled event eventname completed successfully
This is merely an indication that Tivoli Storage Manager successfully issued the scheduled command associated with the eventname. No attempt is made to determine the success or failure of the command. You should assess the success or failure of the command by evaluating the return code from the scheduled command in the scheduler log. The scheduler log entry for the command's return code is prefaced with the following text:
Finished command. Return code is:
If any scheduled backups fail, the scheduler script exits with the same error code as the failed backup command. A non-zero error code means that backup failed.
The results from the very last command in the script are returned to the scheduled event.
v The preferred method of password management for scheduler operations is to specify passwordaccess generate in the dsm.opt file. If passwordaccess generate is not specified in the dsm.opt file, then the Tivoli Storage Manager password must be specified on the tdpsqlc command. To specify the password, use the /tsmpasswordparameter in the command file being run by the scheduler
(sqlfull.cmd). You can also specify the password on the Data Protection for SQL Server command line. For example:
tdpsqlc query tsm /tsmnode=mynode /tsmpassword=newpassword
v All log files and redirected output files must have unique names for both the Data Protection for SQL Server command and the scheduler service that is starting the backup. When you do not use unique names, the schedule fails with a return code of RC=1.
Windows PowerShell and Data Protection for SQL Server
Data Protection for SQL Server includes a set of Windows PowerShell cmdlets to help manage Data Protection for SQL Server components in your environment.
Because cmdlets can be chained together to form commands and because there is a large body of existing cmdlets from other vendors the Data Protection for SQL Server cmdlets help support a seamless management environment. Remote management and automation capabilities are greatly improved when using the Data Protection for SQL Server cmdlets.
Getting started
The cmdlets can be used in supported Windows environments.
About this task
Before you use the cmdlets provided with IBM Tivoli Storage Manager for Databases: Data Protection for SQL Server, complete the following steps:
Procedure
1. Log on to the system as an administrator.
2. From a Windows PowerShell command prompt, enter the following command:
set-executionpolicy remotesigned
3. Import the Windows PowerShell modules from the TDPSql folder:
v FmModuleSQL.dll v FmModuleMMC.dll
To import modules, with the administrator credentials, from a Windows PowerShell command prompt, complete the following steps:
a. Navigate to the TDPSql folder.
b. Enter the following commands:
import-module .\FmModuleSQL.dll import-module .\FmModuleMMC.dll
c. (Optional) To use the cmdlets in these modules any time you start Windows PowerShell, add the following lines to your profile:
$path = (get-itemproperty -path "HKLM:\SOFTWARE\IBM\TDPSql\
currentversion\mmc" -ea SilentlyContinue).path if ($null -ne $path)
{
dir "$path\fmmodule*.dll" | select -expand fullname | import-module -force -Global
}
What to do next
For information about creating, running, monitoring, and troubleshooting scripts with cmdlets, see Windows PowerShell 3.0 documentation. Information about Windows PowerShell cmdlets consistent naming patterns, parameters, arguments, and syntax is also provided in the Windows PowerShell documentation. The following web site is a starting point for this type of documentation:
http://technet.microsoft.com/en-us/library/hh857337.aspx.
Cmdlets for protecting Microsoft SQL Server data
The following table identifies the cmdlets that are available for use when protecting Microsoft SQL Server data.
Table 10. Cmdlets to protect Microsoft SQL Server data. The following table identifies the cmdlets that you can use to protect Microsoft SQL Server data.
Cmdlet name
Related command-line interface
command Short description
Add-DpSqlPolicy tdpsqlc create policy Create a new policy for Microsoft SQL Server data.
Backup-DpSqlComponent tdpsqlc backup Backup SQL components.
Copy-DpSqlPolicy tdpsqlc copy policy Copy an existing policy to a new policy.
Dismount-DpSqlBackup tdpsqlc unmount backup Dismount a backup.
Get-DpSqlBackup tdpsqlc query tsm * Query the backups that are stored on the server.
Get-DpSqlComponent tdpsqlc query sql * Query the databases that are available on the SQL server.
Get-DpSqlConfig tdpsqlc query tdp Display configuration information.
Get-DpSqlConnection tdpsqlc query tsm Displays the Tivoli Storage Manager API and server information.
Get-DpSqlFileGroups not applicable Displays all file and group information about specified SQL Server databases.
Table 10. Cmdlets to protect Microsoft SQL Server data (continued). The following table identifies the cmdlets that you can use to protect Microsoft SQL Server data.
Cmdlet name
Related command-line interface
command Short description
Get-DpSqlInformation tdpsqlc query sql Display specified SQL Server information.
Get-DpSqlManagedCapacity tdpsqlc query managedcapacity Assist with storage planning by determining the amount of managed capacity that is in use.
Get-DpSqlPolicy tdpsqlc query policy Query policy.
Mount-DpSqlBackup tdpsqlc mount backup Mounts a backup that provides access to the files that are contained by the backup.
Remove-DpSqlBackup tdpsqlc delete backup and tdpsqlc inactivate
Use to delete a VSS backup of a SQL Server database, or inactivate one or more active legacy backup objects on the Tivoli Storage Manager server.
Remove-DpSqlPolicy tdpsqlc delete policy Deletes a local policy.
Reset-DpSqlTsmPassword tdpsqlc changetsmpassword Changes the Tivoli Storage Manager password used by Data Protection for SQL Server.
Restore-DpSqlBackup tdpsqlc restore Restore backups of Microsoft SQL Server data.
Set-DpSqlConfig tdpsqlc set paramname Set the Data Protection for SQL Server configuration parameters in the configuration file.
Set-DpSqlPolicy tdpsqlc update policy Changes an existing policy.
To view the details about a specific cmdlet, run the Get-Help cmdlet with the cmdlet name. For example:
Get-Help Get-DpSqlBackup
To continue the example, to see examples for the cmdlet, enter:
get-help Get-DpSqlBackup -examples For more information, enter:
get-help Get-DpSqlBackup -detailed For technical information, enter:
get-help Get-DpSqlBackup -full
To go to the information center, enter:
get-help Get-DpSqlBackup -online
For information about a specific parameter, enter:
help Get-DpSqlBackup -Parameter backupdestination
To display the help in a separate window, include the -showwindow parameter with the help command.
Cmdlets for the Management Console
The following list identifies the cmdlets that are available for use when interacting with the Management Console.
v Clear-FcmMmcManagedCapacityHistory v Clear-FcmMmcScheduledActivityHistory v Disable-FcmMmcSchedule
v Enable-FcmMmcSchedule v Get-FcmMmcActivity
v Get-FcmMmcComputerInformation v Get-FcmMmcManagedCapacityHistory v Get-FcmMmcReport
v Get-FcmMmcSchedule
v Get-FcmMmcScheduledActivity v New-FcmMmcSchedule
v Remove-FcmMmcSchedule v Set-FcmMmcSchedule v Start-FcmMmcSchedule
To view the details about a specific cmdlet, run the Get-Help cmdlet with the cmdlet name. For example:
Get-Help New-FcmMmcSchedule
To continue the example, to see examples for the cmdlet, enter:
get-help New-FcmMmcSchedule -examples For more information, enter:
get-help New-FcmMmcSchedule -detailed For technical information, enter:
get-help New-FcmMmcSchedule -full To go to the information center, enter:
get-help New-FcmMmcSchedule -online
For information about a specific parameter, enter:
help New-FcmMmcSchedule -Parameter backupdestination
To display the help in a separate window, include the -showwindow parameter with the help command.