Restore Failed (Now says Loading)

[jmueller]

I have been searching for an answer to my problem without any luck so here's another thread on this problem...

I'm using sql 7. I just tried to restore a database from EM and I got an error saying there "wasn't enough disk space to expand..." something like that. Now the database simply says (loading) and NO ITEMS under it. I've had this problem before but don't remember how i salvaged it. Can someone tell me how I can gain access to this database so I can retry my restore?

Thank you one and all!

 

[tlbroadbent]

After increasing the disk space, delete the database and restart the restore.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[jmueller]

Thanks Terry but it's not that simple for reasons I won't bother you with. What I was looking for and failed to mention was a command that can be run from the query analyzer to free up the db. I've done it several times in the past and one day i'll make a note of it! Are you or anyone aware of such a command?

 

[tlbroadbent]

Are you referring to sp_detachdb or setting the database to "normal" status by updating the sysdatabases table?

sp_configure 'allow updates', 1
reconfigure with override
update master.dbo.sysdatabases set status = 32
where name = 'your_db_name'



If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[SQLBill]

Try:

RESTORE DATABASE dbname WITH RECOVERY

That should put your database into a recovered state.

-SQLBill

 

[jmueller]

No Terry, that's not the command I was looking for. I'll find out what it is and reply a little later. Meanwhile, cleaning up some space and going to lunch seemed to have fixed it. I was able to restore without error.

Thanks Terry and you also SqlBill for responding!

Jack.

 

[SQLBill]

Jack,

Here's an Undocumented DBCC command that might be what you are looking for:

DBCC DBRECOVER(dbname)

This command manually recovers a database. Normally, databases are recovered at system startup. If this did not occr--due to an error or the disabling of recovery--DBCC DBRECOVER and be used to attempt a manual recovery. Dbname is the name of the database to recover.

(information from The Guru's Guide to Transact-SQL by Ken Henderson)

-SQLBill