Missing Database

[eggy168]

Urgent!!
Hi, I have no idea why the database is missing? However, I can find it under MSSQL/Data. I tried to restore it, but for some reasons, it takes almost an hour to load it, Anyone who has an idea why it takes that long? The database is not big at all. Or is there anyway to make it shown?
Thx

 

[nigelrivett]

Try sp_attach_db

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[eggy168]

Thank for replied.
I tried the sp_attach_db, but it has an error message.
There are messages said the restore has been done, but for some reasons, the (Loading) is on for almost an hour, any idea?

 

[SQLBill]

How did you RESTORE it? Most likely it was restored with the WITH NORECOVERY option. Try this:

RESTORE db_name WITH RECOVERY

Run that in Query Analyzer.

-SQLBill

 

[eggy168]

I did it WITH RECOVERY. The database is still has the word, "LOADING" right next to the name of the database.
I was sorry that I gave out wrong information. When I checked the MSSQL/DATA, I couldn't find the _Data.mdf and _log.ldf.
Do you think this is the reasons why it takes so long to restore?
Thanks

 

[JayKusch]

How large are the database and TLog files we are dealing with.

Thanks

J. Kusch

 

[SQLBill]

How did you do the RESTORE? It should have given you an error if it couldn't find the files.

-SQLBill

 

[SQLBill]

To answer this question:

When I checked the MSSQL/DATA, I couldn't find the _Data.mdf and _log.ldf.
Do you think this is the reasons why it takes so long to restore?


No. You can have the files located anywhere you want them. You just have to tell SQL Server where they are. If SQL Server can't find them it (in my experience) gives you an error message stating so.

-SQLBill

 

[eggy168]

The size of the database is only 7736KB, I don't see it is a long db.
I got the message telling me the restore success, however, there is a word, 'loading' after the message, and it has been sat there for almost an hr after the message.

This is the way I did my restore: I didn't do it manually, so, I right click the "databases", then choose, Restore Database,
Restore: From Device, Select Devices, C drive (which is my disk), Options, check this option, "Force restore over existing database", in the Recovery Complete state, I check, "Leave database nonoperational but able to restore additional transaction logs". I got the successful message, but for some reasons, loading the database takes so long.

 

[JayKusch]

Have you tried refreshing you connection. Try Disconnecting and Reconnecting the server in Enterprise Manager.

Thanks

J. Kusch

 

[eggy168]

I don't want to lose the data again. Everytime I click the database, it said it is in the middle of restoring. It has been almost 3 hours already? Any idea what should I do?

 

[eggy168]

Thanks everyone, I disconnected the Enterprise Manager and Reconnected it, and it worked finally.
Thanks

 

[SQLBill]

By the way, this was the problem:

"Leave database nonoperational but able to restore additional transaction logs"

That is the same as saying WITH NORECOVERY. Your database was waiting for you to restore additional transaction logs.

Running "RESTORE dbname WITH RECOVERY" from the Query Analyzer would have solved the problem.

-SQLBill