Day: April 15, 2013

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
}