Export RDL Files from ReportServer Database with PowerShell

Quick (and long overdue) post on how to extract rdl files directly from the ReportServer database with powershell.

We have an old sql 2k box here that has a bunch of reports on it, and for some reason I’m not able to browse said reports.  I needed to get some of these reports out of this database and sent off to a client, but no matter what permissions I assigned in IIS, I still could not see the reports.  (I don’t give a tinker’s cuss about reporting in general).

This code (which uses SQLPSX, but no reason you couldn’t use native smo) extracts the reports from the dbo.Catalog table and writes them directly to file.  Easy peasy.

Import-Module SqlServer

$rows = Get-SqlData $(Get-SqlServer -sqlserver 'ServerName' -username 'LoginName' -password 'Password') `
	-dbname 'ReportServer' -qry 'SELECT * FROM dbo.Catalog WHERE Content IS NOT NULL'

foreach($row in $rows){
	$row.Content | Set-Content -Path "c:\Test\Reports\$($row.Name).rdl" -Encoding Byte
}

Keep in mind, all these reports were uniquely named.  If you have any reports that are named the same under different directories, you’ll need to implement code that will handle that.

Update 20130415

My good friend Will asked how to create the directories when doing an export from the Path column in the dbo.Catalog table.  Here is how you would accomplish this:

Import-Module SqlServer
cls

$destDir = 'c:\Test'

#if you're using windows auth, you can just pass in the server name in the -sqlserver
#instead of having to use the Get-SqlServer cmdlet.  This uses sqlpsx.
$rows = Get-SqlData $(Get-SqlServer -sqlserver 'ServerName' -username 'LoginName' -password 'Password!') -dbname 'ReportServer' -qry 'SELECT * FROM dbo.Catalog WHERE Content IS NOT NULL'

foreach($row in $rows){
	$newDir = $row.Path.ToString() -replace '/', '\'
	#new-item will automagically create the directory from the string variable
	#for example, string "c:\test\dir1\dir2" will create all three directories
	New-Item -ItemType Directory -Path "$($destDir)$($newDir)" -ErrorAction SilentlyContinue
	$row.Content | Set-Content -Path "$($destDir)\$($newDir)\$($row.Name).rdl" -Encoding Byte
}
Advertisements

3 Comments on “Export RDL Files from ReportServer Database with PowerShell

  1. Did you leave out the path for simplicity, or did you just want a dump of all of the reports?

    • Simplicity. I just wanted a dump of them all. Did you need one to mimic the path? ( I could whip one up quick to do that too).

      • That would be nice. I’m still trying to find the time to work through “Learn powershell 3 in a month of lunches”, along with about 5 various slide decks I’ve come across…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: