transfer subscription reporting service

Question

Would you recommend the best and easy way to transfer (or copy) the subscriptions from one reporting service to another reporting service in different server?

Answer 1

Building on the answer from @S.Juarez, this script fixes his bug that breaks the parameters (and thus prevents subscriptions from working), and also transfers across the related Schedules and Schedule User records. It maintains the same GUIDs across both source and target.

The starting point for using this script is after you have already transferred the reports (for example using the tool ReportSync), and you have manually configured the security on all your report folders on the target server. You also need to decide which User record on the target server to associate Subscriptions with, for those cases where the UserName exists on the source server and not on the target server. (This can happen in situations where you decide not to recreate Users on the target, or when you cannot do so because that person no longer a valid account on the domain - i.e. they have left your organisation).

Before you begin, I'd recommend running this small script against both your source and target ReportServer databases and saving the results. Also, take full backups of the databases as whole. These steps give you the ability to rollback both small and large changes.

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

The first part of this next script will transfer the Subscriptions, followed by the Schedules and then the linking records between the Reports, Subscriptions and Schedules. You need to put in the name of your target and source servers, the name of your default user (must already exist in the target Users table) and then execute this on the source server.

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

To test if your migration has worked properly, you can execute a statement like this against the target server. The GUID should be a SubscriptionID from the Subscriptions table, ideally for something that will be delivered to your inbox.

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

If it works, then you should receive an email within ~20 seconds. If it fails, I found that the best place to look for troubleshooting information was in the SSRS log files, described here.