cancel

Saved Searches location in TRIM?

Highlighted
James_Aardvark
Super Contributor.

Saved Searches location in TRIM?

Hi all,

 

A large number of saved searches have been deleted.  Does anyone know the drive location of saved searches so I can pull out just those from back-up?

 

We are using 6.2.4.1240.

 

Regards,

 

James

1 REPLY
EWillsey
Acclaimed Contributor.

Re: Saved Searches location in TRIM?

James,

 

Saved searches are not stored on the filesystem.  They are stored in the database.  So if you wish to restore you have to go through the exercise of restoration from specific tables in a database backup.

 

From a previous post of mine:

 

First and foremost, understand how saved searches are stored in the TRIM database. The "TSSRHSEARC" table holds the basic information about the search (such as name, description, whether it's public, has access controls, is for an individual user, and also its' URI). The URI of the saved search is important. The actual details of the search itself (like which fields are searched, what sorts and filters are applied) are stored in the TSNOTES table. If there are access controls on the search, those are stored in the TSACCESSCO table.

To make it easier let's just run through the process of restoring just the search name and definition (not any access controls). Assume your database name is "ABC".

Steps in SQL Enterprise Studio:
1) Backup your production database (to a new backup file, not the old one)
2) Restore the database backup of "ABC" and call it "ABC_Restored". Make sure you are changing the database and log file locations.
3) Open the "ABC_Restored" database and navigate to the TSSRHSEAC table, opening the contents.
4) Find the search in question and note the URI (referred to in the rest of the steps as <URI>).
5) Open a new SQL text pane
6) Enter the following SQL into the pane, replacing the <URI> component with the URI, "ABC" with your database name, and "ABC_Restore" with your restored database name values from step 4.

insert into abc.dbo.tssrhsearc(uri, srhName, srhDesc, srhIsPublic, srhUser, srhLastModified, srhAccessLoc, sysLastUpdated) select uri, srhName, srhDesc, srhIsPublic, srhUser, srhLastModified, srhAccessLoc, sysLastUpdated from ABC_Restore.dbo.TSSRHSEARC where uri = <URI>

7) Run the SQL from the previous step, and now use the following statement to re-create the definition in the TSNOTES table

insert into abc.dbo.tsnotes(uri, ntTableId, ntTableUri, notesChunk, notesText, notesLength, ntNameUri) select uri, ntTableId, ntTableUri, notesChunk, notesText, notesLength, ntNameUri from abc_restore.dbo.tsnotes where tableID = 61 AND ntTableUri = <URI>

8)Run the SQL from the previous step.

Viola! You've restored the saved search.

Sometimes it's quicker for non-techies to just recreate the searches manually if there aren't many of them. Notes you can also easily tweak the statements so just update any saved search that are missing, but per your request I've targeted the statements to individual saved searches.

I hope this helps.