How to Query Backed up files on TSM database

[ryanbsc]

time

Hi Guys

I just wanted to query something If I run the command


dsmc query backup "/home/*" for example, it wil produce a list of all files backuped with the size and time, and name of file.


So therefore can't I do a query of the time something was backed up from the contents database in TSM?, is there definatley no time field?


Thanks


Ryan

 

[toxy13]

Hi Ryan,

the "backups" table from the tsm server contains following output:

NODE_NAME: XXX
FILESPACE_NAME: XXX
FILESPACE_ID: 4
STATE: ACTIVE_VERSION
TYPE: DIR
HL_NAME: \
LL_NAME: ADSM.SYS
OBJECT_ID: 595750
BACKUP_DATE: 2009-03-19 10:35:25.000000
DEACTIVATE_DATE:
OWNER:
CLASS_NAME: DEFAULT

but no SIZE for each backed up file :-(

 

[JohanW]

This is a very good way to crash/hang your TSM server. I'd recommend installing the ODBC driver on a Windows machine, exporting the database to a dedicated SQL server (MS SQL, Oracle, DB2 or whatever) (Access can't handle more than 2 GB databases), and querying the SQL Server offline with regards to the TSM server.

The BACKUP and CONTENTS tables typically are huge, and querying them may take hours, days, weeks even.

And the sort order you specified for your query guarantees that the entire result set has to be compiled first, then sorted, then returned.

 

[ryanbsc]

hmm

Thanks for that John?


Is there any tips, i.e how do I do this lol.
Oh how to export my DB?


I can take over , in terms of importing it to MS sql, but not sure how to export the DB from TSM

THanks

 

[toxy13]

read: http://publib.boulder.ibm.com/infoc...oc_5.3.3/also_index_dir/odbc_limitations.html

 

[ryanbsc]

?

Thanks Toxy

Read through it, but didnt really make much sense to me, or what it relates to in my scenario, I'm a bit of a newbie on TSM at the momment.


Thanks

 

[JohanW]

I suspect the easiest way is to create a package in MS SQL Enterprise Manager Data Transformation Services. Address both databases through ODBC.

Or you might (but I haven't tested this) get away with linking all TSM tables in an Access database and then upsizing to MS SQL Server; that might copy the underlying data instead of just the links.

I suspect this question has come up before, try doing a search. Not sure a specific HOWTO has ever been posted however. This is your opportunity to write it, test it, post it and bask in glory forever.

 

[ryanbsc]

hah

Thats true johan , very good point.


That aside, I thought i'd ask if there is a quick way around looking for what I want.

Basically 2 nights ago I seen that a few 10 GB files were transferred from the disk pool to the tape pool, and I'd like to know what the exact files are , so I can track them down.

Is thre any easier , quicker way other than the one suggested?


Thanks

 

[toxy13]

Is thre any easier , quicker way other than the one suggested?

No Way

 

[ryanbsc]

lol

Lol ahh well I did have to try , thankss Toxy.

Again guys I appreciate all your help .

If i do the transfer to MS SQL 2005 I shall write it up and post it up for everyone else to use.



Thanks Again .

 

[jliehr]

If you know the tape label do a q content vollabel f=d to see what is written on that volume. Then narrow it down via the server that way. Quickest way I know.

 

[fanna]

thanks toxy, found how to run that command, ......a bit scare to run that sql incase it bombs TSM

Ok ok ok.... You had found how to run that command, but what about us??

I don't know how to use select to query files backed up in the last 24 hours???


regards,
fanna

 

[ryanbsc]

hi there

Hi there


Sorry what is it your asking ?


Thanks

Ryan

 

[fanna]

Hi there


Sorry what is it your asking ?


Thanks

Ryan

Dear ryanbsc,

I want to query backed up files (each file name ) in the last 24 hours on ly?

regards,
fanna

 

[ryanbsc]

hmm

Hmm I had wanted that too but only figured out how toget a full list and not 24hours

if u can give me a bit I'll try find the database contents and if there is a time field then a " where time =<desired tired> oculd be used, but I'm pretty sure someone here siad there is no time command and thus you hae to list alll informaiton.


cheers

RYan

 

[fanna]

Hmm I had wanted that too but only figured out how toget a full list and not 24hours

if u can give me a bit I'll try find the database contents and if there is a time field then a " where time =<desired tired> oculd be used, but I'm pretty sure someone here siad there is no time command and thus you hae to list alll informaiton.


cheers

RYan

Dear Ryan,

I got your point, and I am all agree with that, but my company here wants a detailed report for that issue.... files in the last 24 hours..


regards
fanna

 

[ryanbsc]

i see

I see what you mean


If you can give me say a day or 2 I will see how we can do this.

cheers

Ryan

what versio of tivoli u runnning and on what?

 

[cjhood]

All files backed up in the last 24 hours? Something like this might give you what you want:

select node_name,filespace_name,hl_name,ll_name from backups where backup_date>=current_timestamp - 24 hours


That should output the node name and the file path for everything backed up during the previous 24 hours. Narrow your search by node name, or order by node name for nice output.

The usual disclaimer applies: Querying the backups table is a very efficient way to bring your server to it's knees. The query WILL take a very long time to complete (if at all) even on a small TSM server, and may cause your box to crash.

On a side note, what's the reason for needing to know what files were backed up every night? Is it a compliance requirement? It just seems to be useless data - there could be millions and millions of files to list out...more than anyone could ever inspect.

 

[fanna]

All files backed up in the last 24 hours? Something like this might give you what you want:

select node_name,filespace_name,hl_name,ll_name from backups where backup_date>=current_timestamp - 24 hours


That should output the node name and the file path for everything backed up during the previous 24 hours. Narrow your search by node name, or order by node name for nice output.

The usual disclaimer applies: Querying the backups table is a very efficient way to bring your server to it's knees. The query WILL take a very long time to complete (if at all) even on a small TSM server, and may cause your box to crash.

On a side note, what's the reason for needing to know what files were backed up every night? Is it a compliance requirement? It just seems to be useless data - there could be millions and millions of files to list out...more than anyone could ever inspect.

Dear cjhood,

First of all, thnxxxxxxxxxxxxxxxxx for your great select, then I could answer you that my TSM server here is just for taking a daily output oracle backup, the number of files is about 10 files only, it's so important to backup those 10 files daily and to report the dba daily that the backup was done successful !!!



Regards,
fanna

 

[cjhood]

No problems!

Hmm, didn't realise it was Oracle, it might be more efficient to query RMAN on the client rather than TSM - you'd at least get more detail from there. But on the other hand if your TSM is only backing that server up I guess the DB will be tiny.

Check: http://www.redbooks.ibm.com/redbooks/pdfs/sg246249.pdf (Chapter 7) for other ways of getting this info. RMAN commands 'report' and 'list' along with 'crosscheck' can get you this information, but you'll have to run them from the client machine.

But...whatever works for you!