October 13, 2011 at 1:55 pm
I tried to do log shipping between two databases on the same server. But the thing is that the secondary database which is in log shipping doesnn't come back from 'Restoring' state. Please suggest why it doesn't come back after a long time too.
The databse size is just 275MB.
And the time when i did log shipping is 9:00Am. and i am looking at the secondary database since that time.
Thanks..
October 13, 2011 at 1:59 pm
There are two options for the database on the secondary server (aka, the one receiving the log shipments, so to speak). It can either remain In Recovery, or it can be in Standby Read Only mode.
You would make this choice when you are initially restoring the backup into the secondary database server. I think by default it would be in In Recovery mode.
An interesting use of Standby Read Only is that you could use that database for running long running queries for perhaps reporting purposes. I believe this use is mentioned in BOL.
October 13, 2011 at 2:07 pm
tnk (10/13/2011)
There are two options for the database on the secondary server (aka, the one receiving the log shipments, so to speak). It can either remain In Recovery, or it can be in Standby Read Only mode.You would make this choice when you are initially restoring the backup into the secondary database server. I think by default it would be in In Recovery mode.
An interesting use of Standby Read Only is that you could use that database for running long running queries for perhaps reporting purposes. I believe this use is mentioned in BOL.
Can you suggest how to keep the secondary base iwth effect of the primary in no restoring state...
Thanks..
October 13, 2011 at 2:47 pm
Forgive me, I didn't quite state things correctly above.
The two modes your secondary database can be in to receive logs in a log shipping scenario are, RESTORE WITH NORECOVERY or RESTORE WITH STANDBY.
If you want to be able to run select queries on your secondary database, you need to select RESTORE WITH STANDBY which puts the database in read-only mode. If you're only using your secondary as a "hot spare" for disaster recovery, then select RESTORE WITH NORECOVERY.
Regarding your primary database, I don't understand your question. Your primary database should be read-write and running transactions normally.
October 13, 2011 at 3:03 pm
No my question is after the secondary database has gone in "Restoring" mode, how to bring to accessible mode?
Please suggest.
Thanks,
Affzal..
October 13, 2011 at 9:43 pm
Beginner_2008 (10/13/2011)
No my question is after the secondary database has gone in "Restoring" mode, how to bring to accessible mode?Please suggest.
Not sure what you mean.
If you want to bring the database online, restore the databse in RECOVERY mode. This will break the log shipping.
restore database yourdb with recovery
If you want to setup log-shipping in STANDBY mode (so that you can read from the secondary database), You will have to re-setup log shipping in standby mode.
October 14, 2011 at 12:19 am
Beginner_2008 (10/13/2011)
I tried to do log shipping between two databases on the same server. But the thing is that the secondary database which is in log shipping doesnn't come back from 'Restoring' state. Please suggest why it doesn't come back after a long time too.The databse size is just 275MB.
And the time when i did log shipping is 9:00Am. and i am looking at the secondary database since that time.
Thanks..
You might have missed few important steps for Log Shipping. I would suggest you to drop the secondary database (assuming its DEV server & small database) and enable the log shipping from step 1.
Following document will guide you step-by-step for log shipping.
How to: Enable Log Shipping (SQL Server Management Studio)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply