转移订阅报告服务

Question

您能否推荐将订阅从一个报表服务传输(或复制)到不同服务器中的另一个报表服务的最佳且简单的方法？

Answer 1

有多少订阅？

如果数量较少，最简单的方法是在另一台服务器上手动重新创建它们。

如果我们谈论的是相当多的数据，那么有一个数据库报告服务用来存储订阅数据，我相信这个数据库叫做dbo.Subscriptions。我建议先去那里看看，看看你是否能看到订阅。

否则，如果您希望传输整个报表服务器数据库(包括计划)，则可能需要使用以下链接：

MSDN Moving the Report Server Databases to Another Computer

Answer 2

基于@S.Juarez的回答，这个脚本修复了他的bug，这个bug破坏了参数(从而阻止了订阅)，还跨相关的时间表和时间表用户记录进行传输。它在源和目标之间维护相同的GUID。

使用此脚本的起点是在传输完报表之后(例如，使用工具ReportSync)，并且在目标服务器上的所有报表文件夹上手动配置了安全性。对于源服务器上而不是目标服务器上存在UserName的情况，您还需要确定要将订阅与目标服务器上的哪个用户记录相关联。(当您决定不在目标上重新创建用户时，或者由于此人不再是域中的有效帐户而无法执行此操作时，可能会发生这种情况，即，他们已离开您的组织)。

在开始之前，我建议您对源和目标ReportServer数据库运行这个小脚本，并保存结果。另外，将数据库作为一个整体进行完整备份。这些步骤使您能够回滚小更改和大更改。

SELECT u.UserName, c.Path, Parameters, s.ExtensionSettings, s.Report_OID, SubscriptionID, u.UserID
FROM dbo.[Subscriptions] s
JOIN users u 
on s.OwnerID = u.UserID
JOIN catalog c
on c.ItemID = s.Report_OID

下一个脚本的第一部分将传输订阅，然后是计划，然后是报表、订阅和计划之间的链接记录。您需要输入目标服务器和源服务器的名称、默认用户的名称(必须已经存在于目标用户表中)，然后在源服务器上执行此操作。

DECLARE @Default_User varchar(50) 
SELECT @Default_User = UserID FROM [SourceServer].ReportServer.dbo.Users WHERE UserName = '[DOMAIN\YourDefaultUserNameGoesHere]'

INSERT INTO [TargetServer].ReportServer.dbo.Subscriptions(
    SubscriptionID, OwnerID, Report_OID,  Locale, InactiveFlags, ExtensionSettings, ModifiedByID, ModifiedDate, 
    [Description], LastStatus, EventType, MatchData, LastRunTime, [Parameters], DataSettings, DeliveryExtension, Version
    )
SELECT 
    --cSource.Path,
    --uSource.UserName,
    SubscriptionID,
    --u.UserName,
    --LastStatus,
    COALESCE(uTarget.UserID, @Default_User) AS OwnerID,
    cTarget.ItemID,
    Locale, InactiveFlags, ExtensionSettings,
    @Default_User AS ModifiedByID,
     GETDATE(),
    sSource.[Description], LastStatus, EventType, MatchData, LastRunTime, [Parameters], DataSettings, DeliveryExtension, Version

FROM [SourceServer].ReportServer.dbo.Subscriptions sSource
    LEFT JOIN [SourceServer].ReportServer.dbo.Catalog cSource ON cSource.ItemId = sSource.Report_OID
    LEFT JOIN [SourceServer].ReportServer.dbo.Users uSource ON sSource.OwnerID = uSource.UserID
    LEFT JOIN [TargetServer].ReportServer.dbo.Catalog cTarget ON cTarget.Path = cSource.Path
    LEFT JOIN [TargetServer].ReportServer.dbo.Users uTarget ON uTarget.UserName = uSource.UserName
WHERE sSource.SubscriptionID NOT IN 
(
SELECT SubscriptionID FROM [TargetServer].ReportServer.dbo.Subscriptions
)


INSERT INTO [TargetServer].ReportServer.dbo.Schedule
(
ScheduleID, Name, StartDate, Flags, NextRunTime, LastRunTime, EndDate, RecurrenceType, MinutesInterval, DaysInterval, WeeksInterval, DaysOfWeek, DaysOfMonth, [Month], MonthlyWeek, State, LastRunStatus, ScheduledRunTimeout, EventType, EventData, Type, ConsistancyCheck, Path, CreatedById
)
SELECT
ScheduleID, Name, StartDate, Flags, NextRunTime, LastRunTime, EndDate, RecurrenceType, MinutesInterval, DaysInterval, WeeksInterval, DaysOfWeek, DaysOfMonth, [Month], MonthlyWeek, State, LastRunStatus, ScheduledRunTimeout, EventType, EventData, Type, ConsistancyCheck, Path, 
COALESCE(uTarget.UserID, @Default_User) AS CreatedById
FROM [SourceServer].ReportServer.dbo.Schedule s
INNER JOIN [SourceServer].ReportServer.dbo.Users uSource
ON s.CreatedById = uSource.UserID
LEFT JOIN [TargetServer].ReportServer.dbo.Users uTarget
ON uSource.UserName = uTarget.UserName
WHERE ScheduleID NOT IN (SELECT ScheduleID FROM [TargetServer].ReportServer.dbo.Schedule)


INSERT INTO [TargetServer].ReportServer.dbo.ReportSchedule
(
ScheduleID, ReportID, SubscriptionID, ReportAction
)
SELECT
    rsSource.ScheduleID, cTarget.ItemID, rsSource.SubscriptionID, rsSource.ReportAction
FROM [SourceServer].ReportServer.dbo.ReportSchedule rsSource
INNER JOIN [TargetServer].ReportServer.dbo.Schedule sTarget
ON rsSource.ScheduleID = sTarget.ScheduleID
INNER JOIN [SourceServer].ReportServer.dbo.Catalog cSource
On cSource.ItemID = rsSource.ReportID
INNER JOIN [TargetServer].ReportServer.dbo.Catalog cTarget
ON cSource.Path = cTarget.Path
LEFT JOIN [TargetServer].ReportServer.dbo.ReportSchedule rsTarget
ON  rsSource.ScheduleID = rsTarget.ScheduleID
AND rsSource.ReportID = rsTarget.ReportID
AND rsSource.SubscriptionID = rsTarget.SubscriptionID
WHERE rsTarget.ReportID IS NULL

要测试您的迁移是否正常工作，您可以在目标服务器上执行如下语句。GUID应该是Subscriptions表中的SubscriptionID，理想情况下用于将要发送到收件箱的内容。

exec [ReportServer].dbo.AddEvent @EventType='TimedSubscription', @EventData='cb38a708-7735-4b5a-8ff3-e03ee1b18edb'

如果工作正常，您应该会在大约20秒内收到一封电子邮件。如果失败，我发现查找故障排除信息的最佳位置是SSRS日志文件described here。

Answer 3

这是我们用来将订阅从2008 SSRS复制到2012 SSRS服务器的东西。您需要提前正确设置数据源。

INSERT INTO Mercury.ReportServer.dbo.Subscriptions(SubscriptionID, OwnerID, Report_OID,  Locale, InactiveFlags, ExtensionSettings, ModifiedByID, ModifiedDate, Description, LastStatus, EventType, MatchData, LastRunTime, Parameters, DataSettings, DeliveryExtension, Version)
SELECT 
    --Path,
    SubscriptionID
    ,(SELECT UserID FROM <Destination Linked Server>.ReportServer.dbo.Users WHERE UserName = '<User from DB>')  OwnerID
    ,(select ItemId from <Destination Linked Server>.ReportServer.dbo.Catalog mCatalog where mCatalog.Path = Catalog.Path )Report_OID
    ,Locale, InactiveFlags, ExtensionSettings
    ,(SELECT UserID FROM <Destination Linked Server>.ReportServer.dbo.Users WHERE UserName = 'User from DB') ModifiedByID
    , GETDATE()
    ,Sub.Description, LastStatus, EventType, MatchData, LastRunTime, Parameter, DataSettings, DeliveryExtension, Version

FROM ReportServer..Subscriptions Sub
    LEFT JOIN ReportServer.dbo.Catalog ON Catalog.ItemId = Sub.Report_OID
WHERE Path NOT IN 
  (
    SELECT Path
    FROM <Destination Linked Server>.ReportServer.dbo.Subscriptions
      LEFT JOIN <Destination Linked Server>.ReportServer.dbo.Catalog ON Catalog.ItemId = Subscriptions.Report_OID
  )
--AND
--  PATH LIKE '...'