migrate report history to another server

All,
I'm looking into the ability to move a report and it's history from one
reporting server to another. Moving the reporting is no problem, but I am
not having any luck in finding a way to move the associated historical
reports. I have used RSScripter to move reports, but the history does not
migrate. I was hoping to migrate the report history programmatically, but
I'm not seeing anything that suggests it is possible short of moving the
entire Reporting Services database. Has anyone moved historical reports
between Reporting Service servers?
Thanks,
RobertRMac,
There is a way to do this, I have done it. It involves creating some DTS
Packages though. Here are the steps that I did and it worked great:
First thing you need to do is go into the Catalog Table in the ReportServer
database and find the report that you are working with (the one that has all
of the history). This report will have an 'ItemID'. You will need this
ItemID to use in queries in the DTS packages.
Once you have the ItemID you can run a query against the History Table to
find all of the SnapShotDataID's, like this:
select * from dbo.history
where reportid = '{B22FBC16-5E61-4B40-903E-0E04E689DE60}'
Create a DTS Package, using this query, to export the history items from the
old server to the new one. Your history records will now be in the new
server History table.
Next, create another DTS Package, using a query similar to this one:
select * from dbo.SnapShotData
where snapshotdataid in
(select snapshotdataid from dbo.history
where reportid = '{B22FBC16-5E61-4B40-903E-0E04E689DE60}')
With this DTS package you are going to export out all of the SnapShotData
Table information from the old server to the new one.
Next DTS package will use a query similar to this one:
select * from dbo.ChunkData
where snapshotdataid in
(select snapshotdataid from dbo.history
where reportid = '{B22FBC16-5E61-4B40-903E-0E04E689DE60}')
The ChunkData table is the table that actually contains the image files (or
snapshots) of the reports. Once you have the History table, SnapShotData
table and ChunkData table information moved over. You will need to run an
update query on the new server like this:
update dbo.History
set reportid = '(your new itemid from the new server catalog table)'
where reportid = '(your old itemid from the old server}'
You will need the new itemid for this previous query. Once you have
deployed the report to the new server, just go out to the Catalog table and
find the new item id. Use that in the update query above.
It sounds a lot harder than it really is. But, I have done it and this does
work.
"RMac" wrote:
> All,
> I'm looking into the ability to move a report and it's history from one
> reporting server to another. Moving the reporting is no problem, but I am
> not having any luck in finding a way to move the associated historical
> reports. I have used RSScripter to move reports, but the history does not
> migrate. I was hoping to migrate the report history programmatically, but
> I'm not seeing anything that suggests it is possible short of moving the
> entire Reporting Services database. Has anyone moved historical reports
> between Reporting Service servers?
> Thanks,
> Robert|||SandiB, this works like a champ. Thanks much!
"SandiB" wrote:
> RMac,
> There is a way to do this, I have done it. It involves creating some DTS
> Packages though. Here are the steps that I did and it worked great:
> First thing you need to do is go into the Catalog Table in the ReportServer
> database and find the report that you are working with (the one that has all
> of the history). This report will have an 'ItemID'. You will need this
> ItemID to use in queries in the DTS packages.
> Once you have the ItemID you can run a query against the History Table to
> find all of the SnapShotDataID's, like this:
> select * from dbo.history
> where reportid = '{B22FBC16-5E61-4B40-903E-0E04E689DE60}'
> Create a DTS Package, using this query, to export the history items from the
> old server to the new one. Your history records will now be in the new
> server History table.
> Next, create another DTS Package, using a query similar to this one:
> select * from dbo.SnapShotData
> where snapshotdataid in
> (select snapshotdataid from dbo.history
> where reportid = '{B22FBC16-5E61-4B40-903E-0E04E689DE60}')
> With this DTS package you are going to export out all of the SnapShotData
> Table information from the old server to the new one.
> Next DTS package will use a query similar to this one:
> select * from dbo.ChunkData
> where snapshotdataid in
> (select snapshotdataid from dbo.history
> where reportid = '{B22FBC16-5E61-4B40-903E-0E04E689DE60}')
> The ChunkData table is the table that actually contains the image files (or
> snapshots) of the reports. Once you have the History table, SnapShotData
> table and ChunkData table information moved over. You will need to run an
> update query on the new server like this:
> update dbo.History
> set reportid = '(your new itemid from the new server catalog table)'
> where reportid = '(your old itemid from the old server}'
> You will need the new itemid for this previous query. Once you have
> deployed the report to the new server, just go out to the Catalog table and
> find the new item id. Use that in the update query above.
> It sounds a lot harder than it really is. But, I have done it and this does
> work.
>
> "RMac" wrote:
> > All,
> > I'm looking into the ability to move a report and it's history from one
> > reporting server to another. Moving the reporting is no problem, but I am
> > not having any luck in finding a way to move the associated historical
> > reports. I have used RSScripter to move reports, but the history does not
> > migrate. I was hoping to migrate the report history programmatically, but
> > I'm not seeing anything that suggests it is possible short of moving the
> > entire Reporting Services database. Has anyone moved historical reports
> > between Reporting Service servers?
> > Thanks,
> > Robert