Database Restore stuck in "loading" state.
-
I'm trying to find any help I can on the particular problem we have. This forum is the closest I can find.
(I also entered this post in the Admin Forum having searched around for a possible solution. I came to the conclusion it should be in here as well/instead??? Apologies to anyone finding this twice...I can't seem to find an answer.....yet)
Description: SQL server 7 and running a Veritas restore.
Problem : Restore gets to 99% complete on the Veritas restore process, Database is in a state of "(loading)".
Also, the Restore shows the following statement being used by Veritas
RESTORE DATABASE [dbname] FROM virtual_device = 'BackupExecSqlAgent_dbname_00' WITH REPLACE, RECOVERY
It is 'awaiting command' according to sp_who2 under Query analyser and 'sleeping' under EM current activity.
As far as I can tell, the restore has been done using the RECOVERY option. I can't see why this doesn't appear to complete.
Database is quite large (33GB) and logfile (about 1GB).
How long should this take to finish "loading" or is this likely to be still this way tomorrow morning (about 18 hours from now).
We need this database back and this is a full restore so should bring it all back the way it was when the Backup was taken.
I'm completely confused and don't know what to do next.
Can anyone help me?
Addional to post in Admin section - We are SQL 7 SP3. Veritas 8.6.
We have tested the restore settings by restoring a smaller DB which started, went into (loading) state and then completed successfully.
Is there something I can check to see what the larger database restore is now doing (if anything) whilst it is still in "loading" state. Perhaps it is doing some index verification (or similar) which is taking all the time.
We are leaving it overnight to see if it completes. If it doesn't I don't know what to do next.
Thanks (in advance) for any help.
-
Several years ago in a disaster recovery test, we experienced problems restoring a database that had been backed up by Veritas. It, too, was in the 30 gb range. We've never used a 3rd party backup tool since, preferring to rely on native SQL Server backups.
You didn't say how long your restore had been running, but the hardware will obviously have a tremendous impact on that. In those old days, the database that I referred to took several hours to restore (also SQL 7.0) using SQL Server backups/restores. I think I'd leave it for a while, and start looking for other options, ie, do you have the mdf/ndf/ldf files backed up? Do you have an older backup with transaction log backups? Can you issue your own Restore command against the backup that you have (rather than Veritas)?
If you have another server (preferably a test server) that can hold the database, I'd start trying any other options on that server while waiting on the Veritas restore. If you can get it back on the test server, then you can duplicate your method on the production server, or even take a native SQL Server backup of the newly restored database and restore that on the production server.
Good luck!
Steve
-
Steve,
Thanks for the information......it all makes me feel a bit nervy. The MDF and LDF (restored) look ok if I just look at the file system. I guess though that they are not fully closed until the Vertias restore finishes properly --- yes??
The restore had been running several hours (up to 4) when we thought it couldn't have been working so we stopped it to investigate. This was last Friday. Yesterday was Tuesday here in Aus (monday was a holiday) and today (wed) the restore has now been running for 16 hours. Still "(loading)".
I notice you're online so I'll post this bit and then continue.......stay tuned.
-
Steve,
Continuing on......
Now I'm concerned about this and I believe we only have complete backups which are done overnight every night. I guess as the databases get bigger this will need to change but for now they complete within enough time. So our ONLY alternative is to get these restores from the backups performed by Veritas backup.
When you suggested restoring via a method other than Veritas, what exactly did you mean? Issuing the command from Query Analyser?? Same command as I posted in my first post?
Currently the tape is still loaded in the backup unit (since the restore is incomplete, Veritas does not release the tape). The only way to get it out and do anything else with it is to re-boot the server (which puts us back to square one). Needless to say, no overnight backups got done......(but we knew this was to be the case when we left the office last night)
Can I do anything with the MDF and LDF on the data directory? I'm not sure what to try.
If I sound panicked......well.....nearly.
thanks
Annabel
-
Update!!!
Waiting for some tech support person to come in and help...
In the meantime the IT guy decided to try doing the restore using the NORECOVER option. We would then do a RESTORE DATABASE dbname WITH RECOVERY manually after it was finished. Guess what.......the restore didn't finish .... same as when using the WITH RECOVERY option. 99% complete and waiting......aaarrrrggghhh!!!
Anyhow, given we had an apparantly good MDF and LDF, IT guy asked if I could try to make use of it anyway. (I mean things were so bad by this stage we may as well try anything whilst we wait for the support guy)
So I did the following:
1. in QA did EXEC sp_detach_db dbname. Got an error saying not closing cleanly(??) but then said "successfully detached"
2. decided to do a sp_attach_single_file_db (again in QA), which started to execute.
3. had a look at what was happening via sp_who2 and noticed the process was doing a CREATE DATABASE. Also noticed it changing from RUNNABLE to sleeping and back again. (nervous again by this stage)
4. Then I noticed user 'sa' with process DBCC was blocked by the other process (now getting really nervy again) and nobody else can start an EM session unless they are already in one. (now absolutely worried again)
5. Started to think I had broken EVERYTHING and was contemplating a KILL spid. Whilst I was considering this, my phone rang so I decided to answer it and leave the process alone for a few minutes.
6. IT guy comes out of the computer/server room suddenly saying that he has seen in the event viewer that "recovery of database dbname 100% complete".
7. had a look around and task now not showing up in sp_who2 and 'sa' user no longer blocked by the process.......yay....much cheering.
8. looked around some more but db not attached (apparantly), so did a normal sp_attach_db.
9. All ok now......
Weird.....
Any comments?????
The MDF and LDF were ok for use but this was not obvious.
-
Just wondering, Did you refresh the databases from EM when you saw database was in 'loading' status?
-
many times.....often just kind of hoping something may have changed.. alas nothing ever did.
-
It sounds like Veritas MIGHT have been trying to VERIFY the final results. See if there is an option to just do the restore without data checks (verify, whatever).
I backup my SQL Server db's using the SQL Server BACKUP DATABASE and BACKUP LOG commands. The backups go to hard drive and then I use Veritas Backup Exec to copy those backup files. Nice thing is I don't need expensive Agent for SQL Server licenses.
I also don't have to worry about whether a third-party software will actually backup and restore my databases.
-SQLBill
-
SQLBill,
I agree that's what it looks like. I also like you suggestion of backups to disk and then regular file backups of those files. Much cleaner!! I'll suggest it to IT dept. I'm sure they'll be much happier if they don't feel so exposed to any further problems like we've just had.
thanks everyone
-
To piggyback on this same item...I restored and the database was left in loading state. I ran the restore db with recovery in query analyzer and received the the error
(file or db partially restored. Must restore entire file successfully.) Selected through EM the backups the I wanted.
Can anyone help with steps to get this database back up.
ps most recent backups seems invalid. Went back to 2 weeks ago.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply