Restore Database...Loading message 1

[FancyPrairie]

I attempted to restore a database and it gave me an error message indicating that the disk was full. After cleaning up the disk space it now (via the Enterprise Manager) says that it is "Loading...". But it dosen't appear to be doing anything. I've attempted issuing the command "Restore database dbName with Recovery" to no avail.

What can I do to fix this?

 

[AtomicChip]

How long did you wait? Depending on the size of your database and your computer speed, it could take a long time to actually display any progress.

For some unknown reason, it took a co-worker of mine just over a half hour to restore a database (just over 1GB in size) that took me about five minutes to restore (it took about 10-15 minutes before it actually displayed any progress).

-----------------------------------------------

http://www.liquidfuzion.com

"The night sky over the planet Krikkit is the least interesting sight in the entire universe."
-Hitch Hiker's Guide To The Galaxy

 

[JohnBates]

FancyPrarie,

This worked for my SQL Server 2000 db.

I once had a db that was hung in 'Loading' status and I needed to delete it but always got the 'is inuse ' message.

This is what I did: (In Enterprise Manager)

1. Changed SQL Server config settings: Right-click Server name

Selected 'Allow modifications to system tables'

2. In master.sysdatabases, changed the category column (for the restored db name) from 1 to 0.

Refreshed EM - still shows (Loading)
3. But I was able to delete the db via right-click-->Delete

4. Reversed Step 1 to ** Uncheck ** 'Allow modifications to system tables' Best to not leave this capability in place.

Just be careful when messing with the systemtables.
Good luck.

In your case, maybe you can delete the Loading database, then recover some disk space, then try the restore again.

John

 

[SQLBill]

There's another possibility...when you did the restore did you use the WITH NORECOVERY option?

I suggest that before you do anything else, open Query Analyzer and run this:

RESTORE DATABASE <dbname>
WITH RECOVERY

WITH NORECOVERY leaves the db in a loading state so that additional backups can be applied. This is used when you have Differential and/or Transaction Log backups to restore.

-SQLBill

Posting advice: FAQ481-4875

 

[FancyPrairie]

AtomicChip, it ran all day while I was trying to clean up the disk. And then it ran all night (2g file).

SQLBill, I had tried your suggestion (see my 1st post), but had no luck with it.

JohnBates, I right clicked on the server (and everyplace else I could think of) and could not find "Allow modifications to system Table". So, in desperation, I opened the master.sysdatabases anyway and discovered that every record in the category column was already set to 0 (note that these are databases created by a vendor). So I went ahead and deleted the database, created a new one, and then restored it. That worked great.

Thank you all for taking the time to answer my question.

 

[JohnBates]

FancyPrarie,

Well, now even I cant find that setting, but it does exist.

I'm very glad you were able to delete the 'loading' database. It can be very frustrating. Took me 3 days to get 1 20GB db restored thru lot of trial and error.

John

 

[vongrunt]

If I remember correctly, "column" column in sysdatabases is for replication stuff.

Column "status" (Yet Another Bitmask (tm)) contains restore status info - and can be reset with sp_resetstatus... carefully.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[vongrunt]

Damn... my brain is tiny ATM... I mean "category", not "column".

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.