I have a SQL Server (on Windows XP) where I have published reports using Report Manager and a bunch of databases. Within the SQL Server, I have jobs scheduled to run updates on tables, etc. I came in this morning to find that the root drive died. My drive is partitioned such that my databases aren't on the root drive - so I was able to salvage the databases.

I was able to use a prior hard drive (from about a month ago - we upgraded) and load current databases to the old drive. I'm trying to bring the old drive up to speed with today's data, jobs, files... .

My databases seemed to load fine.

What I can't do is view any of the reports that use Report Manager. A few questions:

  1. Are the jobs that run through SQL Server stored in a different directory than the databases?
  2. Any idea of what's happening with accessing report manager reports? Usually a client machine could access the reports using IE. - also can't connect using an ODBC connection with Access. (the network plug is connected).
  3. Am I missing any other pieces that may need to be restored to the current view besides the databases?

Any suggestions are appreciated.


The first thing I would do is gracefully stop your SQL Server services -- Then make a copy of all the ldf/mdf files on the server. Also find the location of the backup files -- they should be name something like MyDBname_backup_200907220000.bak and rename the extension to something like .hold. That way they aren't wiped out by a maint plan if you have to go to restore.

After that -- only bring back the SQL Server Service -- and not the Server Agent.

Check if the database(s) are still marked suspect. If they are

EXEC sp_resetstatus 'your_db_name';
DBCC DBRECOVER ('your_db_name')

If that doesn't work -- please post back and we can see what else we can do.

  • I'm not entirely sure where your answer fits in to the question. The databases are not suspect, in fact, the user databases were not affected. The user made no mention of what his backup plan is so I don't understand why you think his backups would be named like that. (What if he uses Tivoli for example?) It looks more like something is wrong in his SSRS setup. – Reaces Sep 1 '15 at 11:11

I'm going to guess, from your questions, that you might have lost master and msdb. Otherwise, it's kind of hard to tell from your question, but...

  1. Jobs are stored in MSDB. If you have a backup but not the files for MSDB, you can restore it as old-msdb and might be able to find the files in the msdb.dbo.sysjob* tables.
  2. Do the ODBC connections refer to the correct machine name? Are the network settings on the new machine name correct in SQL Server Configuration Manager? Also--dumb question, but I have to ask--report manager/SSRS is installed, right? Is the firewall allowing the connection? It could also be permissions, especially if you lost master.

If you lost master but recreated the logins, your SQL logins might be "orphaned." You can tell if you have orphaned users by running

USE <database_name>;
sp_change_users_login @Action='Report';

If you do have orphaned users, you can fix them with

USE <database_name>;
sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', 

(Code from MSDN.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.