I have a tricky case for you, and I didn't find any similar issue anywhere (I must be bad at searching), so here it is...

I took a backup of the master database from a production server (SQL Server 2014 Std), let's call it SQLPRD, and I restored it, like any non-system database (so I thought), to a dev server (SQL Server 2014 Developer), let's call it SQLDEV.

In the restore statement, I changed the name of the destination database to be master_SQLPRD, and I changed the mdf and log file names to match the new name of the database, so they wouldn't overwrite the DEV master.

I extracted what I wanted from master_SQLPRD, so I dropped it. Then SSMS started throwing me errors as it couldn't find that master_SQLPRD database: now I can't list the databases on SQLDEV, I can't expand the SQL Agent node, etc. I restarted the database engine service, expecting it not to come back online... but it did.

Before I rebuild master (which I didn't backup - it's almost a throw-away instance), is there anything I could try to make master identify itself as master, not master_SQLPRD?

Thank you.

Edit: The error showing in the error log is-

Error: 18456, Severity: 14, State: 73. Login failed for user 'mydomain\my.login'. Reason: Failed to open the database 'master_SQLPRD' configured in the session recovery object while recovering the connection. [CLIENT: ]

  • Hmmm you've fouled something up by the sounds of it. Sounds like you've restored master db as a user database - not sure why, but doing this then dropping it wouldn't cause any issues on it's own. When you say you 'extracted what you want' from it, what exactly did you do?
    – Molenpad
    Mar 3 '17 at 16:37
  • There was a table in that database that I wanted to read data from - a user table, not a system one, and the data had gone from the live one.
    – Nelson 2.0
    Mar 3 '17 at 16:43
  • 2
    Check your startup parameters are pointed at the correct master database files - also are there any entries in the SQL error log at this time?
    – Molenpad
    Mar 3 '17 at 19:49
  • Check this thread here for some ideas. In your particular case, if you can't get to it via the GUI, open the SQL errorlog directly in Notepad from the appropriate log folder, I imagine that will tell you what is going on.
    – BradC
    Mar 3 '17 at 20:22
  • Thanks guys. I checked the startup parameters (it's actually the first thing I looked at), and they haven't changed (they point at files for the original master database) and the files are there (they were not renamed). I have edited the OP with the precise error I get in the log.
    – Nelson 2.0
    Mar 6 '17 at 10:48

Alright... I don't know what the underlying issue was, but closing SSMS (2016) and re-opening it seemed to fix it, as I don't have any error messages anymore.

Thank you all for your help and interest!

Your Answer

