June 16, 2008 at 11:40 am
Hello
I have an interesting backup solution that I am trying to implement and would like some suggestions.
Here is the basic problem.
I have a Primary on which I will set up log shipping to a secondary instance.
I would also like to have a Tertiary or more DB instance for both testing or tertiary level DR that is not just a warm standby.
What I would like to do is periodically take a full backup off of the secondary instance without interrupting the log ship cycle. However, it appears that I cannot take a full backup of the "warm" Db unless I take it out of standby mode which means that I would have to take a full backup of the Primary and Restore to the secondary in standby to continue the log shipping task.
Have I missed something, or is this the only way to accomplish what I am looking to do, or is there a better way?
Steve
June 16, 2008 at 11:56 am
Don't think you can do this.
Database Mirroring might be a better solution for you. course not sure you can backup there.
Why not backup off primary?
June 16, 2008 at 12:22 pm
you may use mirroring for the DR purpose and then take a snapshot of the mirrored database for read only purpose
subban
June 16, 2008 at 12:27 pm
I did not want to do backup on the primary because I did wanted to off load the backup cpu load to another server.
As far as database mirroring is concerned, do you mean by means of replication?
Steve
June 16, 2008 at 11:12 pm
I mean "SQL Server 2005 Mirroring " feature.
Assume you have two servers PRIMARY and SECONDARY
configure the mirroring between PRIMARY server to SECONDARY server, so you will have a mirrored database in the SECONDARY server (which you can activate i case of the PRIMARY server failure).
Also you can take a snapshot (sql server 2005 feature) of the mirrored database in the SECONDARY server and the same snapshot database you can use for read/reporting purpose.
subban
June 17, 2008 at 1:58 am
You can refer this document which explains about mirroring. But you cannot backup the mirror server as it would be in restoring state.
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
[font="Verdana"]- Deepak[/font]
June 17, 2008 at 7:28 am
Well I figured out how I will accomplish my task.
Basically I will log ship as I had originally stated. Then (believe it or not I am going to plug a red gate tool) I can use sql packager to take a point in time scripted snapshot of the database. With these two tasks, I can accomplish my end goal.
Thanks for your comments and ideas.
Steve
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply