SQL Server backup and restore with IBM Tivoli Storage Manager (TDP)
Configure the TSM OS and Data Protection for SQL Server agents, schedule backups and restore a database to another server.
This article covers backing up SQL Server with IBM Tivoli Storage Manager (TSM, now Spectrum Protect) and its Data Protection for SQL Server module (TDP), then restoring a database, including to another server. Server, node and database names are generic examples.
Principle
- The OS agent (backup-archive client) backs up the file system.
- The TDP SQL module (
tdpsqlc) backs up the databases through SQL Server. It registers as a second TSM node, suffixed_sql. .mdfand.ldffiles are excluded from the OS backup: TDP covers them.- Always run the OS backup before the TDP backup.
Ports to open
| TCP port | Purpose |
|---|---|
| 1500 | client to TSM server connection |
| 1501 | TSM server to client connection |
| 1905 / 1906 | database agent (direction depends on how the scheduler is set up) |
Prepare the TSM server
Register two nodes: one for the OS, one for the database (replace the initial password with your own).
tsm> reg node sqlserver01 <PASSWORD> dom=dom_sql archdel=no backdel=no clopt=opt_win_lan user=none
tsm> reg node sqlserver01_sql <PASSWORD> dom=dom_sql archdel=no backdel=no clopt=opt_win_lan user=none
Associate the schedules with the OS node. The full SQL backup schedule is deliberately associated with the OS node, not the _sql node, because it runs a script through the OS client scheduler:
tsm> def assoc dom1 INC_1900 sqlserver01
tsm> def assoc dom1 SQL_2000_FULL sqlserver01
Configure the OS agent
Install the client with a custom installation, then create dsm.opt in the baclient folder of the TSM installation directory:
Commmethod tcpip
tcpport 1500
TCPSERVERADDRESS tsm.example.com
NODENAME sqlserver01
schedmod prompted
passwordaccess generate
schedlogname dsmsched.log
errorlogname dsmerror.log
SCHEDLOGRETENTION 30 D
ERRORLOGRETENTION 30 D
QUIET
domain all-local
exclude *:\...\*.mdf
exclude *:\...\*.ldf
Check that TCPSERVERADDRESS resolves. An alternative to domain all-local is DOMAIN C: SYSTEMSTATE together with the database file exclusions.
Install the scheduler service from an elevated console in baclient:
dsmcutil.exe install /name:"TSM_Scheduler" /node:sqlserver01 /password:<PASSWORD> /startnow:yes /autostart:yes
Test with dsmc.exe q opt, which should print the client version and options.
Configure the database agent
In the TDPSql folder, dsm.opt:
NODename sqlserver01_sql
CLUSTERnode NO
PASSWORDAccess Generate
COMMMethod TCPip
TCPServeraddress tsm.example.com
TCPPort 1500
INCLUDE "\...\meta\...\*"
INCLUDE "\...\data\...\*"
EXCLUDE "\...\master\...\log*"
EXCLUDE "\...\msdb\...\log*"
And tdpsql.cfg:
SQLSERVer sqlserver01
FROMSQLserver sqlserver01
SQLAUTHentication INTegrated
MOUNTWaitfordata Yes
BACKUPMethod LEGACY
DIFFESTimate 20
BUFFers 3
BUFFERSIze 1024
STRIPes 1
SQLBUFFers 0
SQLBUFFERSIze 1024
LOGPrune 60
LANGuage ENU
Create a C:\TSM_Scripts folder with two scripts.
sql_full_bkp.bat:
@ECHO OFF
set sql_dir=C:\Progra~1\Tivoli\TSM\TDPSql
set sql_logfile=%sql_dir%\sqlscript.log
C:
cd %sql_dir%
%sql_dir%\tdpsqlc backup * full /tsmoptfile=%sql_dir%\dsm.opt /logfile=%sql_dir%\sqlfull.log >> %sql_logfile%
sql_log_bkp.bat:
@ECHO OFF
set sql_dir=C:\Progra~1\Tivoli\TSM\TDPSql
set sql_logfile=%sql_dir%\sqlscript.log
C:
cd %sql_dir%
%sql_dir%\tdpsqlc backup * log /tsmoptfile=%sql_dir%\dsm.opt /logfile=%sql_dir%\sqllog.log >> %sql_logfile%
Any change to dsm.opt (or its equivalent) requires restarting the TSM_Scheduler service.
Then open the "SQL Client" GUI as administrator, using the dedicated backup account, and check the SQL connection by browsing the databases. Under Utilities > Show TSM Server information, enter the _sql node password to store it.
Start and monitor a backup from the TSM server
tsm> def clientaction sqlserver01 action=i
tsm> def clientaction sqlserver01 action=c object='"C:\TSM_Scripts\sql_full_bkp.bat"'
tsm> q event * @10*
tsm> q sess
The first command starts an incremental system backup, the second the full SQL backup, q event lists events and q sess lists running sessions.
Check that a backup succeeded
tsm> q event * SQL_2200_FULL begind=-2 endd=today begint=21:00 endt=now node=sqlserver01* f=d
The status must be Completed with result 0. Confirm in the TDP log (Total SQL backups attempted and Total SQL backups completed).
Restore a database to another server
First check that the destination SQL Server version is compatible: this is a common cause of failure. If TSM is not installed on the destination, install the OS client and TDP and register the node before restoring.
GUI
- Open an elevated console in the
TDPSqlfolder (for a SQL cluster, the.optfiles sit on a cluster disk in aTSMfolder, named liketdp_<host>_sql.opt). - Copy the
.optfile totdp_restore.optand setNODenameto the source node (sqlserver01_sql). The.optfile points to the source server; the.cfgfile points to the destination and must not be changed. - Run:
tdpsql /tsmoptfile=l:\tsm\tdp_restore.opt /configfile=l:\tsm\sqlserver02_tdpsql.cfg
- In the restore tab, the server shown is the source. Tick the required options, including Replace.
- Select the version, right-click Restore into to name the database, then Relocate to set data and log file paths, then Recover.
Command line
Passwords must be handled explicitly (/tsmpassword); the command reports what to fix if needed. First inspect the backup:
tdpsqlc query TSM AppDb FULL /fileinfo /fromsqlserver=sqlserver01 /tsmnode=sqlserver01_sql /tsmpassword=<PASSWORD> /mountwait=yes
Then restore, relocating file by file:
tdpsqlc restore AppDb FULL /relocate=AppDb,AppDb_log /to=e:\MSSQL\DATA\AppDb.mdf,e:\MSSQL\LOG\AppDb_log.ldf /fromsqlserver=sqlserver01 /sqlserver=sqlserver02 /tsmnode=sqlserver01_sql /tsmpassword=<PASSWORD> /mountwait=yes
or to a directory:
tdpsqlc restore AppDb FULL /RELOCATEDIR=e:\MSSQL\DATA\ /fromsqlserver=sqlserver01 /sqlserver=sqlserver02 /tsmnode=sqlserver01_sql /tsmpassword=<PASSWORD> /mountwait=yes
A successful restore ends with Restore of AppDb completed successfully. and the summary Total database backups restored: 1.
Notes
- The original note also covers storage pool creation, Exchange and Linux backups, server maintenance and tape troubleshooting; those topics are not included here.
- Values (nodes, passwords, addresses, database names) are generic examples;
<PASSWORD>stands for a password of your choice. - The source documentation targets TSM 5.5 and 6.2 era products: check options against the IBM documentation for your version.