SQL Server Performance

Backup Restore Stuck in "Restoring..." State

Discussion in 'SQL Server 2005 General DBA Questions' started by carlitosway, Sep 5, 2006.

  1. carlitosway New Member

    Hi,

    I'm trying to restore a backup on a sql server 2005 instance. I needed to restore this using the WITH NORECOVERY option (I'm in the process of configuring database mirroring). When the restore completes, the database is stuck in a "Restoring...." state on SQL Server Management studio. I try to click into its properties and get an error message, saying it's still in the middle of a restore.

    The database is very small (about 26 MB) and the log file size is about 1Gb.

    I've repeated the restore several times and this happens each time.

    Any ideas?

    Thanks
  2. ghemant Moderator

    Hi,
    here is a excerpt from BOL for *NORECOVERY* option

    quote:NORECOVERY

    Instructs the restore operation to not roll back any uncommitted transactions. Either the NORECOVERY or STANDBY option must be specified if another transaction log has to be applied. If neither NORECOVERY, RECOVERY, or STANDBY is specified, RECOVERY is the default.

    SQL Server requires that the WITH NORECOVERY option be used on all but the final RESTORE statement when restoring a database backup and multiple transaction logs, or when multiple RESTORE statements are needed (for example, a full database backup followed by a differential database backup).



    Note When specifying the NORECOVERY option, the database is not usable in this intermediate, nonrecovered state.


    When used with a file or filegroup restore operation, NORECOVERY forces the database to remain in the restoring state after the restore operation. This is useful in either of these situations:

    A restore script is being run and the log is always being applied.


    A sequence of file restores is used and the database is not intended to be usable between two of the restore operations.
    Try to run restore yourdb with recovery

    Regards

    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami
    ------------------------
    http://hemantgirisgoswami.blogspot.com
  3. satya Moderator

    As explained if you have restored the datbase WITH NORECOVERY clause then it will be inaccessible for any process...follow as suggested to get in online.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  4. carlitosway New Member

    Thanks for the information.

    I've been reading through database mirroring configuration guides and they all specify to restore your database on the secondary server using the WITH NORECOVERY option. Why is this so?


  5. satya Moderator

    Yes, inorder to make the mirror database not availble for usage and it is by default.
    I don't feel changing this option and for disaster recovery purpose this is good.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.

Share This Page