database state set to restoring

  • i have set up a log ship of a database to my remote 2005 sql server and when the jobs run the database is created and then state is set to restoring. Cant do anything with the database. I have tried using the norecovery and the standby options on the "restore tranasction log" tab but state is still restoring. I expected to be able to gain access to the database properties but it doesnt allow when in restore state.

    the primary db backs up the log every hour and copies to the secondary server. the secondary restores every 5 hours(twice in all). Is there something wrong with my logship set up

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Hi,

    You will not be able to access any database, while SQL Server is restoring. Once restored and if you have opted for Standby, then you will be able to read-only. Please bear in-mind, you need to allow time for backup, file transfer and file restore and depending on size of database, this may take a bit.

    Thank you,

    Phillip Cox

  • i figure the restore should be complete at that point though. When creating the job i had the plan backup and restore the target db to the secondary server and it reported successful. The .BAK file is only 400MB and the logs are small(63kb). But the database state is still set to restoring. Both servers are on the same lan so the file copy should be very quick

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • select percent_complete from sys.dm_exec_requests where command like 'RESTORE%'

    That can tell you the "percent" at which the restore is done.


    * Noel

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply