如何将SSRS快照数据从一台服务器迁移至另一台服务器？

你提到已经能用PowerShell导出SSRS快照，但缺少导入方法，结合你提供的快照存储结构SQL，以下是直接针对SSRS数据库的导入方案（注：直接操作SSRS数据库属于非官方操作，执行前务必备份目标服务器的SSRS数据库）：

核心逻辑说明 #

SSRS快照数据分散在SnapshotData、History、SegmentedChunk、ChunkSegmentMapping、Segment这几张关联表中，导入时需要按表的依赖顺序处理，同时确保目标服务器已存在对应报表（否则History表的ReportID无法匹配）。

步骤1：确认源数据范围 #

你提供的SQL可用于筛选需要迁移的快照数据，先在源服务器执行确认：

-- 查看指定报表的快照关联数据
SELECT * 
FROM [dbo].[SnapshotData] SD
JOIN dbo.History H ON h.SnapshotDataID = sd.SnapshotDataID
JOIN dbo.Catalog C ON c.ItemID = h.ReportID
JOIN dbo.SegmentedChunk SC ON SC.SnapshotDataId = SD.SnapshotDataID
JOIN dbo.ChunkSegmentMapping CSM ON csm.ChunkId = sc.ChunkId
JOIN dbo.Segment S ON s.SegmentId = csm.SegmentId
WHERE C.Path = '/你的报表路径' -- 替换为实际报表路径
ORDER BY c.Path, sd.CreatedDate DESC

步骤2：目标服务器导入操作 #

2.1 映射报表ID #

先获取目标服务器对应报表的ItemID，后续替换History表中的ReportID：

DECLARE @TargetReportID UNIQUEIDENTIFIER = (SELECT ItemID FROM [dbo].[Catalog] WHERE Path = '/目标服务器报表路径');

2.2 按依赖顺序导入表数据 #

导入时需开启IDENTITY_INSERT以保留源数据的主键ID，避免冲突：

导入SnapshotData表

SET IDENTITY_INSERT [dbo].[SnapshotData] ON;
GO
INSERT INTO [dbo].[SnapshotData] (SnapshotDataID, CreatedDate, ExpirationDate, Size, Status, ExecutionID)
SELECT SnapshotDataID, CreatedDate, ExpirationDate, Size, Status, ExecutionID
FROM [源服务器IP].[源SSRS数据库名].[dbo].[SnapshotData] SD
JOIN [源服务器IP].[源SSRS数据库名].[dbo].[History] H ON H.SnapshotDataID = SD.SnapshotDataID
JOIN [源服务器IP].[源SSRS数据库名].[dbo].[Catalog] C ON C.ItemID = H.ReportID
WHERE C.Path = '/源服务器报表路径';
GO
SET IDENTITY_INSERT [dbo].[SnapshotData] OFF;
GO

导入History表

SET IDENTITY_INSERT [dbo].[History] ON;
GO
INSERT INTO [dbo].[History] (HistoryID, ReportID, SnapshotDataID, CreatedDate, ExpirationDate, Parameters)
SELECT HistoryID, @TargetReportID, SnapshotDataID, CreatedDate, ExpirationDate, Parameters
FROM [源服务器IP].[源SSRS数据库名].[dbo].[History] H
JOIN [源服务器IP].[源SSRS数据库名].[dbo].[Catalog] C ON C.ItemID = H.ReportID
WHERE C.Path = '/源服务器报表路径';
GO
SET IDENTITY_INSERT [dbo].[History] OFF;
GO

导入Segment表（Blob快照数据）

SET IDENTITY_INSERT [dbo].[Segment] ON;
GO
INSERT INTO [dbo].[Segment] (SegmentId, Data, CompressionType)
SELECT S.SegmentId, S.Data, S.CompressionType
FROM [源服务器IP].[源SSRS数据库名].[dbo].[Segment] S
JOIN [源服务器IP].[源SSRS数据库名].[dbo].[ChunkSegmentMapping] CSM ON CSM.SegmentId = S.SegmentId
JOIN [源服务器IP].[源SSRS数据库名].[dbo].[SegmentedChunk] SC ON SC.ChunkId = CSM.ChunkId
JOIN [源服务器IP].[源SSRS数据库名].[dbo].[SnapshotData] SD ON SD.SnapshotDataID = SC.SnapshotDataId
JOIN [源服务器IP].[源SSRS数据库名].[dbo].[History] H ON H.SnapshotDataID = SD.SnapshotDataID
JOIN [源服务器IP].[源SSRS数据库名].[dbo].[Catalog] C ON C.ItemID = H.ReportID
WHERE C.Path = '/源服务器报表路径';
GO
SET IDENTITY_INSERT [dbo].[Segment] OFF;
GO

导入SegmentedChunk表

SET IDENTITY_INSERT [dbo].[SegmentedChunk] ON;
GO
INSERT INTO [dbo].[SegmentedChunk] (ChunkId, SnapshotDataId, Sequence)
SELECT SC.ChunkId, SC.SnapshotDataId, SC.Sequence
FROM [源服务器IP].[源SSRS数据库名].[dbo].[SegmentedChunk] SC
JOIN [源服务器IP].[源SSRS数据库名].[dbo].[SnapshotData] SD ON SD.SnapshotDataID = SC.SnapshotDataId
JOIN [源服务器IP].[源SSRS数据库名].[dbo].[History] H ON H.SnapshotDataID = SD.SnapshotDataID
JOIN [源服务器IP].[源SSRS数据库名].[dbo].[Catalog] C ON C.ItemID = H.ReportID
WHERE C.Path = '/源服务器报表路径';
GO
SET IDENTITY_INSERT [dbo].[SegmentedChunk] OFF;
GO

导入ChunkSegmentMapping表

SET IDENTITY_INSERT [dbo].[ChunkSegmentMapping] ON;
GO
INSERT INTO [dbo].[ChunkSegmentMapping] (ChunkSegmentMappingId, ChunkId, SegmentId, Sequence)
SELECT CSM.ChunkSegmentMappingId, CSM.ChunkId, CSM.SegmentId, CSM.Sequence
FROM [源服务器IP].[源SSRS数据库名].[dbo].[ChunkSegmentMapping] CSM
JOIN [源服务器IP].[源SSRS数据库名].[dbo].[SegmentedChunk] SC ON SC.ChunkId = CSM.ChunkId
JOIN [源服务器IP].[源SSRS数据库名].[dbo].[SnapshotData] SD ON SD.SnapshotDataID = SC.SnapshotDataId
JOIN [源服务器IP].[源SSRS数据库名].[dbo].[History] H ON H.SnapshotDataID = SD.SnapshotDataID
JOIN [源服务器IP].[源SSRS数据库名].[dbo].[Catalog] C ON C.ItemID = H.ReportID
WHERE C.Path = '/源服务器报表路径';
GO
SET IDENTITY_INSERT [dbo].[ChunkSegmentMapping] OFF;
GO

验证迁移结果 #

导入完成后，在目标SSRS报表管理器中查看对应报表的历史快照，确认能正常加载和显示。

内容的提问来源于stack exchange，提问作者Gurthang99