SQL Server Performance Forum – Threads Archive
database still in "restoring" modeI’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?
Apparently, this is expected behavior and I was unaware of this aspect of log shipping. Please disregard my question. Thanks.
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
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
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
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.