It is 2020 and Microsoft still didn’t give us version control for reports, I invite you to take a moment of silence with for this nonsense.
You are working on your fancy powerbi report to impress management yourself and listening “Chemical Brothers-Galvanise” because you are just a cool kid. You added new features, it is time to update production report and publish it on PowerBI Report Server. You are feeling good and chorus is coming, your finger is on the button. You can’t wait anymore and “PUSH THAT BUTTON!“. Oops, you did it again. You accidentally overwrote your report with an older version and now wondering how you are going to get your report back. You are confident that somewhere on internet, someone should have blogged about this, after all you can’t be the only one! Well, you are an oracle.
I will give 2 PowerShell scripts for 2 different scenarios:
1- Rebuild a single RDL file: You are only interested in recovering one single RDL file
2- Rebuild all files(RDL,PBIX,RDS)
You need to perform a few steps for first scenario and adjust it according to your needs. I will explain the general idea, important tables to give an idea about the logic to individuals with no prior experience on this matter.
Preparation
First of all, Due to the fact that you published the report and persisted it to database, you have to find an old backup of Reporting Service database and restore it with a different name.
USE [master]
RESTORE DATABASE [REPORTSERVER_DATABASE_NAME] FROM DISK = N'D:\REPORTSERVER_DATABASE_NAME.bak' WITH FILE = 1, STATS = 5
GO
Now we have our desired version of the report in the database.
Reports are stored in your PowerBI Report Server database on Microsoft SQL Server instance, in [Catalog] and [CatalogItemExtendedContent] tables. Feel free to check both table’s designs and explore.
Scenario 1
If you are working on scenario 1,you have to identify your report’s unique item identifier first and build the SQL query to retrieve necessary information from database tables to rebuild it.
We will note down [Catalog].[ItemID] of the report to use it as an unique filter in PowerShell script.
SELECT [Catalog].[Path]
, [Catalog].[ItemID]
FROM dbo.[Catalog]
This query will return the data we need to rebuild specific RDL report.
SELECT [Catalog].[Path]
,[Catalog].[Type]
,ISNULL([CatalogItemExtendedContent].ContentType,'SSRS') as ContentType
,CONVERT(varbinary(max), [Catalog].[Content]) AS RDL_BinaryContent
FROM dbo.[Catalog]
LEFT OUTER JOIN dbo.[CatalogItemExtendedContent]
ON [Catalog].ItemID = [CatalogItemExtendedContent].ItemId
WHERE [Catalog].ItemID ='YOUR_REPORTS_ITEM_ID'
We are now ready to use our Powershell magic. You have to fill 4 variables into PS script:
- SQL Server Instance Name
- Reporting Service Database Name
- Windows File Path to Export Files
- Report Item Identifier
Be aware: The account runs this code needs SELECT permission on the ReportServer database!
# Set the variables!
[string] $server = "$SQLINSTANCENAME";
[string] $database = "$REPORTSERVER_DATABASE_NAME";
[string] $folder = "$EXPORT_PATH";
# Extract data from report server database
$sql = "SELECT [Catalog].[Path]
,[Catalog].[Type]
,ISNULL([CatalogItemExtendedContent].ContentType,'SSRS') as ContentType
,CONVERT(varbinary(max), [Catalog].[Content]) AS RDL_BinaryContent
FROM dbo.[Catalog]
LEFT OUTER JOIN dbo.[CatalogItemExtendedContent]
ON [Catalog].ItemID = [CatalogItemExtendedContent].ItemId
WHERE [Catalog].ItemID ='YOUR_REPORTS_ITEM_ID'";
Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Process started");
# Create Database Connection Object
# This script use Kerberos Authentication with executer's Windows credentials, If you'd like to use SQL Authentication, adjust it accordingly!
$connection = New-Object Data.SqlClient.SqlConnection;
$connection.ConnectionString = "Data Source=$server;Initial Catalog=$database;Integrated Security=True;";
$connection.Open();
Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Connection has been established with database");
# Build SQL Command Object
$command = New-Object Data.SqlClient.SqlCommand $sql, $connection;
$reader = $command.ExecuteReader();
$invalids = [System.IO.Path]::GetInvalidFileNameChars();
# Looping through all selected datasets.
While ($reader.Read())
{
Try
{
$name = $reader.GetString(0);
Write-Output "Reading $name"
foreach ($invalid in $invalids)
{ $name = $name.Replace($invalid, "-"); }
If ($reader.GetInt32(1) -eq 2)
{ $name = $name + ".rdl"; }
ElseIf ($reader.GetInt32(1) -eq 5)
{ $name = $name + ".rds"; }
ElseIf ($reader.GetInt32(1) -eq 8)
{ $name = $name + ".rsd"; }
ElseIf ($reader.GetInt32(1) -eq 11)
{ $name = $name + ".kpi"; }
ElseIf ($reader.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;
$BinaryWriter = New-Object System.IO.BinaryWriter($fs);
# Read Content
$BinaryContent = $reader.GetSqlBinary(3).Value;
$BinaryWriter.Write($BinaryContent, 0, $BinaryContent.Length);
$BinaryWriter.Flush();
$BinaryWriter.Close();
$fs.Close();
}
Catch
{
Write-Output ($_.Exception.Message)
}
Finally
{
$fs.Dispose();
}
}
# Cleanup
$reader.Close();
$command.Dispose();
$connection.Close();
$connection.Dispose();
Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Process Finished");
Now go and check your export folder. You successfully restored your report and saved your valuable time!
Scenario 2
Maybe you need more than one file or just want to be safe and rebuild everything. Then, welcome to scenario 2. this is more straight forward compared to first one. After restoring database, you can execute below PowerShell script directly. All you need to do, set the variables:
- SQL Server Instance Name
- Reporting Service Database Name
- Windows File Path to Export Files
Be aware: The account runs this code needs SELECT permission on the ReportServer database!
# Set the variables!
[string] $server = "$SQLINSTANCENAME";
[string] $database = "$REPORTSERVER_DATABASE_NAME";
[string] $folder = "$EXPORT_PATH";
# Extract data from report server database
$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'";
Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Process Started");
# Create Database Connection Object
# This script use Kerberos Authentication with executer's Windows credentials, If you'd like to use SQL Authentication, adjust it accordingly!
$connection = New-Object Data.SqlClient.SqlConnection;
$connection.ConnectionString = "Data Source=$server;Initial Catalog=$database;Integrated Security=True;";
$connection.Open();
Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Connection has been established with database");
# Build SQL Command Object
$command = New-Object Data.SqlClient.SqlCommand $sql, $connection;
$reader = $command.ExecuteReader();
$invalids = [System.IO.Path]::GetInvalidFileNameChars();
# Looping through all selected datasets.
While ($reader.Read())
{
Try
{
$name = $reader.GetString(0);
Write-Output "Reading $name"
foreach ($invalid in $invalids)
{ $name = $name.Replace($invalid, "-"); }
If ($reader.GetInt32(1) -eq 2)
{ $name = $name + ".rdl"; }
ElseIf ($reader.GetInt32(1) -eq 5)
{ $name = $name + ".rds"; }
ElseIf ($reader.GetInt32(1) -eq 8)
{ $name = $name + ".rsd"; }
ElseIf ($reader.GetInt32(1) -eq 11)
{ $name = $name + ".kpi"; }
ElseIf ($reader.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;
$BinaryWriter = New-Object System.IO.BinaryWriter($fs);
# Read Content
if ($reader.GetString(2) -eq "SSRS") {
$BinaryContent = $reader.GetSqlBinary(4).Value;
} else{
$BinaryContent = $reader.GetSqlBinary(3).Value;
}
$BinaryWriter.Write($BinaryContent, 0, $BinaryContent.Length);
$BinaryWriter.Flush();
$BinaryWriter.Close();
$fs.Close();
}
Catch
{
Write-Output ($_.Exception.Message)
}
Finally
{
$fs.Dispose();
}
}
# Cleanup
$reader.Close();
$command.Dispose();
$connection.Close();
$connection.Dispose();
Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Process Finished");
Hope this small script will save your valuable time.
Bahti Samet Coban 