SQL Server 2005 Restore Hanging

Question

I have a database showing up in SQL Enterprise Manager as "(Restoring...)"

If i do SP_WHO there is no restore process.

The disk and CPU activity on the server is very low

I think it is not restoring at all.

How can I get rid of this?

I've tried renaming the underlying MDF file, but even when I do "NET STOP MSSQLSERVER" it tells me the file is open.

I've tried using PROCEXP to find what process has the file open, but even the latest PROCEXP can't seem to do that on Windows Server 2003 R2 x64. The lower pane view is blank.

In the SQL Server log it says "the database is marked RESTORING and is in a state that does not allow recovery to be run"

Answer 1

Sql Server has two backup types:

• Full backup, contains the entire database
• Transaction log backup, contains only the changes since the last full backup

When restoring, Sql Server asks you if you want to restore additional logs after the full backup. If you choose this option, called WITH NORECOVERY, the database will be left in Restoring state. It will be waiting for more transaction logs to be restored.

You can force it out of Restoring mode with:

RESTORE DATABASE <DATABASE_NAME> WITH RECOVERY

If this command gives an error, detach the database, remove the MDF files, and start the restore from scratch. If it keeps failing, your backup file might be corrupted.

Here's a screenshot of the restore options, with the default selected. The second option will leave the database in Restoring state.

Image of the restore options http://img193.imageshack.us/img193/8366/captureu.png

P.S.1. Are you running the 64 bit version of process explorer? Verify that you see procexp64.exe in the task manager.

P.S.2. This is more like a question for serverfault.