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.