SQL Select command - Last status for each SQL Database?

[jcpatum]

I've posted this before, but I think the question was misunderstood.

I need to get the last backup status for every SQL Server Database we back up with TSM. This is a status on each SQL Database not just the SQL server node.

Today I see this information is spit out of in a log file every night on each client.

How can I get to this data (via SQL Select) from the TSM Databases?

Thanks for any help or information.

 

[rore]

Hi,
Apart from client logs the only location in TSM server is the activity log (for individual SQL databases backup status):

Sample:
02/23/2009 03:30:23 ANE4991I (Session: 839874, Node: SERVER1_SQL) TDP
MSSQL Win32 ACO3000 Data Protection for SQL: Starting
full backup of database msdb from server SERVER1.
(SESSION: 839874)
02/23/2009 03:30:26 ANE4991I (Session: 839874, Node: SERVER1_SQL) TDP
MSSQL Win32 ACO3001 Data Protection for SQL: full backup
of database msdb from server SERVER1 completed
successfully. (SESSION: 839874)

You might want to build a script to parse the q actlog results.

Hope this helps,

Rudy

Edit: you might want to check the 'enable events' option to send selected messages to a file or other receiver.

 

[jcpatum]

I'm not the TSM Admin.

Are you saying there is one centralized Activity log somewhere on the TSM server?

Will every entry be uniform like what you posted?

I started writing a parser using logs on the clients but ran into problems due to differences in text due to client TSM- versions.

It's somewhat hard to accept that they don't write some form status to the database so we can query directly from a DB???

Thanks for your response.

 

[rore]

I'm not the TSM Admin.

No problem, TSM docs and the forum are good place to learn.
Ask the TSM admin if there is anything already in place.

Are you saying there is one centralized Activity log somewhere on the TSM server?

Yes. Almost all TSM configuration and logs are keeped in the TSM database.

Will every entry be uniform like what you posted?

I started writing a parser using logs on the clients but ran into problems due to differences in text due to client TSM- versions.

It could be different. Check with your installed versions.

It's somewhat hard to accept that they don't write some form status to the database so we can query directly from a DB???

Thanks for your response.

Well, you can query directly the DB ('select from actlog'). Depending on the size of your DB it could take some time. But you need to parse anyway to have a human readable report.

Rudy

 

[lidra]

Hi Guys,

I dont know if you solved your problem, but I am going to post anyway so anyone trying to get backup stats for all SQL databases can do so.

You need a select that does the job for you.
I didnt spend to much time on the following (I can improve it if needed):

select DATE_TIME,MESSAGE,NODENAME from actlog where MESSAGE like '%%ANE4991I%%' and MESSAGE like '%%full backup of database%%' and DATE_TIME >=current_timestamp - 24 hours order by NODENAME > SQL.txt

The above select will give you a time stamp, the actual message of starting and finishing the backup of the xxx database and the node name. The result is oder by Nodename, that means you import that in Excel you get a nice report with all the databases / node.

The output is directed to a file (named sql.txt) that you will find in the directory where you run dsmadmc (c:\program files\tivoli\tsm\baclient\ - for windows) or current directory if you run it under linux/unix.

Also the report runs for the last 24 hours. If you need for longer time, modify the 24 with whatever you like.

Note: Before running the above select, run a "set sqldisplaymode wide" .

I can do some magic around the time (return date and hour, not that ugly time stamp).
Also I might be able to do something with the message.

Let me know if you need more help.

Cheers,

Lidra