SSRS – Do you back up ReportServerTempDB ??

For one reason or another one of our Dev SSRS ( 2005 ) Servers did an unexpected restart and came back up with the ReportServerTempdb database marked as suspect.

Interesting! There are no backups taken on this box but I figured anything TempDB would just recreate if I restarted SQL Server. Yeah I know I should know this but I suppose it shows just how resilient and reliable SQL Server is – I run SSRS on laptops and test servers at home and none of these systems get 100% clean shutdowns.

Anyway I soon discovered that ReportServerTempDB doesn’t get recreated – a quick BING brought up a couple of forum posts which explained you should drop the database, create a new database, put back the role, run a setup script to recreate the objects and reset the permissions.

First up no script anywhere on my install DVD but there were a couple of copies in a ServicePack and CU. Alas having done this I kept getting messages about a missing column.  After some deliberation I identified the table with the missing column, well without the column to be absolutely precise and the table was different between my prod SSRS server and the rebuilt database.

So I just scripted out the table and permissions and ran the script against the Dev box and I was back in business.

Other options on my list not tried were a backup and restore from another server and a complete db generate script from another server.

So there you have it – make sure you backup ReportServerTempDB, you may need a backup one day. Should I have known this ? Absolutely , am I the only one ? possibly not.

Published 04 September 2010 21:56 by GrumpyOldDBA
Filed under:

Comments

# re: SSRS – Do you back up ReportServerTempDB ??

06 September 2010 13:43 by DavidWimbush

That makes at least two of us who were assuming that! Amazing really, my assumption wasn't based on any facts. I vaguely remember reading that it was only some sort of temporary working data but that's not the same thing as tempdb. Thanks very much for your post. I've added this database to my backup job now.