LiteSpeed for SQL Server 8.9 - Integration with TSM Guide

About Using IBM Spectrum Protect (TSM) with LiteSpeed

Overview of IBM Spectrum Protect (TSM) Integration

Configure LiteSpeed to work with IBM Spectrum Protect (TSM)

IBM Spectrum Protect (TSM) Requirements Configure LiteSpeed to Back Up Directly to IBM Spectrum Protect (TSM) Install and Configure the IBM Spectrum Protect (TSM) Backup-Archive Client Back Up and Restore to IBM Spectrum Protect (TSM) with LiteSpeed Troubleshoot Backing Up and Restoring to IBM Spectrum Protect (TSM)

IBM Spectrum Project (TSM) Examples Use IBM Spectrum Protect (TSM) Archive

Store a Backup as a IBM Spectrum Protect (TSM) Archive Restore an Archived Backup

IBM Spectrum Project (TSM) Examples

Following are several TSM examples. For more information about the LiteSpeed extended stored procedures and command-line utilities, please refer to the LiteSpeed User Guide.

Back up Database to TSM Using SQL

The following command backs up the database:

exec master.dbo.xp_backup_database
@database = 'pubs_50gb'
, @tsmclientnode = 'testsystemwas'
, @tsmclientownerpwd = 'password'
, @tsmobject = 'TWAS1_50GB\All_pubs50gb_Backups\pubs50gb_FirstBackup'
, @tsmconfigfile = 'C:\Program Files\Tivoli\tsm\api\SAMPSRC\dsm.opt'
, @init = 1
, @AdaptiveCompression = 'Speed'

In this example, LiteSpeed backs up the database pubs_50gb by:

Connecting to the tsm server as noted by the tsmserveraddress option specified in the dsm.opt file.
Using the client node name and client owner password to authenticate the user to the TSM server.
Storing the backup in the TSM name space designated by @tsmobject.
(If there is a TSM object with the same name) Versioning the backup using the @init = 1 option.
Using Adaptive Compression to optimize backup for speed.

Back Up to TSM with the Passwordaccess Generate Option Using SQL

If the Passwordaccess Generate option is specified in the dsm.opt file, then the node name and password are not required:

exec master.dbo.xp_backup_database
@database = 'Northwind'
, @tsmobject = 'fsMH\nw\Northwind_%z'
, @tsmconfigfile = 'C:\program files\Tivoli\TSM\baclient\dsm.opt'
, @compressionlevel = 1
, @tsmmanagementclass = N'SPS_MGTD'
, @init = 1

Use Command-Line to Back Up to TSM

Use the following command to back up the "Northwind" database:

sqllitespeed -Bdatabase -DNorthwind -i"fsMH\nw\testcmd" -c"10.0.1.200" -k"password" -j"c:\program files\Tivoli\TSM\baclient\dsm.opt" --AdaptiveCompression "Speed" -z"SPS_MGTD" S"MyServer\Instance" -T

This will back up the "Northwind" database with the following options:

To the unique location	fsMH\nw\testcmd Where: fsMH is TSM filespace nw is TSM high level testcmd is TSM low level (If backing up to a non-unique location, use -I parameter.)
From the client node	10.0.1.200 (As set up with the TSM server. In this example that node name is the IP address of the client.)
Connecting to the server instance	MyServer\Instance
Using a trusted connection	(Instead of passing the username -U and password -P.)
Connecting with the client password	"password"
Using the TSM option file	C:\Program Files\Tivoli\TSM\baclient\dsm.opt
Using the Adaptive Compression option	Optimize for speed
Using the specified management class	SPS_MGTD (If not specified, LiteSpeed uses the default management class.)

Use Command-Line to Back Up to TSM with the Passwordaccess Generate Option

If the Passwordaccess Generate option is specified in the dsm.opt file, you can use the following command:

sqllitespeed -Bdatabase -D"Northwind" -i"fsMH\nw\Northwind_%z" -j"C:\program files\Tivoli\TSM\baclient\dsm.opt" -C1 -I -S"MyServer\Instance" -T

Restore Database from TSM Using SQL

Exec master.dbo.xp_restore_database
@database = 'pubs4'
, @with = 'move ''pubs'' to ''C:\test\pubs4.mdf'''
, @with = 'move ''pubs_log'' to ''C:\test\pubs4_log.ldf'''
, @tsmclientnode = 'nodet1'
, @tsmobject = 'm1\pubs_mb\test1'
, @tsmclientownerpwd = 'password'
, @tsmconfigfile = 'C:\Program Files\Tivoli\tsm\api\SAMPSRC\dsm.opt'

Verify TSM Backup Using SQL

exec master.dbo.xp_restore_verifyonly
@tsmclientnode = 'nodet1'
, @tsmclientownerpwd = 'nodet1'
, @tsmobject = 'm1\pubs_mb\test1'
, @tsmconfigfile = 'C:\Program Files\Tivoli\tsm\api\SAMPSRC\dsmmt.opt'

Restore File List Only from TSM Using SQL

Exec master.dbo.xp_restore_filelistonly
@tsmclientnode = 'nodet1'
, @tsmclientownerpwd = 'password'
, @tsmobject = 'm1\pubs_mb\test1'
, @tsmconfigfile = 'C:\Program Files\Tivoli\tsm\api\SAMPSRC\dsmmt.opt'

Striped Backup To TSM Using SQL

exec master.dbo.xp_backup_database
@database = 'Northwind'
, @tsmobject = 'fsMH\nw\stripetest01'
, @tsmobject = 'fsMH\nw\stripetest02'
, @tsmobject = 'fsMH\nw\stripetest03'
, @tsmobject = 'fsMH\nw\stripetest04'
, @tsmclientnode = '10.0.1.200'
, @tsmclientownerpwd = 'password'
, @tsmconfigfile = 'C:\Program Files\Tivoli\TSM\baclient\dsm.opt'
, @init = 1

Striped Restore from TSM Using SQL

Verify Striped Backup

exec master.dbo.xp_restore_verifyonly
@tsmobject = 'fsMH\nw\stripetest01'
,@tsmobject = 'fsMH\nw\stripetest02'
,@tsmobject = 'fsMH\nw\stripetest03'
,@tsmobject = 'fsMH\nw\stripetest04'
,@tsmclientnode = '10.0.1.200'
,@tsmclientownerpwd = 'password'
,@tsmconfigfile = 'C:\Program Files\Tivoli\TSM\baclient\dsm.opt'

Restore Header Only

exec master.dbo.xp_restore_headeronly
@tsmobject = 'fsMH\nw\stripetest01'
,@tsmobject = 'fsMH\nw\stripetest02'
,@tsmobject = 'fsMH\nw\stripetest03'
,@tsmobject = 'fsMH\nw\stripetest04'
,@tsmclientnode = '10.0.1.200'
,@tsmclientownerpwd = 'password'
,@tsmconfigfile = 'C:\Program Files\Tivoli\TSM\baclient\dsm.opt'

Restore File List Only

exec master.dbo.xp_restore_filelistonly
@tsmobject = 'fsMH\nw\stripetest01'
,@tsmobject = 'fsMH\nw\stripetest02'
,@tsmobject = 'fsMH\nw\stripetest03'
,@tsmobject = 'fsMH\nw\stripetest04'
,@tsmclientnode = '10.0.1.200'
,@tsmclientownerpwd = 'password'
,@tsmconfigfile = 'C:\Program Files\Tivoli\TSM\baclient\dsm.opt'

Restore from Striped Backup

exec master.dbo.xp_restore_database
@database = 'Northwind'
,@tsmobject = 'fsMH\nw\stripetest01'
,@tsmobject = 'fsMH\nw\stripetest02'
,@tsmobject = 'fsMH\nw\stripetest03'
,@tsmobject = 'fsMH\nw\stripetest04'
,@tsmclientnode = '10.0.1.200'
,@tsmclientownerpwd = 'password'
,@tsmconfigfile = 'C:\Program Files\Tivoli\TSM\baclient\dsm.opt'

Striped Backup to TSM Using the Command Prompt

sqllitespeed -Bdatabase -DNorthwind -i"fsMH\nw\testcmd1" -i"fsMH\nw\testcmd2" -i"fsMH\nw\testcmd3" -c"10.0.1.200" -k"password" -j"c:\program files\Tivoli\TSM\baclient\dsm.opt" -I -S"MyServer\Instance" -T

Striped Restore from TSM Using the Command Prompt

sqllitespeed -RDatabase -j"c:\program files\Tivoli\TSM\baclient\dsm.opt" -i"fsMH\nw\testcmd1" --TSMPointInTime "2011-04-12 11:46:48" -i"fsMH\nw\testcmd3" --TSMPointInTime "2011-04-12 11:46:48" -i"fsMH\nw\testcmd2" --TSMPointInTime "2011-04-12 11:46:48" -N1 -DNorthwind c"10.0.1.200" -k"password"-S"MyServer\Instance" -T

View Filespaces Associated with a Client Node

exec master.dbo.xp_view_tsmfilespaces
@tsmclientnode = '10.0.1.200'
,@tsmclientownerpwd = 'password'
,@tsmconfigfile = 'C:\Program Files\Tivoli\TSM\baclient\dsm.opt'

View Contents of TSM Filespace

exec master.dbo.xp_view_tsmcontents
@tsmclientnode = '10.0.1.200'
,@tsmclientownerpwd = 'password'
,@tsmconfigfile = 'C:\Program Files\Tivoli\TSM\baclient\dsm.opt'
,@tsmfilespace = 'fsMH'

View Information on TSM Management Classes

List all the management classes:

exec master.dbo. xp_view_tsmmc
@tsmclientnode = '10.0.1.208'
,@tsmclientownerpwd = 'password'
,@tsmconfigfile = 'C:\Program Files\Tivoli\TSM\baclient\dsm.opt'

List information about a specific management class:

exec master.dbo. xp_view_tsmmc
@tsmmanagementclass = 'SPS_MGTD'
,@tsmclientnode = '10.0.1.208'
,@tsmclientownerpwd = 'password'
,@tsmconfigfile = 'C:\Program Files\Tivoli\TSM\baclient\dsm.opt'

Bind an Object to a New Management Class

exec master.dbo.xp_rebind_tsmmc
@tsmclientnode = '10.0.1.113'
,@tsmclientownerpwd = 'password'
,@TSMMANAGEMENTCLASS = 'STD_MGT_CLASS_B'
,@tsmobject = 'w2k10\HL1\LL2'
,@tsmconfigfile = 'c:\Program Files\Tivoli\TSM\api\dsm.opt'

Delete an Object in TSM Using the Command-Line

SLSMedia.exe -r tsm:file space\high level\low level --TSMConfigFile C:\ProgramFiles\Tivoli\tsm\api\dsm.opt

The above command deletes the TSM object.

usage: SLSMedia {operation} {options}
operation:
-?|--ShowHelp [<help topic>]
-d|--Download <source url> <target filename>
-r|--Remove <object>
-l|--List <object>
-b|--TSMBind <tsm_name>
-q|--TSMQuery <tsm_type>

NOTE: You can only delete an object in this manner if the TSM management policy set for the node allows it. Your TSM administrator will be able to advise you on your TSM policy settings.

Caution: Be careful when deleting objects. LiteSpeed places no restrictions on the objects to be deleted, which means it is possible to delete one object from a striped backup set, rendering the backup set useless.

Delete Old Backups

Delete full, differential and transaction log TSM backups created before 06/15/2012, using the PASSWORDAccess generate option to connect to the TSM Server:

exec master.dbo.xp_slsSmartCleanup
@database = N'test_tsm'
, @tsmconfigfile = N'C:\Program Files\Tivoli\TSM\baclient\dsm_gp.opt'
, @BackupExpiration = '2012-06-15'
, @LogExpiration = '2012-06-15'

For more information, see xp_slsSmartCleanup.

Delete transaction log TSM backups created more than 10 days ago, using the PASSWORDAccess generate option to connect to the TSM Server:

SLSSmartCleanup.exe --Database test_tsm --TSMConfigFile "C:\Program Files\Tivoli\TSM\baclient\dsm_gp.opt" --LogRetainDays 10 --WindowsAuth

For more information, see SmartCleanup Command-Line Arguments.

Convert a TSM Backup to a LiteSpeed Disk Backup

You can use the following command to convert a TSM backup to a LiteSpeed disk backup to be able to restore it on a machine that does not have TSM installed:

slsrecast.exe -j "c:\tsmconfig.opt" -E tsmbkp:fs\highlevel\lowlevel -F "c:\new.bkp"

For more information, see Recast LiteSpeed Backups.

Use IBM Spectrum Protect (TSM) Archive

You can select to store a backup to IBM Spectrum Project (TSM) as an archive or as the usual backup.

TSM archiving is the method of transferring files off the computer into long-term storage, and it differs from regular TSM backups. Archiving uses different arguments, and allows additional parameters for narrowing the list of items returned in a query.

Store a Backup as a IBM Spectrum Protect (TSM) Archive

You can run a TSM archive using xp_backup_database and xp_backup_log. For more information about the LiteSpeed extended stored procedures and command-line utilities, please refer to the LiteSpeed User Guide.

To store a backup as a TSM archive

The parameter @tsmarchive = 1 is required.

Example:

EXEC master.dbo.xp_backup_database
@database = 'pubs'
, @tsmclientnode = 'ClusterGroup'
, @tsmclientownerpwd = 'test1111'
, @tsmobject = 'SLS_Russel-Mar\pubs\(16)Thursday_14:14'
, @tsmconfigfile = 'C:\Program Files\Tivoli\tsm\baclient\dsm.opt'
, @tsmarchive = 1
, @init = 1

Restore an Archived Backup

You can restore a TSM archive backup using xp_restore_database and xp_restore_log. Using xp_restore_verifyonly you can see if the backup set is complete and valid.

For more information about the LiteSpeed extended stored procedures and command-line utilities, please refer to the LiteSpeed User Guide.

To restore an archived backup

Run the xp_view_tsmcontents (or the GUI must run it for the TSM Object Selection dialog just as it currently does for backups).
From the list, select the desired archive to restore. That selected archive has a tsmpointintime column in the list as seen in xp_view_tsmcontents. That tsmpointintime must be passed in to the restore in the @tsmpointintime parameter. This is the same parameter that backups can currently pass in for selection of an old retained backup to restore.

To restore an archive both the @tsmarchive = 1 and the @tsmpointintime parameters are required. If the @tsmarchive is passed in but the @tsmpointintime is not passed in, an error message is thrown stating that the @tsmpointintime is needed and where to get it (the xp_view_tsmcontents).

The @tsmarchive = 1 must have a supporting check box/radio button in the EC restore wizard. The @tsmpointintime however needs no GUI real estate change since it should work the same way it currently does for backups. Once the user selects an archive to restore the code should pull the tsmpointintime from the xml for that selected archive.