Tips and Tricks for:  SQL Server

The Problem:   A Restore can leave the database in "LOADING" state

When restoring a database you get three options for the final state of the database.

• Leave database operational. No additional transaction logs can be restored.
• Leave database nonoperational but able to restore additional transaction logs.
• Leave database read-only and able to restore additional transaction logs.

So what if you've chosen the second option and you want to use the database and not restore additional logs?

Solution: RESTORE DATABASE mydbname WITH RECOVERY

The solution line is the answer.  Just substitute your database name and the state of the database is changed an you can now go in and use it.

I had to use this when a client tried to do a  restore to a point-in-time but selected transaction logs beyond that point in time.  He hit some type of problem which left the database in the nonoperation (loading) state even though that wasn't what he had intended.  The RESTORE DATABASE WITH RECOVERY command was all that was needed to get the DB in to a usable state.