August 15, 2017 at 10:46 am
| I have two SQL Server 2008 R2 servers one for PROD and the other for DR. I am trying to add log shipping for a database called School. Steps so far
Is there a way that I can restore the database without having the database been stuck in the restoring state. |
August 15, 2017 at 11:29 am
No, That's how log shipping works. Maybe what you want is Replication.
August 15, 2017 at 11:49 am
What do you need to do?
If you want a stand-by copy of the database for DR, and are using log shipping, then the database will indeed be "restoring". That's what it's supposed to be. The database must be in a state to allow each subsequent log to be applied as they are taken on the primary.
Database mirroring works in a similar manner, the databases will show "Mirror, synchronized / restoring" as the state.
If you want this copy to be available for queries, then log shipping may not be what you want. Since you are in SQL 2008, you can replicate the data. That may present various challenges.
Always-On is not available until SQL 2012, so that's not an option.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
August 15, 2017 at 12:15 pm
You can use STANDBY mode if you want the secondary for log shipping to be read only. Or you can use NORECOVERY and the database stays in the restoring state and can't be used for read operations. There are trade offs for whatever direction you go - such as users kicked out for log restores if using standby.
But when you restore for any purposes using NORECOVERY, the database state will be restoring. That's normal.
Sue
August 15, 2017 at 1:50 pm
Thank you for the replies.Earlier in the day, I had followed the same procedure to put log shipping on three larger databases of 15GB each, but I did not experience this behavior. The School database is 14GB. The only difference between the previous three databases and this database is that when I took a backup I tried to take a backup again but it failed as the backup file was already present. Do you think I should just wait for a couple of hours for the database to complete?
August 17, 2017 at 9:10 am
testingtheworld100 - Tuesday, August 15, 2017 1:50 PMThank you for the replies.Earlier in the day, I had followed the same procedure to put log shipping on three larger databases of 15GB each, but I did not experience this behavior. The School database is 14GB. The only difference between the previous three databases and this database is that when I took a backup I tried to take a backup again but it failed as the backup file was already present. Do you think I should just wait for a couple of hours for the database to complete?
To setup log shipping , why do u need to take a backup yourself ? After the configurations done in the wizard , SQL does it itself . And the database will be in the restoring state , which is perfectly normal . Not sure what you are trying to do . This can't be mirror setup too , where in we've to manually backup and restore the backups (full backup and at least one trans log) but leave it in recovery mode for mirror setup to establish . Are you just trying to setup a standalone copy of this database and its taking very long time to restore ? my understanding.
August 17, 2017 at 9:40 am
Arsh - Thursday, August 17, 2017 9:10 AMTo setup log shipping , why do u need to take a backup yourself ? After the configurations done in the wizard , SQL does it itself .
That's incorrect. It can be done in advance.
If you use the wizard, there is the option on initialization of "No the secondary database is initialized". That is why the option is there.
Although it would be done differently, it can be setup using scripts. Many people don't use the wizards for things.
So the only path is most definitely not just using the wizard and having SQL Server set it all up.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply