January 13, 2017 at 3:47 pm
I have some reports that I have to move from 2008 to 2012. I managed to move the reports by using VISUAL STUDIO 2015 ( Worked well )
Question: How do I move the subscriptions from the 2008 server to 2012.
I am thinking there should be a nice T-SQL script that i can build. Any help please ....
BTW the reports between the servers have the same name and folder structure.
So a script can be made... Can you a help...
January 15, 2017 at 8:33 pm
Try the following:
- back up the SSRS encryption key on 2012 installation
- backup the SSRS 2012 databases (ReportServerDB and ReportServerTempdb)
- these are just in case something goes wrong later
- backup the SSRS encryption key in the 2008 installation
- backup the SSRS 2008 databases
- restore the SSRS 2008 database over the top of the SSRS 2012 databases (you may need to stop the SSRS 2012 service and SQL Agent before doing this)
- restore the backup of the SSRS 2008 encryption key on the SSRS 2012 installation
- start the SSRS 2012 services and SQL Agent if you stopped them
This should be all you need. SSRS should upgrade the SSRS 2008 database to be a SSRS 2012 database and create the subscription for you.
January 17, 2017 at 6:42 am
happycat59 - Sunday, January 15, 2017 8:33 PMTry the following:
- back up the SSRS encryption key on 2012 installation
- backup the SSRS 2012 databases (ReportServerDB and ReportServerTempdb)
- these are just in case something goes wrong later
- backup the SSRS encryption key in the 2008 installation
- backup the SSRS 2008 databases- restore the SSRS 2008 database over the top of the SSRS 2012 databases (you may need to stop the SSRS 2012 service and SQL Agent before doing this)
- restore the backup of the SSRS 2008 encryption key on the SSRS 2012 installation
- start the SSRS 2012 services and SQL Agent if you stopped themThis should be all you need. SSRS should upgrade the SSRS 2008 database to be a SSRS 2012 database and create the subscription for you.
You may also need to set the compatibility level of the restored database up to the SQL 2012 level right after the restore is successful.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 17, 2017 at 11:23 am
sgmunson - Tuesday, January 17, 2017 6:42 AMhappycat59 - Sunday, January 15, 2017 8:33 PMTry the following:
- back up the SSRS encryption key on 2012 installation
- backup the SSRS 2012 databases (ReportServerDB and ReportServerTempdb)
- these are just in case something goes wrong later
- backup the SSRS encryption key in the 2008 installation
- backup the SSRS 2008 databases- restore the SSRS 2008 database over the top of the SSRS 2012 databases (you may need to stop the SSRS 2012 service and SQL Agent before doing this)
- restore the backup of the SSRS 2008 encryption key on the SSRS 2012 installation
- start the SSRS 2012 services and SQL Agent if you stopped themThis should be all you need. SSRS should upgrade the SSRS 2008 database to be a SSRS 2012 database and create the subscription for you.
You may also need to set the compatibility level of the restored database up to the SQL 2012 level right after the restore is successful.
Unfortunately this will not work.
Why: I had made changes to reports ( Some tables were moved to certain schemas so the reports had to be changed as well )
Now if you are asking me to IMPORT the entire database, that means my new reports are going to get overwritten.
I only want the subscriptions transferred. There must be a way to do this via a script.
January 17, 2017 at 12:12 pm
Take a look at rs.exe
Although my preferred method would have been to restore a backup of the ReportServer database to the new server.
You can still do this, just save (or move them into source control - I can't recommend this enough) & reupload/deploy your modified rdl files after the restore.
Final and least favourite option would be to script moving the rows yourself - you'll need some way to map the reports between the old & new servers. Tables involved are dbo.Catalog, dbo.Schedule, dbo.Subscriptions, dbo.ReportSchedule
January 17, 2017 at 12:30 pm
Gazareth - Tuesday, January 17, 2017 12:12 PMTake a look at rs.exeAlthough my preferred method would have been to restore a backup of the ReportServer database to the new server.
You can still do this, just save (or move them into source control - I can't recommend this enough) & reupload/deploy your modified rdl files after the restore.Final and least favourite option would be to script moving the rows yourself - you'll need some way to map the reports between the old & new servers. Tables involved are dbo.Catalog, dbo.Schedule, dbo.Subscriptions, dbo.ReportSchedule
Cool!
If you could help me how to join the SCHEDULE table ( See my code below ) that would help me a lot.
I think I can then start the work on my own.
Select *
FROM
[Catalog] rpt
inner join Subscriptions s on (s.Report_OID = rpt.ItemID)
inner join ReportSchedule rs on (rs.SubscriptionID = s.SubscriptionID )
January 17, 2017 at 1:20 pm
mw112009 - Tuesday, January 17, 2017 12:30 PMGazareth - Tuesday, January 17, 2017 12:12 PMTake a look at rs.exeAlthough my preferred method would have been to restore a backup of the ReportServer database to the new server.
You can still do this, just save (or move them into source control - I can't recommend this enough) & reupload/deploy your modified rdl files after the restore.Final and least favourite option would be to script moving the rows yourself - you'll need some way to map the reports between the old & new servers. Tables involved are dbo.Catalog, dbo.Schedule, dbo.Subscriptions, dbo.ReportSchedule
Cool!
If you could help me how to join the SCHEDULE table ( See my code below ) that would help me a lot.
I think I can then start the work on my own.
Select *
FROM
[Catalog] rpt
inner join Subscriptions s on (s.Report_OID = rpt.ItemID)
inner join ReportSchedule rs on (rs.SubscriptionID = s.SubscriptionID )
It's in dbo.ReportSchedule
join dbo.Schedule sc on sc.ScheduleID = rs.ScheduleID
Can't remember if you need to get to Subscriptions via ReportSchedule rather than directly joining from Catalog. Try both & check the results.
The problem you've then got to get around is that ItemID, SubscriptionID and ScheduleID won't be the same on the old & new servers.
There may also be extra/removed columns in the tables in the different versions, be sure to check.
Good luck!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply