wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== config example ======

**IMPORTANT:** MSSQL database name are case sensitive !

Option file for baclient: dsm.opt
<code>
NODENAME         MSSQLSRV01
TCPSERVERADDRESS 172.10.10.10
MANAGEDSERVICES WEBCLIENT SCHEDULE
TCPCLIENTPORT 1501
SESSIONINITIATION SERVERONLY
SCHEDMODE PROMPTED
DATEFORMAT 4
PASSWORDACCESS GENERATE
TCPPort  1500
HTTPport  1581
ERRORLOGNAME "c:\program files\tivoli\tsm\baclient\dsmerror.log"
SCHEDLOGRETENTION 14 D
SCHEDLOGNAME "c:\program files\tivoli\tsm\baclient\dsmsched.log"
ERRORLOGRETENTION 14 D

DOMAIN "\\mssqlsrv01\c$"
</code>

Option file for TDPsql: dsm.opt
<code>
NODename MSSQLSRV01_SQL
PASSWORDAccess generate
TCPServeraddress 172.10.10.10
TCPPort 1500
HTTPport 1582
TCPClientPort 1502
SCHEDMODE PROMPTED
ERRORLOGNAME "c:\program files\tivoli\tsm\TDPSql\dsmerror.log"
SCHEDLOGRETENTION 14 D
SCHEDLOGNAME "c:\program files\tivoli\tsm\TDPSql\dsmsched.log"
ERRORLOGRETENTION 14 D
BUFFers     4
SQLBUFFers  4
STRIPes     4
</code>

Config file for TDPsql: tdpsql.cfg
<code>
LOCALDSMAgentnode MSSQLSRV01
BACKUPMETHod vss
LASTPRUNEDate     01/20/2017 14:45:45
SQLserver MSSQLSRV01\SQL01
LANGuage     enu
VSSPOLICY * * * * MSSQL_MGT
</code>

Configure services like this:
  Client acceptor (for baclient in auto startup)
  Client remote (for baclient in manual startup)
  Client Scheduler (for baclient in manual startup)
  Client Scheduler for SQL (in auto startup)

On Spectrum Protect server:
<cli>
Protect: ISPPROD> reg node MSSQLSRV01 password dom=SQL passe=0 backdel=yes maxnummp=16
Protect: ISPPROD> reg node MSSQLSRV01_SQL password dom=SQL passe=0 backdel=yes maxnummp=16
Protect: ISPPROD> grant proxy agent=MSSQLSRV01 target=MSSQLSRV01_SQL
</cli>

====== Debugging ======

Tivoli Storage Manager API trace
Enable tracing with the DP/SQL DSM.OPT file and the "TRACEFILE" and "TRACEFLAGS" keywords. The following entry is an example of the entry in the DP/SQL DSM.OPT file:

  TRACEFILE APITRACE.TXT
  TRACEFLAG SERVICE

DSMAGENT trace
Enable tracing with the DSMAGENT DSM.OPT file and the "TRACEFILE" and "TRACEFLAGS" keywords. The following entry is an example of the entry in the DSMAGENT DSM.OPT file:

  TRACEFILE AGTTRACE.TXT
  TRACEFLAG ALL_VSS

See the following examples:

 Command line client:
   TDPSQLC BACKUP pubs FULL /TRACEFILE=trace.log /TRACEFLAG=SERVICE,API

 GUI client:
   TDPSQL /TRACEFILE=trace.log /TRACEFLAG=SERVICE,API

**On latest version of TDP, in the Microsoft MMC, use the following procedure to trace:**

    When you encounter a problem in Microsoft Management Console (MMC), create trace files by using the Diagnostics property page.
        Click Properties > Diagnostics, and click Begin.
        Close the property page and reproduce the problem.
        Open the Diagnostics property page and click Stop. Clicking the Diagnostics button is the preferred method for gathering information to send to your service representative. This method gathers all the information that is needed. Even if a problem occurs only on the command-line interface, command, you can always gather information by using the Automate tab. The log files are displayed in the Trace and Log Files view.
    Click the trace or log file that you want to view. The contents of the file are displayed in the results pane.

===== Problem with Visual Console =====

On Spectrum Protect 8.1.2 and higher

Bad right access for users

SQL service account use for Visual Console are not enough, you have to set full control for this user on the following folders.

Installation folder, if différent from default :
  full control for the user on C:\Program Files\Tivoli

Folder that holds dsmcert certificates:
  full control for the user on C:\Program Files\tivoli

Folder that holds server certificates:
  full control for the user on c :\ProgramData\Tivoli

===== User Permissions for backup SQL =====

Permissions to backup/restore self-contained application data for MS SQL.

Backup rights:

Users with the db_backupoperator database role are granted to run the self-contained application data backup.

If the user is a member of the SQL Server sysadmin fixed server role, he can back up any databases of Microsoft SQL Server instance. The user can also backup the databases for which he is the owner and if he doesn't have backup rights to a specific database.

Restore rights:

If the database already exists, the user can perform the restore if he is a member of the dbcreator fixed server role, or if the user is the database owner.

Users with Microsoft SQL Server sysadmin fixed server role, have rights to restore a database from any backup sets. For other users, the situation depends on whether the database already exists.


Minimum backup rights 		Minimum restore rights
sysadmin Server-level role
or
db_backupoperator Database-Level role
or
dbo_owner Database-Level role 		sysadmin Server-Level role
or
dbcreator Server-Level role
or
db_owner Database-Level role

MS SQL roles : Database-Level Roles and Server-Level Roles

In addition to the requirement described from the table above, UAC (User Account Control) must be disabled in Windows 2008 and Windows 2012.

Permissions to backup and restore can be granted using any of the following methods:

    Use a built-in administrator account; or
    Disable the User Account Control (UAC) security component. This method requires you to access

the User Account Control Settings dialog in your Windows system Control Panel, and move the slider
to the Never notify setting; or

    Disable the Admin Approval Mode security policy setting. This method requires you to access the

Local Security Settings dialog on your Windows system and disable the User Account Control: Run all
administrators in Admin Approval Mode policy setting.

After the UAC has been disabled, the Windows system must be restarted for the change to take effect. 

===== Problems =====

Expiration not working as expected:

This will cause any Active Versions of data that is older than the desired retentions to be inactivated. The data will then be removed from storage based on the retentions in the Active Policyset.
<cli prompt='>'>
c:\TSM\TDPsql> tdpsqlc inactivate * * /olderthan=21
</cli>


===== Links =====

https://jackiechen.org/2012/06/14/tsm-6-3-backup-sql-database/

http://publib.boulder.ibm.com/tividd/td/DPSQLN/SH26-4111-01/en_US/HTML/ab5m1m10.htm

/SQLUSer=sqlusername
The /sqluser parameter specifies the name that TDP for SQL uses to log on to the SQL server.
Considerations:

    Using this parameter means that you are using SQL Server authentication. The SQL Server and the SQL user id for this password must both be configured for SQL Server authentication.
    Note: 	SQL Server authentication is provided by SQL Server only for compatibility with prior releases; Microsoft recommends using Windows authentication. To specify this, see page ***.
    The SQL user id must have the SQL server SYSADMIN fixed server role.
    If you do not specify /sqluser, the default is sa.
    If you specify /sqluser but not sqlusername, the default is also sa.
    Note: 	This parameter is ignored if you use the /sqlauth=integrated parameter with it. 
    
==== script backup full ====

**C:\Program Files\Tivoli\TSM\TDPSql\sqlfull.cmd**

Do a full backup of the database followed by a log backup every day.
<code>
@ECHO OFF
 
rem ----------------
rem SET ENVIRONMENT
rem ----------------
 
set sql_srv=SQLSRVS17
set sql_dir="C:\TSM\TDPSql"
 
 
set cfg_file="%sql_dir%\tdpexc.cfg"
set tsm_opt="%sql_dir%\dsm.opt"
set log_file="%sql_dir%\sqlinc.log"
set schedlog_file="%sql_dir%\sqlschedinc.log"
 
 
cd /d %sql_dir%
 
rem ----------------
rem ROTATE LOGS
rem ----------------
 
if exist %log_file%.3 del %log_file%.3
if exist %log_file%.2 move %log_file%.2 %log_file%.3
if exist %log_file%.1 move %log_file%.1 %log_file%.2
if exist %log_file%   move %log_file%   %log_file%.1
 
if exist %schedlog_file%.3 del %schedlog_file%.3
if exist %schedlog_file%.2 move %schedlog_file%.2 %schedlog_file%.3
if exist %schedlog_file%.1 move %schedlog_file%.1 %schedlog_file%.2
if exist %schedlog_file%   move %schedlog_file%   %schedlog_file%.1

rem --------------------
rem START INCR BACKUP
rem --------------------
 
echo Current date is: >> %schedlog_file%
date /t < NUL >> %schedlog_file%
echo Current time is: >> %schedlog_file%
time /t < NUL >> %schedlog_file%

%sql_dir%\tdpsqlc backup * full /configfile=%cfg_file% /tsmoptfile=%tsm_opt% /backupmethod=legacy /logfile=%log_file% >> %schedlog_file%
%sql_dir%\tdpsqlc backup * log  /trunc=no /configfile=%cfg_file% /tsmoptfile=%tsm_opt% /backupmethod=legacy /logfile=%log_file% >> %schedlog_file%

set RC=%ERRORLEVEL%
echo ——————— >> %schedlog_file%
echo Return code was %RC%  >> %schedlog_file%
echo ===================== >> %schedlog_file%
exit %RC% 

</code>

**The Transaction Log (SQL Server) SQL Server 2016 and later**

Every SQL Server database has a transaction log that records all transactions, and the database modifications made by each transaction. Except master.

The transaction log is a critical component of the database. If there is a system failure, you will need that log to bring your database back to a consistent state. Never delete or move this log unless you fully understand the ramifications of doing so.

To backup transaction log, is only supported in Legacy mode (not in VSS!)
  tdpsqlc back DBName log /truncate=yes /BACKUPMETHod=LEGACY

Add this to dsm.opt 
  INCLUDE "\...\DB1\...\log*" MC_MSSQLLOG
wiki

User Tools

Site Tools

Page Tools
