LLI Wiki — My Digital Knowledge Base

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

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

  1. Open an elevated console in the TDPSql folder (for a SQL cluster, the .opt files sit on a cluster disk in a TSM folder, named like tdp_<host>_sql.opt).
  2. Copy the .opt file to tdp_restore.opt and set NODename to the source node (sqlserver01_sql). The .opt file points to the source server; the .cfg file points to the destination and must not be changed.
  3. Run:
tdpsql /tsmoptfile=l:\tsm\tdp_restore.opt /configfile=l:\tsm\sqlserver02_tdpsql.cfg
  1. In the restore tab, the server shown is the source. Tick the required options, including Replace.
  2. 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

Mastodon LLIMastodon SocialFosstodon