Manual backup works, SQL agent does not.

[osi9400]

here's the setup:
- Windows Server 2003 R2
- MSSQL Server 2005
- TSM BAC 5.5.2.0
- TDPSQL 5.5.2.0
- Two Windows servers are clustered

I created two scripts (SQLFULL.CMD and SQLINCR.CMD) and can run both manually. They work great.

When I try to run via a SQL agent or with the query "
xp_cmdshell 'G:\Micros~1\MSSQL.1\MSSQL\Backup\sqlfull.cmd';" it fails with the following: "
ACO5716W An error was encountered with Tivoli Storage Manager API initialization, rc = 106.
Examine the dsierror.log for more information or determine if the TSM API is installed properly."

I checked the dsierror.log file and it is empty.

I can run other xp_cmdshell commands just fine. for example "xp_cmdshell ;ping localhost' and I get the ping results.

I have removed, rebooted, and reinstalled both Tivoli clients.

Why can I run the script manually, but not from within SQL Server?

Thanks.

 

[Mita201]

Can we see the script itself? Maybe you are using something you have in user PATH, that can be executed when you are in your user env (logged in)?

 

[osi9400]

Thanks for the response. Here is the code to SQLFULL.CMD:

set tsm_dir=C:\Progra~1\Tivoli\TSM\TDPSql
set sql_dir=G:\Micros~1\MSSQL.1\MSSQL\Backup

date < NUL >> %sql_dir%\sqlsched.log
time < NUL >> %sql_dir%\sqlsched.log

%tsm_dir%\tdpsqlc backup * full /tsmnode=WMSTUTDEVRTS_S /tsmp=XXXXXXX /tsmoptfile=%tsm_dir%\dsm.opt /logfile=%sql_dir%\sqlfull.log >> %sql_dir%\sqlsched.log

 

[zatogo]

If I look at a couple sqlfull.cmd examples from installs I've done (granted these files are a year or two old)...I don't see an tsm_dir variable...just an sql_dir and it points to the tdpsql folder...below is an example of what I mean:

rem ==================================================================
rem Replace "X:" with the drive where the SQL application client
rem is installed.
rem ( ensure the correct path is where this sample is in the
rem default install path )
rem ==================================================================
set sql_dir=C:\Progra~1\Tivoli\TSM\TDPSql

then the command itself:
%sql_dir%\tdpsqlc backup * full /tsmoptfile=%sql_dir%\dsm.opt /logfile=%sql_dir%\sqlfull.log >> %sql_dir%\sqlsched.log

 

[Mita201]

check this one:

http://www-01.ibm.com/support/docvi...78&loc=en_US&cs=UTF-8&lang=en&rss=ct669tivoli

it is not exactly your case, but can be hepful - rc = 106 is definitely pointing in the direction that tsm api can't write log because of file permission. Try to export 'DSMI_LOG' as system env variable, or cd to proper directory (tsm_dir) inside your script before actual tdpsqlc operation, check ntfs permission of dsierror.log...

 

[osi9400]

Thanks, one more question

Thanks for the help, I will check out that document.

Are there any special considerations since these are clustered servers (active/passive). The drives and databases can pass between the two nodes.

 

[osi9400]

Solution found.

Thanks everyone for your help. The API error posted below solved my problem. I had to do two things:

Add a line to my dsm.opt file defining an error log in a directory that SQL Server could write to:
That line is:

errorlogname C:\Progra~1\Mi6841~1\MSSQL.2\MSSQL\Backup\dsmerror.log

Obvisouly, the log files needed to also be in a writable location. so here is the SQLFULL.CMD script:

set tsm_dir=C:\Progra~1\Tivoli\TSM\TDPSql
set sql_dir=C:\Progra~1\Mi6841~1\MSSQL.2\MSSQL\Backup
 
date < NUL >> %sql_dir%\sqlsched.log
time < NUL >> %sql_dir%\sqlsched.log
 
%tsm_dir%\tdpsqlc backup * full /tsmnode=WMSTUTENG1_S /tsmp=xxxxxx /tsmoptfile=%tsm_dir%\dsm.opt /logfile=%sql_dir%\sqlfull.log >> %sql_dir%\sqlsched.log