SQL Query Content Store to Select a Single Report Specification

[Cape Cod Gunny]

I currently have SQL bak files of the content store. In the event a user deletes a critical report I'd like to implement the following:

• Restore a backup copy of the content store as ContentStoreCopy
• Run a SQL query against ContenStoreCopy to get the XML report specification of the single, deleted report
• Copy the SQL result of the XLM Report Specification to the clipboard
• Open Professional Author and Open Report from Clipboard

How do I determine the unique key of the single report from the content store?
Which Content Store table contains the XML Report Specification?

[CognosPaul]

Years and years ago I wrote a VBS script that would loop through the content store database and export every report as an XML file to your file system. The trick here is a recursive UDF to handle the directory structure, in case you have multiple reports with the same name.

You can adapt the SQL from there: https://cognospaul.com/2014/03/11/export-report-xmls-file-system-updated/

[dougp]

I created a backup/version history routine using PowerShell to extract my reports, dashboards, and data sets to text files, tidy the format for the output (XML or JSON), compare to the most recent version in my git repo, and push to the repo as appropriate.  Here is the PowerShell file that extracts from the Content Store:  https://pastebin.com/DhH4QN41.  Obviously, you'll add your own database server name, adjust the folder locations (from this code, this clearly resides at C:\ReportBackup), determine if you want all of the logging, etc.  There may be other things that depend on parts I am not providing here.

Don't just download code off the internet and expect it to not cause problems for you.  No warranty is expressed or implied, blah, blah.  Your environment is probably different from mine.  Your mileage may vary.

[Penny]

I had an issue where an author deleted a very complex report before it was deployed to our production system.  We restored the content store to a test database and I queried it to find the report, and then used plsql to extract the report definition (xml) to a text file.  From the text file I recreated the report.  The code to find our report which was named Retention Completion Draft was:

select * from cmobjnames_base where name like '%Retention%'; -- this table also has a cmid column that you need to find the specification

If you can find the report that way by name, you can get the report specification from the table cmobjprops7 and find it with select cmid = 'NNNNN'.  The column SPEC has the report definition.

Once you have found the correct record you need to write the code to pull from the SPEC column (a clob) to a text file, after that copy from text file, create a new report and pull specification from the clipboard.

We use oracle here and I could try to find the code I used to extract the clob if you need it.

No sure if this helps but I hope so.