Restoring a database gets stuck in (loading) mode
I hope this is not to long winded:
I have a routine which Takes the latest logs off the primary servers backup
and restores it to a hot backup database that remains in standby mode. every
once in a while it seems that the backup database becomes stuck in the loading
phase. By saying "Loading Phase" I mean: When SQL Server Agent begins to
restore the database in enterprise manager places '(Loading)' after the database
name until it is done. After the restore is done, it changes to (Read-Only)
since it is in standby mode.
I read up on how to restart a file restore that was interrupted when reading
from tape. I am restoring from a file on disk, but is this a similar problem?
Did the restore kick out partially done, and is there a way to continue
When the problem occurs and I catch it I end up running my script that restores
the database fully from scratch including all full, differential, and log
files are restored. This seems like a bad practice in case something goes
wrong for real and the standby DB is stuck in (Loading) mode.
A secondary question comes up when it comes to my scripts which are resoring
my database to the hot server:
I created a table in the master DB which simply holds the number of the last
index in the backup file to be restored, therefore the script will know where
to pick up the next time it runs to restore all log files that have a higher
index. Is there another way to obtain this number, or a better way to know
at which point to begin in the backup file to find the newest logs?
Thanks for any help,