September 21, 2004 at 12:08 pm
Hi
I am having a problem with restoring a databaase. My databse is around 7 GB. I am restoring it from last night backup as a different database name using enterprise manager. After it restores successfully, it displays the database in enterprise manager with loading status, i.e., MyDatabaseName(Loading). It stays in that status and does not refresh the objects in it. I have tried this other smaller databases and it works fine. I do not know why it is not working for this database.
Thanks for your help.
September 21, 2004 at 3:05 pm
it sounds like it is behaving as if the "NORECOVERY" option is set (meaning waiting for further tansaction logs to be applied.)
try it the old fashioned way by TSQL:
RESTORE DATABASE mynewdbname FROM DISK = '\path\filename' WITH RECOVERY, REPLACE
,MOVE {logical data device name} TO 'data file path & name'
,MOVE {logical log device name} TO 'log file path & name'
That's the simple way. see tsql help file.
September 21, 2004 at 3:26 pm
Ok. I will try that but I end doing detach/copy/attach fo now
September 22, 2004 at 6:22 am
You might also try "RESTORE DATABASE dbname WITH RECOVERY" after you have completed your restore while it is in 'loading' status.
September 22, 2004 at 7:22 am
No. I tried this but does not work
RESTORE DATABASE dbname WITH RECOVERY
September 22, 2004 at 8:06 am
Is the db you are backing up in ONLINE status?
Are you changing the file names on the options tab to valid, unique file names/paths?
Are you selecting the 'Leave db operational...' option when restoring via SEM?
Are any error/info msgs displayed during the restore (SEM) or the 'RESTORE...WITH RECOVERY' command?
Is there any other info you can supply?
September 22, 2004 at 8:38 am
Yes. DB I am backing is online. When I restore, I check the unique filenames/path under the option tab. Default option is selected tp "Leave DB Operational...", i.e., there are no more transactional logs to be applied. Once the restored DB is on loading status, I tried Restore..With Recovery. I think it gave me an error message such as "DB is partially restored...".
Finally I ended up doing detach/copy/attach to workaround this problem.
Thanks
September 22, 2004 at 9:17 am
OK, another question (which shouldn't be an issue but...). What is the Recovery Interval for the server upon which the original db resides and the destination server (can be found on Database Settings tab in Server Properties)? If this is set at a value other than zero, you may want to consider changing it.
To attempt to circumvent potential issues with the Recovery Interval, execute a CHECKPOINT immediately before executing the BACKUP. This will minimize any rolling forward that may be needed. You could also do a BACKUP LOG between the CHECKPOINT & BACKUP DATABASE to minimize the log portion of the backup.
I am 'playing' with db's >300GB and have never encountered this problem so it should not be size related which is why I tend to believe a time or other issue is responsible.
September 22, 2004 at 9:39 am
Yes. The recovery interval is 0. Instead of restoring from last night backup and then applying transaction logs. I backed up the entire database and then restored it so that I do not need to apply logs but It still gives the same problem.
I also used Restore verifyOnly to make sure that my backup is valid. So I do not know what is happening.
September 22, 2004 at 10:59 am
Hmmm... Well, I'm stumped. I can't think of anything else that might be hindering the restore. If I think of something, I'll post again. Sorry I couldn't be of more help...
September 22, 2004 at 11:40 am
Thanks for your time.
October 6, 2004 at 12:09 pm
Could it be a simple Refresh on Enterprise Manager?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply