Migrate reporting databases

Question

Migrate reporting databases

sqlfriend

SSC Guru

Points: 52794
More actions
March 1, 2012 at 12:18 pm

#253984

I 'm migrating a sql server 2005 reportServer and reportServertempdb to a SQl server 2008R2 box.
What is the beter route for me?
1. On the new box, install SQL server 2008 R2 database engine and reporting service, but choose Install but do not configure option, so there is no reportserver database created, then later restore databases from old 2005 server
Or
2.
On the new box, install SQL server 2008 R2 database engine and reporting service using the default installation,- that is install the newest reportserver db and reportServertempdb comes with the installation , then later restore database to overwrite the existing databases.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Perry Whittle SSC Guru Points: 234065 More actions · Answer 1

sqlfriends (3/1/2012)
I 'm migrating a sql server 2005 reportServer and reportServertempdb to a SQl server 2008R2 box.
What is the beter route for me?
1. On the new box, install SQL server 2008 R2 database engine and reporting service, but choose Install but do not configure option, so there is no reportserver database created, then later restore databases from old 2005 server
Or
2.
On the new box, install SQL server 2008 R2 database engine and reporting service using the default installation,- that is install the newest reportserver db and reportServertempdb comes with the installation , then later restore database to overwrite the existing databases.

You cannot just restore the SSRS2005 databases.

Install a new instance of SSRS2008 and then migrate the reports, etc, from the SSRS2005 instance to the SSRS2008 instance. You can use the Reporting Services Scripter utility or redeploy from your source code projects which i'm sure you have, right!!

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

sqlfriend SSC Guru Points: 52794 More actions · Answer 2

Perry Whittle (3/3/2012)
sqlfriends (3/1/2012)
I 'm migrating a sql server 2005 reportServer and reportServertempdb to a SQl server 2008R2 box.
What is the beter route for me?
1. On the new box, install SQL server 2008 R2 database engine and reporting service, but choose Install but do not configure option, so there is no reportserver database created, then later restore databases from old 2005 server
Or
2.
On the new box, install SQL server 2008 R2 database engine and reporting service using the default installation,- that is install the newest reportserver db and reportServertempdb comes with the installation , then later restore database to overwrite the existing databases.
You cannot just restore the SSRS2005 databases.
Install a new instance of SSRS2008 and then migrate the reports, etc, from the SSRS2005 instance to the SSRS2008 instance. You can use the Reporting Services Scripter utility or redeploy from your source code projects which i'm sure you have, right!!

Thanks, but why I cannot just restore the database from SSRS2005, after it restored , I think it will antomatically upgraded to 2008r2 database, correct, I tried above option 2, it looks correct to me.

So just wonder why you said "You cannot just restore the SSRS2005 databases'

Why is that?

Thanks

Perry Whittle SSC Guru Points: 234065 More actions · Answer 3

Because attaching\restoring the reporting databases to the 2008 R2 instance will upgrade the database structure but the internal SSRS structures will not. It is a known issue with SSRS upgrades, the best way is to deploy a new instance and re deploy or migrate the objects across.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Ant-Green SSC Guru Points: 113566 More actions · Answer 4

Perry, while I totally agree with you with migrating should be a full redeployment from the base solutions again or by using tools to script out and redeploy from an existing site to the new site, there seems to be a bit of contradiction from technet as they do say you can just simply backup and restore the database

http://technet.microsoft.com/en-us/library/ms143724%28v=sql.105%29.aspx

sqlfriend SSC Guru Points: 52794 More actions · Answer 5

Yes, that is the article I read too.

By the way what is the best reporting script utility for DBAs?

Thanks

Ant-Green SSC Guru Points: 113566 More actions · Answer 6

there is a tool called RSScriptor which I've used in the past, start putting in a change versioning / source control mechanisum where you store the solutions and rdl files so you can easily deploy them back. Something like TFS.