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
}

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

      1. 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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.