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 https://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.

Comments

22 responses to “How to Rename Your existing Report Server Database”

  1. SQL-Articles » How to Rename Your existing Report Server Database…

    Thank you for submitting this cool story – Trackback from DotNetShoutout…

  2. Julie avatar
    Julie

    Is the script you used available on the website? I’m not seeing it.

    1. VidhyaSagar avatar
      VidhyaSagar

      No Julie I haven’t attached the script. You can script your existing report server db.

    2. Tarun Kumar avatar
      Tarun Kumar

      We just right click on DB –>select “Generate scripts” –>selected database objects (Sps,Views,Function etc.) –>then from property selected “drop and create” script –> Generated scripts for all objects( means All SPs,views etc. )

      1. VidhyaSagar avatar

        Thanks, at that time the version of SSMS doesn’t supported. However when it became standalone product, it has more features like the one you described.

  3. Joe avatar
    Joe

    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.

  4. Szymon Wojcik avatar

    Hi,

    I only modified the trigger Schedule_UpdateExpiration on Schedule table in ReportServer DB and it worked – it was in SSRS 2008 R2.

  5. simon avatar

    Thanks guys, saved me a whole load of hassle when donig a DR test and restoring to a named instance!! Had to change the 83 rows, views and functions.

  6. Alex avatar
    Alex

    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. VidhyaSagar avatar
      VidhyaSagar

      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

  7. […] How to Rename Your existing Report Server Database […]

  8. Frank K avatar
    Frank K

    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.

  9. Angela But avatar
    Angela But

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

  10. Mohan avatar
    Mohan

    your article has helped me to understand the migration issues.
    Thanks for your good article.

  11. […] the Report Server was another problem I needed to solve. This post helped me lot and saved me some time. Migrating TFS to a new server, specially when using a […]

  12. Vivek avatar
    Vivek

    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

  13. Alexander Zoutenbier avatar

    Perfect article! Thanks for the support

  14. paul crompton avatar

    I found this article through google search after going through a process of renaming a report server database and finding afterwards I was getting the following error when I tried to delete some reports:
    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 ‘HDM_ReportServer$HDMSTempDB.dbo.SnapshotData’

    Your article proved to be very helpful and I was able to reset the internal stored procedures and triggers in the renamed report server db and subsequently successfully delete the reports I wanted to delete

  15. mike avatar
    mike

    Thanks for the tip to generate script and use find and replace function 🙂
    In my case i had to recreate 3 other objects

    views
    ExtendedDataSets
    ExtendedDataSources

    function
    ExtendedCatalog

    cheers

    1. VidhyaSagar avatar

      Thanks Mike for sharing your input.

  16. Thomas avatar
    Thomas

    Big thanks!
    Had to change the SP’s, 1 function, 2 views and 1 trigger.
    Your search-script was very helpful also.

    1. VidhyaSagar avatar

      Glad I was able to help you

Leave a Reply to VidhyaSagar Cancel reply

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