Script to run LOG backup

[osi9400]

I have two scripts to run SQL Server backups. The full now executes great thanks to the people on this board. The incr executes in the same manner but does not back up any data.

Here is the backup line in the script:

tdpsqlc backup * log /tsmnode=WMSTUTENG1_S /tsmp=xxxxx /tsmoptfile=dsm.opt /logfile=sqlinc.log

The script executes, but I get this in the log. notice that it states backups attempted=0:

04/17/2009 08:48:22 Request                       : LOG BACKUP
04/17/2009 08:48:22 Database Input List           : *
04/17/2009 08:48:22 Group Input List              : -
04/17/2009 08:48:22 File Input List               : -
04/17/2009 08:48:22 Number of Buffers             : 3
04/17/2009 08:48:22 Buffer Size                   : 1024
04/17/2009 08:48:22 Number of SQL Buffers         : 0
04/17/2009 08:48:22 SQL Buffer Size               : 1024
04/17/2009 08:48:22 Number of Stripes specified   : 1
04/17/2009 08:48:22 Estimate                      : 0
04/17/2009 08:48:22 Truncate Log?                 : Yes
04/17/2009 08:48:22 Wait for Tape Mounts?         : Yes
04/17/2009 08:48:22 TSM Options File              : C:\Progra~1\Tivoli\TSM\TDPSql\dsm.opt
04/17/2009 08:48:22 TSM Nodename Override         : WMSTUTENG1_S
04/17/2009 08:48:22 Sqlserver                     : WMSTUTENG1\OASYSHDB
04/17/2009 08:48:22   
04/17/2009 08:48:23 Total SQL backups selected:               13
04/17/2009 08:48:23 Total SQL backups attempted:              0
04/17/2009 08:48:23 Total SQL backups completed:              0
04/17/2009 08:48:23 Total SQL backups excluded:               13
04/17/2009 08:48:23 Throughput rate:                          0.00 Kb/Sec
04/17/2009 08:48:23 Total bytes transferred:                  0
04/17/2009 08:48:23 Elapsed processing time:                  0.00 Secs

Can I not use the "*" in a logback up?

 

[Mita201]

Are your SQL databases in correct state to allow log backups? Can you backup logs of those databases logs using TSM SQL client GUI?

 

[osi9400]

That's an excellent question. When I try to run a log backup with the GUI, I see a black x on every database. What is the option to enable this in SQL?

 

[rore]

Hi,
Full recovery mode.

Also you cannot use * in your backup statement... it will try to take a log backup of the system database (master) which is not supported.

Rudy

 

[osi9400]

Thanks for the fast reply. If I leave * in the statement, will it simply skip master? Or do I need to list each database individually?

Hi,
Full recovery mode.

Also you cannot use * in your backup statement... it will try to take a log backup of the system database (master) which is not supported.

Rudy

 

[rore]

TDP 5.3 yes, list each database individually.

I am not sure if newer TDP versions are different on that.

Rudy

 

[JohanW]

It says backups selected 13, excluded 13, attempted 0. Looks like you're excluding all the log backups. backup * should work fine, automatically skipping over databases with simple logging, like master. Could you post the relevant exclude line from the tdpsql.cfg?

 

[rore]

It says backups selected 13, excluded 13, attempted 0. Looks like you're excluding all the log backups. backup * should work fine, automatically skipping over databases with simple logging, like master. Could you post the relevant exclude line from the tdpsql.cfg?

Nope,
As I said it is not supported (it will fail):

tdpsqlc.exe backup * log
 
04/17/2009 15:06:35 Backup of master failed.
04/17/2009 15:06:35 ACO5422E Received the following from the MS SQL server:
04/17/2009 15:06:35 [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot back up the log of the master database. Use BACKUP DATABASE instead.
04/17/2009 15:06:35 [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP LOG is terminating abnormally. Microsoft SQL-DMO (ODBC SQLState: 42000) (HRESULT:0x80041074)

Rudy

 

[djchopps0013]

you need to exclude the db's you dont want log backups of like master in dsm.opt file as follows:

EXCLUDE "\...\master\...\log*"
EXCLUDE "\...\msdb\...\log*"
EXCLUDE "\...\model\...\log*"
EXCLUDE "\...\distribution\...\log*"

If SQL db is in simple mode logs will fail also.

 

[osi9400]

JohanW-

here is the entirety of the TDPSQL.CFG file.

LASTPRUNEDate     04/14/2009 14:58:22
SQLSERVer     PCCTDSMSSQL\OASYSHDB
FROMSQLserver     PCCTDSMSSQL\OASYSHDB
SQLAUTHentication     INTegrated
MOUNTWaitfordata     Yes
BACKUPMethod     LEGACY
DIFFESTimate     20
BUFFers     3
BUFFERSIze     1024
STRIPes     1
SQLBUFFers     0
SQLBUFFERSIze     1024
LOGPrune     60
LANGuage     ENU

It says backups selected 13, excluded 13, attempted 0. Looks like you're excluding all the log backups. backup * should work fine, automatically skipping over databases with simple logging, like master. Could you post the relevant exclude line from the tdpsql.cfg?

 

[osi9400]

I set the backup method on two of my databases to "full recovery." The database logs are now available for backing up in the TDPSQL GUI client. This is a great forum for TSM newbies. thanks for your help. I hope others will benefit.

 

[djchopps0013]

I set the backup method on two of my databases to "full recovery." The database logs are now available for backing up in the TDPSQL GUI client. This is a great forum for TSM newbies. thanks for your help. I hope others will benefit.

They always do!!

 

[JohanW]

Just checked my own setup - should have done that before posting.

We run 5.2.something, we can backup * log because we exclude databases in simple recovery mode in dsm.opt (not tdpsql.cfg). We select 69, exclude 3, attempt 66. I do seem to remember later versions than 5.2 automatically exclude backups in simple mode though, that might explain your TDP's behaviour.