SSRS Download All History Snapshots

Question

Is it possible to download all the history snapshots of a report at once? Preferably as a CSV. Would save a lot time instead of clicking into each one individually and selecting save as CSV.

I only see the option to Delete

Answer 1

In PowerShell, you can loop through each snapshot and save them using this example:

<#
    Description: Save SSRS Report Snapshots

#>

$sql = "
    DECLARE @ReportName    NVARCHAR(200) = 'Your Report Name'; --change to NULL for every snapshot
    DECLARE @FileFormat    NVARCHAR(50) = 'CSV'; --HTML5,PPTX,ATOM,HTML4.0,MHTML,IMAGE,EXCEL (for .xls),EXCELOPENXML (for .xlsx),WORD (for .doc),WORDOPENXML (for .docx),CSV,PDF,XML
    DECLARE @FileExtn      NVARCHAR(50) = 'csv'; 
    DECLARE @ServerName    NVARCHAR(100) = 'http://YourServerName';
    DECLARE @DateFrom      DATE = CAST(DATEADD(DAY, -1, GETDATE()) AS DATE); --change to NULL for every snapshot
    DECLARE @ExportPath    NVARCHAR(200) = 'C:\Temp\';

    SELECT 
        --[ReportID] = [c].[itemid]  
     --  , [ReportName] = [c].[name]  
     --  , [ReportPath] = [c].[path]  
     --  , [SnaphsotDate] = FORMAT([h].[snapshotdate], 'dd-MMM-yyyy')
     --  , [SnapshotDescription] = [s].[DESCRIPTION]  
     --  , [SnapshotEffectiveParams] = [s].[effectiveparams]
     --  , [SnapshotQueryParams] = [s].[queryparams]
     --  , [ScheduleName] = [sc].[name] 
     --  , [ScheduleNextRunTime] = CONVERT(VARCHAR(20), [sc].[nextruntime], 113) 
         [ExportFileName] = @ExportPath + REPLACE([c].[name], ' ', '_') + '_' + FORMAT([h].[snapshotdate], 'yyyyMMdd_HHmm') + '.' + @FileExtn
       , [SnapshotUrl] = 
            @ServerName 
          + '/ReportServer/Pages/ReportViewer.aspx?' 
          + [c].[path] + '&rs:Command=Render&rs:Format=' 
          + @FileFormat + '&rs:Snapshot=' 
          + FORMAT([h].[snapshotdate], 'yyyy-MM-ddTHH:mm:ss')
    FROM
        [ReportServer].[dbo].[History] AS [h] WITH(NOLOCK)
        INNER JOIN [ReportServer].[dbo].[SnapshotData] AS [s] WITH(NOLOCK) ON [h].[snapshotdataid] = [s].[snapshotdataid]
        INNER JOIN [ReportServer].[dbo].[Catalog] AS [c] WITH(NOLOCK) ON [c].[itemid] = [h].[reportid]
        INNER JOIN [ReportServer].[dbo].[ReportSchedule] AS [rs] WITH(NOLOCK) ON [rs].[reportid] = [h].[reportid]
        INNER JOIN [ReportServer].[dbo].[Schedule] AS [sc] WITH(NOLOCK) ON [sc].[scheduleid] = [rs].[scheduleid]
    WHERE
       1=1
       AND [rs].[reportaction] = 2 
       AND (@ReportName IS NULL OR [c].[Name] = @ReportName)
       AND (@DateFrom IS NULL OR [h].[snapshotdate] >= CAST(DATEADD(DAY, -1, GETDATE()) AS DATE))
    ORDER BY 
         [c].[name]
       , [h].[snapshotdate];
                        ;"

    $server = 'YourServerName'; 
    $dbs = 'MASTER';
    $dsn = "Data Source=$server; Initial Catalog=$dbs; Integrated Security=SSPI;"; 
    $cn = New-Object System.Data.SqlClient.SqlConnection($dsn); 

    #execute merge statement here with parameters
    $cn = New-Object System.Data.SqlClient.SqlConnection($dsn);
    $cn.Open(); 

    $cmd = $cn.CreateCommand();
    $cmd.CommandText = $sql
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $cmd
    $cmd.Connection = $cn
    $ds = New-Object System.Data.DataSet
    $SqlAdapter.Fill($ds)
    $cn.Close()
    $Result = $ds.Tables[0]

    Foreach ($item in $Result) 
    {
        #Write-Host $item.name

        $SnapshotUrl = $item.SnapshotUrl
        $ExportFileName = $item.ExportFileName
        (Invoke-WebRequest -Uri $SnapshotUrl -OutFile $ExportFileName -UseDefaultCredentials -TimeoutSec 240);
    }

https://learn.microsoft.com/en-us/sql/reporting-services/url-access-parameter-reference?view=sql-server-ver15