LLI Wiki — My Digital Knowledge Base

SQL Server backup and restore with IBM TSM (tdpsqlc)

IBM Tivoli Storage Manager tdpsqlc commands to back up, query and restore SQL Server databases.

IBM Tivoli Storage Manager (TSM) for Databases, Data Protection for SQL Server (TDP for SQL), provides the tdpsqlc command to back up and restore SQL Server databases from the command line. This article collects the common commands.

Warning: a database with the AUTO_CLOSE option enabled can cause errors during backups. Disable it on the databases you back up.

Prerequisites

Move to the default installation folder:

cd /d "C:\Program Files\Tivoli\TSM\TDPSql"

Connection settings are passed either through a TSM options file (/TSMOPTFile) and a TDP configuration file (/CONFIGFILE), or directly on the command line. Never write passwords in clear text in scripts: <PASSWORD> below is a placeholder.

Backup

tdpsqlc backup * full
tdpsqlc backup * log
tdpsqlc backup AppDb full
tdpsqlc backup AppDb log
tdpsqlc backup AppDb differential

* means all databases. To exclude a database (for example tempdb):

tdpsqlc.exe backup * full /sqlserver=sqlserver01 /SQLAUTH=INT /TSMOPTFile=C:\tsm\tdp_sqlserver01.opt /EXCLUDEDB=tempdb

Named instance, specific port, authentication

tdpsqlc.exe backup * full /sqlserver=sqlserver01\INST1,14330 /SQLAUTH=INT /tsmoptfile=C:\tsm\tdp_inst1.opt
tdpsqlc.exe backup * log  /sqlserver=sqlserver01\INST1,14330 /SQLAUTH=INT /tsmoptfile=C:\tsm\tdp_inst1.opt
Option Purpose
/sqlserver= SQL Server or instance (server\instance[,port])
/SQLAUTH=INT Windows integrated authentication
/SQLAUTH=SQL SQL authentication (with /SQLUSer= and /SQLPassword=)
/tsmoptfile= TSM client options file
/CONFIGFILE= TDP configuration file
/EXCLUDEDB= Database(s) to exclude from a * backup

From SQL Server with xp_cmdshell

EXEC xp_cmdshell '"C:\Program Files\Tivoli\TSM\TDPSql\tdpsqlc.exe" backup * full /sqlserver=sqlserver01 /SQLAUTH=INT /tsmoptfile=C:\tsm\tdp_sqlserver01.opt /EXCLUDEDB=tempdb';

With SQL authentication:

tdpsqlc.exe backup * full /sqlserver=sqlserver01 /SQLAUTH=SQL /SQLUSer=app_user /SQLPassword=<PASSWORD> /tsmoptfile=C:\tsm\tdp_sqlserver01.opt /EXCLUDEDB=tempdb

Querying

tdpsqlc query tdp
tdpsqlc query tsm * types /all
tdpsqlc.exe query tsm AppDb /compat
tdpsqlc.exe query tsm AppDb /all
tdpsqlc.exe query tsm AppDb full /fileinfo /all
tdpsqlc.exe query sql *

To list the backups of another server (needed before a cross-server restore):

tdpsqlc.exe query tsm * types /all /fromsqlserver=sqlserver01 /tsmoptfile=C:\tsm\tdp_sqlserver01.opt

In a cluster, add /CONFIGFILE and /TSMOPTFile pointing to the files specific to the node or instance.

Restore

Restore a database on the same server:

tdpsqlc.exe restore AppDb full /CONFIGFILE=C:\tsm\tdpsql_inst1.cfg /TSMOPTFile=C:\tsm\tdp_inst1.opt

Restore a backup taken on another server (/fromsqlserver) to a target server (/SQLSERVer), with a log file:

tdpsqlc.exe restore AppDb full /fromsqlserver=sqlserver01 /tsmoptfile=C:\tsm\tdp_restore.opt /SQLSERVer=sqlserver02 /logfile=C:\tsm\restore.log

Move the files to other paths with /relocate (logical names) and /to (physical paths):

tdpsqlc.exe restore AppDb full /fromsqlserver=sqlserver01 /tsmoptfile=C:\tsm\tdp_restore.opt /SQLSERVer=sqlserver02 /relocate=AppDb,AppDb_log /to=D:\Data\AppDb.mdf,E:\Log\AppDb_log.ldf

To overwrite an existing database, add /REPlace. If the storage media needs mounting, /MOUNTWait=Yes makes the command wait.

Sample restore output

Starting Sql database restore...
Connecting to TSM Server as node 'NODE_SQLINST1'...
Querying Tivoli Storage Manager server for a list of database backups, please wait...

Beginning full restore of backup object AppDb, 1 of 1,
  to database AppDb
Restore of AppDb completed successfully.

Total database backups inspected:               1
Total database backups requested for restore:   1
Total database backups restored:                1
Total database skipped:                         0

Graphical interface

Running tdpsql (without the c) with the configuration files opens the graphical restore interface:

tdpsql /tsmoptfile=C:\tsm\tdp_restore.opt /CONFIGFILE=C:\tsm\tdpsql.cfg

Notes

Mastodon LLIMastodon SocialFosstodon