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_CLOSEoption 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 *
query tdpshows the TDP for SQL configuration.query tsm * types /alllists the backups available on the TSM server.query sqllists the databases of the SQL Server./fileinfoshows the files contained in the backup.
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
- Server, instance, database, path and account names in this article are generic examples.
- Never leave
/SQLPasswordor/TSMPasswordin clear text in a script or shell history; prefer integrated authentication (/SQLAUTH=INT) or a protected options file. - Reference documentation: IBM Spectrum Protect for Databases: Data Protection for SQL Server.