Link to home
Get AccessLog in
Avatar of Williams225
Williams225

asked on

IBM TSM for SQL : How to set up management policy for differential back ups

Hello experts,

I use ibm TSM 7.0 to backup my exchange databases.

I know how to configure management classes for full backups, but I don't know how to do it for differential backups. Can you please help?
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

I don't know TSM but shouldn't be different from FULL backup. Just the type of backup changes.
Sometimes is only a matter of name. Some applications call Incremental instead of Differential. Might be this your issue?
Avatar of Williams225
Williams225

ASKER

an example of my opt file inside the C:\Program Files\Tivoli\TSM\TDPSql folder.
DIFF_TO_MGMT is the name of the management class.
When I execute the backup script, the management class works, but I get errors on the backups, so I don't know exactly if I follow the right procedure.
Also, I want to know if its possible to insert the management class inside the backup script instead of insert it inside the opt file. Please help!!

Example of my opt file
NODename NSA-TO-AGR
PASSWORDAccess generate
TCPServeraddress IP_ADRESS_TSM_SERVER
TCPPort 1500
HTTPport 1581
managedservices schedule
INCLUDE "\...\*\diff*" DIFF_TO_MGMT
COMPRESSION YES
COMPRESSALWAYS NO
DEDUPLICATION YES
ENABLEDEDUPCACHE NO

Open in new window


example of my backup script
@ECHO OFF

set sql_dir=C:\Progra~1\Tivoli\TSM\TDPSql
set log_dir=E:\TSM\logs
C:

cd %sql_dir%
rem  ==================================================================
rem   The two lines below put a date/time stamp in a log file for you.
rem   Note: You can change "sqlsched.log" to whatever you prefer in 
rem   lines below.
rem  ==================================================================

date /t < NUL >> %log_dir%\sqlsched_DIFF_ARESSO.log
time /t < NUL >> %log_dir%\sqlsched_DIFF_ARESSO.log

%sql_dir%\tdpsqlc backup AGR_TOGO difffull /tsmoptfile=dsm_to_AGR.opt /sqlserv=NSA-NP-SQLDB\NSASQLDB  /logfile=%log_dir%\sqlDIFF_TO_AGR.log >> %log_dir%\sqlsched_DIFF_ARESSO.log

set RC=%ERRORLEVEL%
echo --------------------- >> %log_dir%\sqlsched_DIFF_ARESSO.log
echo Return code was %RC%  >> %log_dir%\sqlsched_DIFF_ARESSO.log
echo ===================== >> %log_dir%\sqlsched_DIFF_ARESSO.log
exit %RC%

Open in new window

Can you post the error?
Here is the differential backup log. I had to translate it with google translate because the log was in french.

03/31/2016 9:42:24 ======================================== =================================
03/31/2016 9:42:24 Application DIFF BACKUP
03/31/2016 9:42:24 list of entries in the database: AGR_TOGO
03/31/2016 9:42:24 list group entries: -
03/31/2016 9:42:24 list file entries: -
03/31/2016 9:42:24 Number of buffers: 3
03/31/2016 9:42:24 Buffer Size: 1024
03/31/2016 9:42:24 Number of buffers SQL: 0
03/31/2016 9:42:24 Buffer Size SQL: 1024
03/31/2016 9:42:24 Number of indicated segments: 1
03/31/2016 9:42:24 Rating: 20
03/31/2016 9:42:24 truncate the log? : -
03/31/2016 9:42:24 Waiting for mounting tapes? : Yes
03/31/2016 9:42:24 File TSM options: dsm_to_AR.opt
03/31/2016 9:42:24 Replacing the TSM node name: -
03/31/2016 9:42:25 Sql Server: NSA-NP-SQLDB \ NSASQLDB
03/31/2016 9:42:25
03/31/2016 9:42:35 ACO5436E An error on the segment number occurred (0), rc = 1301
03/31/2016 9:42:35 ANS0328E (RC45) The specified objects failed to merge.
03/31/2016 9:42:37 ACO5436E An error on the segment number occurred (0), rc = 1301
03/31/2016 9:42:37 ANS0328E (RC45) The specified objects failed to merge.
03/31/2016 9:42:37 AGR_TOGO The backup failed.
03/31/2016 9:42:37 ANS0328E (RC45) The specified objects failed to merge.
03/31/2016 9:42:37 Total SQL backups selected: 1
03/31/2016 9:42:37 Total SQL backups attempted: 1
03/31/2016 9:42:37 Total SQL backups leading 0
03/31/2016 9:42:37 Total SQL backups excluded: 0
03/31/2016 9:42:37 Total SQL backups deduplicated: 0
03/31/2016 9:42:37 rate: 2 845.87 Kb / Sec
03/31/2016 9:42:37 Total bytes inspected: 32784384
03/31/2016 9:42:37 Total number of bytes transferred: 32,784,384
03/31/2016 9:42:37 Total bytes transferred LAN-: 0
03/31/2016 9:42:37 Total bytes before deduplication: 0
03/31/2016 9:42:37 Total number of bytes after deduplication: 0
03/31/2016 9:42:37 data compressed by 0%
03/31/2016 9:42:37 Reduce deduplication: 0.00%
03/31/2016 9:42:37 Total data reduction ratio: 0.00%
03/31/2016 9:42:37 Elapsed processing time: 11.25 Secs
03/31/2016 9:42:37 ACO0151E Errors occurred while processing the request.
03/31/2016 9:42:50 ACO0151E Errors occurred while processing the request.

Open in new window

This seems to me an issue with the TSM itself and not with SQL Server.
Check this thread if it helps you.
Yes I know its a TSM issue. Thats the reason why I asked if I did an error inside the opt file while I added the management class
Sure. I was expecting a SQL Server error so I could help you better.
From the IBM forum looks like the issue is resolved by changing the management class.
Hi,
you shouldn't change mgmt class on TDP SQL, IBM recommends NOT to do that: you should have created a new node with the wanted mgmt class.

Here is from IBM Support:

DP for SQL may fail with the following error:

ANS0328E (RC45) The specified objects failed the merge test.

when the management class is changed from the previous backup, and the new objects have different copy destinations, or if the VEREXIST, VERDELETE, RETONLY, RETEXTRA values change for the management class. This is a limitation of the product recorded in APAR IC33026.

Workaround: You can use a new Nodename for your backups or rename the Filespaces for the current backups. Both circumventions will allow you to retain the older backups and also allow you to perform the new backups with different management class settings than the original.

hope this helps
max
Hello Max the king ,

Thanx for your reply.

If I understand well, I shouldn't insert a management class inside the opt file of tdpsql folder, and I also need to create a node for the full backup , and a node for differential backups to be able to use a unique management class for each backups?

We actually do differential backups from monday to friday, and full back up on saturday.

I want to be able to restore up to 2 weeks or even a month of differential backups. So I am looking for the best practices?

Also I wonder if its possible to include the management class inside the tsm backup script
ASKER CERTIFIED SOLUTION
Avatar of max_the_king
max_the_king

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Thanx a lot.

There is one thing I still don't understand. If I have 1 node for full backup with a management class for full,
and one node for diff backup with its own management class.

What will be the process to restore? Will I have to restore the full from one node, and then the differential backup from the other node? I am a little bit confused.

Also I have noticed on the GUI that the management class for the differential backup is "default", is it possible to change the settings of the default management class? Is it different from standard?

User generated image
SOLUTION
Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Thanx a lot Max The king, i really I appreciate. I would like to ask you one question which seems like a dumb question, but I dont actually find that info online.

Is  it possible to manually activate "inactive backups"?
Hi,
i'm not sure i understand your question: do you want to set an "inactive" database to "active" state ? Why ?
"Inactive" versions are the ones (older than latest) that become "inactive" in order to be expired by tsm server in the way the tsm policy is driving. In fact, TSM server is not allowed to delete active objects after expiration process. So after mormal scheduling of tdpsql it will mark older versions as "inactive".
If by any chance some object would be in the active state and you want to delete them, then you would use a tdpsql command to set it as "inactive" and then let tsm server delete it: in this case you would use the "inactivate db" command, such as for example:

tdpsqlc inactivate dbname * /fromsqlserv=servername /olderthan=7

hope this helps
max