cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dash1 Frequent Visitor
Frequent Visitor

Access report files after evaluation period expired

We used the 180 days evaluation period of Power BI Report Server to test the functionality.

Now the evaluation period has expired and we would like to access the .pbix files (report files) for editing.

 

Is there a way to restore the .pbix files from the server even if we cannot access the Report Server in the browser?

1 REPLY 1
stpnet Established Member
Established Member

Re: Access report files after evaluation period expired

You can export the files out of the backend DB using some powershell and write them out to disk. Since you are going direct to the DB the web service not running doesn't matter.

 

# Configuration data
[string] $server   = "YourSQLServerName";        # SQL Server Instance.
[string] $database = "ReportServer";        # ReportServer Database.
[string] $folder   = "C:\Export_PBI_SSRS\";          # Path to export the reports to.

# 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] IN (2, 8, 5,11,13)";
		
# replacing with a single known PBI report that has all 3 parts in the 	CatalogItemExtendedContent	
$sql = "SELECT	CT.[Path]
        ,CT.[Type]
		,ISNULL(cc.ContentType,'SSRS') as ContentType
        ,CONVERT(varbinary(max), cc.[Content]) AS PBI_BinaryContent
        ,CONVERT(varbinary(max), ct.[Content]) AS RDL_BinaryContent
FROM dbo.[Catalog] AS CT
		LEFT OUTER JOIN dbo.CatalogItemExtendedContent cc
			ON ct.ItemID = cc.ItemId
WHERE CT.[Type] IN (2, 8, 5,13)
	AND ISNULL(cc.ContentType,'CatalogItem') = 'CatalogItem'";		
 #       WHERE CT.[Type] IN (8)";

# Open ADO.NET Connection with Windows authentification.
$con = New-Object Data.SqlClient.SqlConnection;
$con.ConnectionString = "Data Source=$server;Initial Catalog=$database;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;
$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);
		Write-Output "fetching $name"
        foreach ($invalid in $invalids)
           {    $name = $name.Replace($invalid, "-");    }

        If ($rd.GetInt32(1) -eq 2)
            {    $name = $name + ".rdl";    }
        ElseIf ($rd.GetInt32(1) -eq 5)
            {    $name = $name + ".rds";    }
        ElseIf ($rd.GetInt32(1) -eq 8)
            {    $name = $name + ".rsd";    }
        ElseIf ($rd.GetInt32(1) -eq 11)
            {    $name = $name + ".kpi";    }
        ElseIf ($rd.GetInt32(1) -eq 13)
			{   $name = $name + ".pbix";    }

			
			
        Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Exporting {0}" -f $name);

        $name = [System.IO.Path]::Combine($folder, $name);

        # New BinaryWriter; existing file will be overwritten.
        $fs = New-Object System.IO.FileStream ($name), Create, Write;
        $bw = New-Object System.IO.BinaryWriter($fs);

        # Read of complete Blob with GetSqlBinary
        if ($rd.GetString(2) -eq "SSRS") {
			$bt = $rd.GetSqlBinary(4).Value;
		} else{
			$bt = $rd.GetSqlBinary(3).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");