SQL Server Performance

database still in "restoring" mode

Discussion in 'SQL Server 2005 Log Shipping' started by lorbeckd, Nov 30, 2005.

  1. lorbeckd New Member

    I've been playing with the SQL 2005 log shipping and have been running into a few problems.

    My system is configured on a virtual server with two instances of SQL 2005. The OS is Windows 2003. I'm shipping logs from the default instance to the named instance with the default having the primary database and the named, the secondary.

    When I use the log shipping wizard, and have it do the initial restore to the secondary, all appears to work well but the database remains in "(Restoring ...) in the Management Studio (MS) GUI. So after starting over and manually restoring the database to the secondary, I used the wizard again to set this up and now the Agent jobs indicate success in applying the logs but again, the database continues to show as (Restoring ...) in the MS GUI. It is indeed unavailable as you can't query it in a Query window. I tried this with upgraded SQL 2000 databases and newly created SQL 2005 databases, both with the same result.

    Has anyone run into this and can you tell me what might be happening?
  2. lorbeckd New Member

    Apparently, this is expected behavior and I was unaware of this aspect of log shipping. Please disregard my question. Thanks.
  3. Banthor New Member

    This is one condition

    RESTORE DATABASE [ENCE] FROM DISK = N'\ICTSQLDEVBak$ENCE.bak' WITH FILE = 1,
    MOVE N'ENCE_Data' TO N'E:MSSQLDATAENCE_Data.mdf',
    MOVE N'ENCE_Log' TO N'E:MSSQLDATAENCE_Log.ldf'
    , STANDBY = N'E:MSSQLROLLBACK_UNDO_ENCE.BAK',
    REPLACE, STATS = 10
    GO
    Restores the database in a REad only mode and then you can establish log shipping to the restore database. The database is usable in read only and easily set into normal mode.
    Just Run the last log shipping job, Backup the database and then restore without the standby parameter.

    Randy Piktin
    Technology Consultant
  4. satya Moderator

    Unless there is no NORECOVERY only used the database will be in access mode, otherwise you will see it inaccessible.

    Satya SKJ
    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