How to Rename Your existing Report Server Database

Have you ever faced in a scenario where you are required to rename existing database. While dealing with one my client I have installed reporting server and during RS configuration I’ve chosen the default settings while creating the database so it created the reporting server dbname as ReportServer and ReportServerTempdb and RS working fine.Now here is the problem, as per the company policy the dbname should be in format ReportServer_Appname & ReportServer_AppnameTempdb (in my case app name is clix). This looks easy to me so I’ve just stopped reporting server and renamed both the databases as per their requirement and started reporting service. Now went to reporting server configuration tool (Check my article http://sql-articles.com/articles/dba/configuring-sql-server-reporting-services/ for configuring reporting server) and selected the new databases as shown below Wonderful no errors and RS is working fine when I browse the homepage so I handed over the server to client.

The actual problem lies here, now my client is running some rdl files to get the report however he is facing the error below while running the reports. I then jumped and tried to reconfigured RS with new db however none worked. So I just started digging it, later I found that all the procedures in the Reporting server database is now pointing to the old RS tempdb database as shown below

An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. (rsReportServerDatabaseError) Get Online Help Invalid object name ‘ReportServerTempDB.dbo.TempCatalog‘. Could not use view or function ‘ExtendedCatalog’ because of binding errors

I felt that some objects are pointing to the old reportserver tempdb database so I gave a quick search on syscomments table on Reporting Server db (here it’s ReportingServer_Clix), now I found the culprit. It returned around 83 rows that means 83 procedures are pointing to the old report server tempdb as shown below

USE ReportServer_Clix
GO
SELECT OBJECT_NAME(id) 'Name' FROM syscomments
WHERE text LIKE '%ReportServerTempDB%'
ORDER BY Name

To resolve this, I have script all the procedures with drop existing parameter on report server database to a new query window. Now I did a search and replace the old report server tempdb name to new report server tempdb and recreated all the objects again as shown below.


235 occurences are there in the objects all of them is now replaced. Now I gone to reporting server and executed the rdl file, it worked perfectly fine as shown below

Thus to consolidate for renaming reporting server database , follow the steps below it will help you out.

  • First and foremost thing, backup your existing reporting server databases
  • Stop SQL Server reporting Services
  • Now rename your report server database as required
  • Now script stored procedures and triggers that exist on report server to a new file or query window. (Script with drop and create functionality)
  • Use search and replace function , search for old report server tempdb database and replace with new report server tempdb database
  • Now execute the modified script
  • Start SQL Server reporting services
  • Go to Reporting server configuration tool and go to database section
  • From the drop down list select the new database (which is renamed) and click on Ok

That’s it your part is done. Reporting server databases are now renamed as per your requirement. I have tested the same in Denali CTP3, unfortunately this is the only version I have in my laptop.

15 thoughts on “How to Rename Your existing Report Server Database”

  1. Pingback: DotNetShoutout
  2. Note that in my SQL2008R2 installation, I also had to script out changes to 2 views and 1 table-valued user function for this to work.

  3. I am trying to point the SSRS to anohter DB / DBTTEMP and I am getting this error message:

    The report server installation is not initialized. (rsReportServerNotActivated)

    does anyone happen to know, how can approach and fix this issue.

    thanks

    1. Probably Encryption might be set, so go to reporting server configuration wizard and then click on Encryption tab. Once you are there take a backup of the existing key and then delete it. Now you will be able to point it to new db

  4. I had this issue after completing a native migration from a 2008R2 box to another.

    This worked for me. Only thing I have to note is that the DROP and CREATE selection is separate in 2008R2. Otherwise once the script was generated and the old db name replaced with the new db name and ran, the SSRS reports worked as expected.

    Thank you.

  5. I had this same problem in SSRS 2012 R2. Modifying the ExtendedCatalog function to refer to the new database name fixed this for me.

  6. Thanks Vidhya,

    Your article has saved my day . Faced similar issue while migration Reporting Server SQL Server 2008 Named Instance to QSL Server 2012 Default Instance .

    Vivek

Leave a Reply

Your email address will not be published. Required fields are marked *

*