Archive

Archive for May, 2011

Recover Lost .RDL Files *

May 17, 2011 1 comment

 

Recently , per company wide policy my office machine was upgraded to Windows Server 2008 . Even after backing up everything I missed lot of my precious technical arsenal. That included my diagnostic scripts and number of .rdl files that I created and published for my DB admin work. For a moment I was distraught. Feeling of creating more than 50 reports again was painful but right at that moment I realized that I had not deleted my old ReportServer DB . Kind of blessing out of nowhere.

But again I wasn’t sure if ReportServer DB keep definition of every RDL and datasources. After 30 mins of table surfing I found out it does. Voila

I attached my old ReportServer DB and queried table dbo.Catalog  . Catalog table keeps definition of all the data sources and reports. Catalog table have column named “content” which is of image datatype .

You can run below query to get the definition of data sources and .rdl file from Catalog table. Value of column Type distinguish between the datasources and reports.

For datasources Type would be 5 and for reports Type would be 2 so for getting the definition of reports in Xml format we can run below query.

   1: SELECT

   2:      NAME,CAST(CAST(CONTENT AS VARBINARY(MAX)) AS XML) AS ReportDef

   3:   FROM dbo.catalog

   4:   WHERE TYPE = 2

 

For datasources :

   1: SELECT

   2:       NAME,CAST(CAST(CONTENT AS VARBINARY(MAX)) AS XML) AS ReportDef

   3:    FROM dbo.catalog

   4:    WHERE TYPE = 5

This would output definition in Xml format.

Rename the xml output as .rdl for reports and .rds for data sources. All set. Reports and datasources are ready again.

I had to make couple of other changes in .rds file which might be specific to my env. I was seeing some xml format error. So I created one dummy datasource and grabbed couple of xml format specific lines from dummy datasource .

That saved me hours of time that I would have spent creating reports and re-writing all the scripts.

Good luck until next tidbit.

–NJ

Advertisements
Categories: Uncategorized

My First Writing–Intro

May 11, 2011 Leave a comment

 

My Name is Neeraj Jandwani. I live in San Francisco Bay Area . Working as Senior Sql Server DBA .. Time just flies as I didn’t realize that it’s been 10 years I have been working with Sql Server. Yes, I know I am late comer in blogging world but It’s never too late. Here I will be blogging about my experiences with Sql Server and related technologies..I hope I don’t procrastinate and start blogging for real this time.

Ok..Will be back soon.

Neeraj

Categories: Uncategorized