<# .SYNOPSIS
      Export of SSRS reports datasources and images
   .DESCRIPTION
      This PowerShell script exports all (or filtered) reports, data sources and images directly from the ReportServer $ReportServerDB
      to a specified folder. For the file name the complete report path is used; for file name invalid characters are replaced with a -.
      Reports are exported with .rdl as extension, data sources with .rds and resources without any additional extension.
      Please change the "Configuration data" below to your enviroment.
      Works with SQL Server 2005 and higher versions in all editions.
      Requires SELECT permission on the ReportServer $ReportServerDB.
   .NOTES
      Author  : Olaf Helper
      Requires: PowerShell Version 1.0, Ado.Net assembly
	  Modified By: Nawaz Kazi
	  Modificaion: Updated to export all items, not just reports
   .LINK
      GetSqlBinary: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getsqlbinary.aspx
#>

#// Parameters
[cmdletbinding(SupportsShouldProcess=$true)]
Param(
	[parameter(Mandatory=$true,HelpMessage="Site Server where SQL Server Reporting Services are installed")]
    [ValidateScript({Test-Connection -ComputerName $_ -Count 1})]
    [string]$ReportServer,
	[parameter(Mandatory=$false,HelpMessage="Name of the ReportServer database. Defaults to ReportServer")]
    [string]$ReportServerDB = "ReportServer",
	[parameter(Mandatory=$true,HelpMessage="Path to where the reports will be exported")]
    [string]$ExportPath
)

# Select-Statement for file name & blob data with filter.
$sql = "SELECT CT.[Path]
              ,CT.[Type]
              ,CONVERT(varbinary(max), CT.[Content]) AS BinaryContent
        FROM dbo.[Catalog] AS CT
        WHERE CT.[Type] <> 1";

# Open ADO.NET Connection with Windows authentification.
$con = New-Object Data.SqlClient.SqlConnection;
$con.ConnectionString = "Data Source=$ReportServer;Initial Catalog=$ReportServerDB;Integrated Security=True;";
$con.Open();

Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Started ...");

# New command and reader.
$cmd = New-Object Data.SqlClient.SqlCommand $sql, $con;
$cmd.CommandTimeout = 60;
$rd = $cmd.ExecuteReader();

$invalids = [System.IO.Path]::GetInvalidFileNameChars();
# Looping through all selected datasets.
While ($rd.Read())
{
    Try
    {
        # Get the name and make it valid.
        $name = $rd.GetString(0);
		$SubPath = (Split-Path -Path $name).TrimStart("\")
        $ReportName = Split-Path -Path $name -Leaf
		
        foreach ($invalid in $invalids)
           {    $ReportName = $ReportName.Replace($invalid, "-");    }

        Switch ($rd.GetInt32(1))
		{
			2 { $ReportName += ".rdl"; }
			5 { $ReportName += ".rds"; }
			6 { $ReportName += ".mdl"; }
			8 { $ReportName += ".rsd"; }
			9 { $ReportName += ".res"; }
		}

		$ReportFileName = -join ($ExportPath,"\",$SubPath,"\",$ReportName)
        # Create additional directories under the export path
        if (-not(Test-Path -Path (-join ($ExportPath,"\",$SubPath)))) {
           New-Item -Path (-join ($ExportPath,"\",$SubPath)) -ItemType Directory -Force -Verbose:$false | Out-Null
        }
        Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Exporting {0}" -f $ReportFileName);

        #$name = [System.IO.Path]::Combine($ExportPath, $name);

        # New BinaryWriter; existing file will be overwritten.
        $fs = New-Object System.IO.FileStream ($ReportFileName), Create, Write;
        $bw = New-Object System.IO.BinaryWriter($fs);

        # Read of complete Blob with GetSqlBinary
        $bt = $rd.GetSqlBinary(2).Value;
        $bw.Write($bt, 0, $bt.Length);
        $bw.Flush();
        $bw.Close();
        $fs.Close();
    }
    Catch
    {
        Write-Output ($_.Exception.Message)
    }
    Finally
    {
        $fs.Dispose();
    }
}

# Closing & Disposing all objects
$rd.Close();
$cmd.Dispose();
$con.Close();
$con.Dispose();

Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Finished");