Table of Contents
config example
IMPORTANT: MSSQL database name are case sensitive !
Option file for baclient: dsm.opt
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$"
Option file for TDPsql: dsm.opt
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
Config file for TDPsql: tdpsql.cfg
LOCALDSMAgentnode MSSQLSRV01 BACKUPMETHod vss LASTPRUNEDate 01/20/2017 14:45:45 SQLserver MSSQLSRV01\SQL01 LANGuage enu VSSPOLICY * * * * MSSQL_MGT
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:
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
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.
c:\TSM\TDPsql> tdpsqlc inactivate * * /olderthan=21
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.
@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%
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
