I have a customer with a gigantic SQL Server 2005 database (almost 400GB including the log file) and a table has been unintentionally dropped. We are trying to restore to a temporary database then we will copy the data across from the missing table.
The disk had around 450GB free space. As soon as I started the restore, SQL allocated the 400GB for the new temporary database leaving 50GB free. The restore then ran continuously without error for around 23 hours. At some point near the end (I don't know when) it failed with an error along the lines of "error not found", which I assume is related to disk space because the disk only had 8GB free due to another file being created in the meantime.
The database is now sitting in state of "Restoring...".
I guess I can delete this database and start over again, but my client is jumping up and down because they have no database and can't wait another 23 hours - and even then, will it work again!?
My question is - can I get the database out of the "Restoring..." state at least just to see if it has the data I need - as I only need the data out of one of the tables. I have already tried:
RESTORE DATABASE xxx WITH RECOVERY
But that came back with the error
Msg 4333, Level 16, State 1, Line 1 The database cannot be recovered because the log was not restored. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
Any help much appreciated.