How to recover a report from a backup copy of the report server database

[mjbernier]

I have a developer who was a bit aggressive when cleaning up some of his reports and accidentally deleted one off our report server. Unfortunately, no one can locate a copy of the .pbix file, so our only option to retrieve it is to pull it from a backup copy of the report server database, which we do have.

I've read several different posts that are now a few years old explaining how to do this, but my attempts to follow those directions so far have all failed to produce a usable file. I assume this is because there have been some internal changes to the way the report definition is being stored in the report server database since those instructions were published.

Could someone please provide a set of current steps for retrieving the .pbix file from a copy of the report server database?

Thanks!

[josef78]

Correct way is retore backup to separate database, install PBIRS to some virtual separated server, connect this PBIRS to restored DB, and restore report using portal UI.

But faster way is restore blob in Content column of CatalogItemExtendedContent table (where ContentType = 'PowerBIReportDefinition' and ItemId is your report), to pbix file by any method. My favorite is using SSIS export column, which you can simple use to export all pbix reports to separated pbix files.

[mjbernier]

Thank you for your response and the sample code. Yes, I have a restored backup of the database, I can perform all of the steps you outlined, and the 'BinaryData' column is populated for the "CatalogItem" entry for the report I'm looking for. But, when I try to save that column as a .pbix file it doesn't work; opening it generates an error saying the file is either corrupted or encrypted. What am I missing?

[sevenhills · Accepted solution]

On your computer, Launch the PowerShell, (Run as administrator mode)

Configure the first three values

# Set the variables
[string] $SQL_Reportserver = "..."; >>> SQL Server\ instance name
[string] $PBIRS_DB = "..."; >>>> Database Name 
[string] $download_folder = "..."; >>> Your local computer folder, not server 

# !!! Coded for all known types but tested only .rdl, .rds, .rsd, .kpi, .pbix for my needs !!!

# Set the variables
[string] $SQL_Reportserver   = "...";    
[string] $PBIRS_DB           = "...";       
[string] $download_folder    = "...";  
   
# Extract data from report server database
$sql = "SELECT
	c.ItemID
	, c.Name
	, c.Path
	, c.Type
	, TypeDescription = 
		CASE c.Type
			WHEN 2 THEN 'SSRS Report'
			When 13 Then 'Power BI Report'

            -- Not tested ! Code works
            WHEN 1 THEN 'Folder'
            WHEN 3 THEN 'Resources'
            WHEN 4 THEN 'Linked Report'
            WHEN 5 THEN 'Data Source'
            WHEN 6 THEN 'Report Model'
            WHEN 7 THEN 'Report Part'
            WHEN 8 THEN 'Shared Dataset'
            -- 11 ... KPI
			ELSE 'Other Type (check/revisit)'

		END 
    , ISNULL(cc.ContentType,'SSRS') as ContentType
	, RDL_BinaryContent = IIF(c.Type <> 13, CONVERT(varbinary(max), c.[Content]), null)
	, PBIX_BinaryContent = IIF(c.Type = 13, CONVERT(varbinary(max), cc.[Content]), null)
    , cc.ContentType
FROM dbo.[Catalog] c
	LEFT JOIN dbo.[CatalogItemExtendedContent] cc ON c.ItemID = cc.ItemId AND cc.ContentType = 'CatalogItem'
WHERE c.TYPE IN (2, 5, 8, 11, 13)
-- AND c.ItemID ='...'
";        

cls

<#
    $SQL_Reportserver
    $PBIRS_DB
    $download_folder
    $sql
#>

Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Started ...");
  
# DB connect using Integrated security
$conn = New-Object Data.SqlClient.SqlConnection;
$conn.ConnectionString = "Data Source=$SQL_Reportserver;Initial Catalog=$PBIRS_DB;Integrated Security=True;";
$conn.Open();
 
Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Report Server DB - Connection success! ...");
 
$cmd = New-Object Data.SqlClient.SqlCommand $sql, $conn;
$rdr = $cmd.ExecuteReader();
# $invalids = [System.IO.Path]::GetInvalidFileNameChars();
 
# Loop
While ($rdr.Read())
{
    Try
    {
        Write-Output "------------------------------------------"
        $ItemName = $rdr.GetString(1);
        Write-Output "Reading $ItemName"
        
        #  foreach ($invalid in $invalids) 
        #      { $name = $name.Replace($invalid, "-"); }

        # $rdr.GetInt32(3)
        $ItemFileName = ""
        If ($rdr.GetInt32(3) -eq 2)        { $ItemFileName = $ItemName + ".rdl"; }
        ElseIf ($rdr.GetInt32(3) -eq 5)    { $ItemFileName = $ItemName + ".rds"; }
        ElseIf ($rdr.GetInt32(3) -eq 8 )    { $ItemFileName = $ItemName + ".rsd"; }
        ElseIf ($rdr.GetInt32(3) -eq 11)   { $ItemFileName = $ItemName + ".kpi"; }
        ElseIf ($rdr.GetInt32(3) -eq 13)   { $ItemFileName = $ItemName + ".pbix"; }

        if ($ItemFileName -ne "")
        {
            Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Exporting {0} ... " -f $ItemFileName);
 
            $ItemName_Path = [System.IO.Path]::Combine($download_folder, $ItemFileName);
            #$ItemName_Path
            $fs = New-Object System.IO.FileStream ($ItemName_Path), Create, Write;
            $BinaryWriter = New-Object System.IO.BinaryWriter($fs);
 
            # Read Content 
            if ($rdr.GetString(4) -eq "Power BI Report") {
                $BinaryContent = $rdr.GetSqlBinary(7).Value;
            } 
            else
            {
                $BinaryContent = $rdr.GetSqlBinary(6).Value;
            }

            $BinaryWriter.Write($BinaryContent, 0, $BinaryContent.Length);
            $BinaryWriter.Flush();
            $BinaryWriter.Close();
            $fs.Close();
        }
        else 
        {
            Write-Output $ItemFileName "... !!! NOT exported"
            Write-Output $rdr.GetInt32(3)
            Write-Output $rdr.GetString(4)
        }       
    }
    Catch
    {
        Write-Output ($_.Exception.Message)
    }
    Finally
    {
        $fs.Dispose();
    }
}
 
# Cleanup
$rdr.Close();
$cmd.Dispose();
$conn.Close();
$conn.Dispose();

Write-Output "------------------------------------------" 
Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Process Finished ... ")

[mjbernier]

This sort of works... there's a typo in the following line:

       ElseIf ($rdr.GetInt32(3) -eq     { $ItemFileName = $ItemName + ".rsd"; }

I think the value should be "8" for Shared Datasets, so I added that in. The rest of the code works and generates the output files I'm looking for. Thanks!

[sevenhills]

It is the code converter, it converts "8" and ")" makes a different character.

I checked my code and it has it. I updated my code. Thanks for the update.

[sevenhills]

I assume you had done these steps ... 

1. Restored the old backup of SQL database on a server, let us say "ReportServer_PBIRS_Backupyyyymmdd"

Note: You can have as standalone database for this, I mean you dont have to replace the existing reporting server database.

2. Able to connect to it and execute the following basic sql

Select * from catalog 

3. Can you see the pbix files? Yes / No

Select * from catalog where type = 13

4. Can you see the binary content in the column? Yes/No

SELECT	CT.[Path]
        ,CT.[Type]
		,cc.ContentType
        ,CONVERT(varbinary(max), cc.[Content]) AS BinaryContent
FROM dbo.[Catalog] AS CT
		LEFT OUTER JOIN dbo.CatalogItemExtendedContent cc
			ON ct.ItemID = cc.ItemId
WHERE CT.[Type] IN (13) -- this idicates a PBI report
	AND cc.ContentType = 'CatalogItem'
AND ct.ItemID = '...'

If yes, then you can recover the .pbix files. Let me know the answer, I can provide the next steps. 

(In my view, this is logical separation in recovery steps, hence asked)