Skip to content

April 15, 2013


Export RDL Files from ReportServer Database with PowerShell

by Scott Newman

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

$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
Read more from Powershell, SQL Server
3 Comments Post a comment
  1. Just Will
    Apr 12 2013

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

    • Apr 12 2013

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

      • Just Will
        Apr 12 2013

        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: Logo

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

Note: HTML is allowed. Your email address will never be published.

Subscribe to comments

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

%d bloggers like this: