Seeing a database in any state other than “online” makes me a little nervous. For one thing, it’s one of those things I don’t see often, so I have to rub a few brain cells together to remember what I did the last time. For another, there’s no guarantee that you’ll be able to get the database back online. That’s scary.
But one of our clients had three SQL Server databases in “recovery pending” state this week. Trying to bring the databases online produces these errors:
"Error: 5173, Severity: 16, State: 1. One or more files do not match the
primary file of the database. If you are attempting to attach a database,
retry the operation with the correct files. If this is an existing database,
the file may be corrupted and should be restored from backup."
"Error: 928, Severity: 20, State: 1: During upgrade, database raised exception
945, severity 14, state 2, address (). Use the exception number to determine
The 928 error was a typical Microsoft red herring having to do with database compatibility levels and permissions. However, trying to bring the database online added a little meat to the 5173 error’s bones:
"Database [dbname] cannot be opened due to inaccessible files or insufficient
memory or disk space. See the SQL Server errorlog for details."
"Log file [log name] does not match the primary file. It may be from
a different database or the log may have been rebuilt previously."
I checked file permissions, memory and disk space. Everthing was kosher there. I knew that would be the case because life just isn’t that easy.
Which left the log files. These databases (along with a dozen others in the instance) had been involved in a SAN crash. They were migrated to a new disk, but performance was awful — data warehouse processes were taking 10 times longer to run than they had previously. So the client built a new drive for the logs, migrated the logs, changed the drive letter, and altered the file paths stored in the master database. Plenty of opportunities there to corrupt the log.
I had no backups to work with, so I needed to recover the databases. I put one of the db’s in emergency mode to try to run CHECKDB, but CHECKDB couldn’t access the files either.
So, on to the last resort (I’ll spare you the trial and error and get down to the solution):