Need Help With A Restore 2

[dylanwhite]

Hi,

Here's my situation:
-I've created a named instance to restore a previously backed up Database from Tape.
-I started the named instance in single user mode and completed the restore (from backupExec tape) successfully.
- I have checked the disk to be sure that all data has been restored also and can confirm the full .mdf .ldf ect are present.
- However when i go into SQL Enterprise manager the database still says (loading) next to it. It has been like this for over a day now and i cannot even look at the properties or anything it just comes up with a message:
"Database is in the middle of a restore"

Does anyone have any ideas why this is and how a can get to my data???

Any Help is much appreciated.

Thanks in advance.

 

[dylanwhite]

Just thought this update from the error log might be useful too. The log says
SQL Server Error : 4060 , Cannot open database requested in login [database_name] Login Fails SQL Server State 42000.

Any ideas on this??

Thanks again

Dylan

 

[SQLBill]

While doing the restore did you use the command WITH NORECOVERY?

If so, did you use WITH RECOVERY on the last file being restored?

-SQLBill

 

[dylanwhite]

Thanks for responding Bill.

I have checked BackupExec and the recovery option was set to No Recovery.

Will This be a problem??

Thanks

Dylan

 

[SQLBill]

Yes, SQL Server is still waiting for the rest of the restore files. You have told it that there is at least one more to be restored before the database is in it's proper state. That's what WITH NORECOVERY means.

You might have to stop SQL Services and start the restore over again.

Try doing a search in this forum and the Microsoft SQL Server: Programming forum to see if there's any other option to force the WITH RECOVERY.

-SQLBill

 

[JamesLean]

You can recover the DB without actually restoring. This is the example from BOL:

-- Restore database using WITH RECOVERY.
RESTORE DATABASE MyNwind
   WITH RECOVERY

--James

 

[dylanwhite]

Thanks guys. Unfortunatley the tape has now been over written so doing the restore again is no longer an option. So i'm going to have to try going down the 'Searching the SQL Programming forum' in the hope that there's something out there that will help me force no recovery.

Thanks for your response

Dylan

 

[JamesLean]

Did you read my post? You don't have to do the restore again! Just execute the statement and it will force the database to recover without actually restoring anything.

--James

 

[SQLBill]

Just type James' script into Query Analyzer and run it. As he said, that should put your database into a completely restored state.

-SQLBill

 

[dylanwhite]

Thanks James.

I ran your script and it said completed successfully, checked the database and all good.
Thanks for your help guys. You have definatley saved my life here.

Cheers

Dylan