February 1, 2009 at 11:34 pm
I have production instances and the Disaster Recovery instances in different locations. When I try to implement log shipping, I realize that I cannot full backup of the databases. My database sizes are over 100 GB, so its not possible to transfer them fully daily over the network.
I need to configure log shipping, as well as I need to perform full backup of the databases daily. Is it possible by some way?
February 1, 2009 at 11:50 pm
Arun (2/1/2009)
When I try to implement log shipping, I realize that I cannot full backup of the databases. My database sizes are over 100 GB, so its not possible to transfer them fully daily over the network.
Log shipping just requires the full to be transferred once, after than, just the logs need to be shipped. There's no need to copy the full backup to the standby server daily.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 2, 2009 at 1:47 am
Yes, but actually I need to take backup of those log shipped databases daily, and take them to tape.
I cant take backups from the source server, because if I take, I need to tranfer it too to the disaster recovery server. It is not possible to be done daily because the DB size is over 100 GB, and the sites are different.
In the log shipped server, I cant take backups because it can be either in Recovering mode or in Standby mode.
Is there a workaroung for this issue?
February 2, 2009 at 2:50 am
Hi,
Anyway you are going to configure log shipping which is already a DR then why again Full backup to DR location?
May be you can take full backup at local location and move it to tape and ship the tape to DR location which is what normal practice....
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
February 2, 2009 at 3:28 am
Sakthivel Chidambaram (2/2/2009)
Hi,Anyway you are going to configure log shipping which is already a DR then why again Full backup to DR location?
May be you can take full backup at local location and move it to tape and ship the tape to DR location which is what normal practice....
Thanks for your suggestion. But actually my situation is like this.. I need to take full backups and TL backups daily in my production environment (not to the DR location), and it is often requred for restores.
Apart from this, we have a DR location, where the latest copy of production instance needs to be there incase the production goes down. For this when we configure log shipping, it contradicts with the backup plan in the production server. Thats the problem. 🙁
February 2, 2009 at 6:30 am
Arun (2/2/2009)
For this when we configure log shipping, it contradicts with the backup plan in the production server. Thats the problem. 🙁
Why? There shouldn't be any problem. Take full backups daily and write them to tape, set up the log shipping so that it doesn't delete the tran log backups after they've been shipped, and then you can write them to tape.
I don't understand the problem that you seem to have.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 3, 2009 at 12:19 am
GilaMonster (2/2/2009)
Why? There shouldn't be any problem. Take full backups daily and write them to tape, set up the log shipping so that it doesn't delete the tran log backups after they've been shipped, and then you can write them to tape.
Many thanks for your help, I got a clue from your statement. I am able to take full backup in my production server using the WITH COPY_ONLY option now. Also the log shipping is working fine.
Have a great day!! 🙂
February 3, 2009 at 7:18 am
Arun (2/3/2009)
I am able to take full backup in my production server using the WITH COPY_ONLY option now. Also the log shipping is working fine.
The only time the COPY_ONLY option is necessary is if you are doing differential backups as without that a full backup will reset the differential base.
Full backups never break the log chain.
Are you doing differential backups?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 4, 2009 at 5:38 am
GilaMonster (2/3/2009)
The only time the COPY_ONLY option is necessary is if you are doing differential backups as without that a full backup will reset the differential base.
Full backups never break the log chain.
Are you doing differential backups?
wow, thats great.. i didnt knew this :w00t:
I was thinking that log shipping is just an automation of the normal transaction log backups and restores which we normally perform. but it didnt fail even after i took full backups, log backups and even diff backups in the source server. There is no need for COPY_ONLY
Now I dont have any issues. Am continuing my usual backup schedules (to tape) in my production server, and also using log shipping to the DR server.
Great, that i got the best advice from u. Thanks a lot for ur help 🙂
February 4, 2009 at 9:30 am
Arun (2/4/2009)
I was thinking that log shipping is just an automation of the normal transaction log backups and restores which we normally perform. but it didnt fail even after i took full backups, log backups and even diff backups in the source server.
If you take a log backup and don't ship it, you will have a problem. No log backups past that point will restore, saying there are LSNs missing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 5, 2009 at 3:54 am
GilaMonster (2/4/2009)
If you take a log backup and don't ship it, you will have a problem. No log backups past that point will restore, saying there are LSNs missing.
Yes I noticed that..
Also, I saw that, if at all the log shipping keeps failing for some reason, I am able to resolve it by taking a full backup in the source server and restore it in the log shipped database and leave it in restore / standby mode. All logs shipped after that keeps succeeding.
Now log shipping looks so good a DR plan for me..
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply