Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
HeliosRex
Starting Member
7 Posts |
 Posted - 2008-01-23 : 14:13:14
|
Hello guys,I am a newbie to the forum and this is my first post here! I am trying to implement a log shipping solution to set up Disaster/Recovery site for our OLTP database. These two servers - both of which run on SQL 2000, SP4 - are in different physical locations and of course, different networks as well. To initiate the DR, I will take a full backup of OLTP and restore it on DR. After that, I will ship the logs over as and when they are ready (which is every 15 minutes on OLTP) and restore them on DR to get it on par with OLTP. However, as a standard practice, I take a full backup of my OLTP database once every night. So all transactions that took place in my OLTP database between the last transaction log backup and full backup are available only in my (full) backup. For these transactions to make their way into DR, I will have to restore from the full backup to DR, isn't it?Let me try to explain in a slightly better way (if I can  )Feb-01-2008 2 AM - Full backup taken on OLTP database. This backup is used to restore on DR DB server for the first time in an attempt to build the DR site. Now both the servers are on par.Feb-01-2008 2:15 AM - Transaction log backed up on OLTP database. This log is shipped to DR server and restored. Now both the servers are on par.Feb-01-2008 2:30 AM - Transaction log backed up on OLTP database. This log is shipped to DR server and restored. Now both the servers are on par.......Feb-02-2008 1:45 AM - Transaction log backed up on OLTP database. This log is shipped to DR server and restored. Now both the servers are on par.Feb-02-2008 2 AM - Full backup taken on OLTP database. Since the transactions that took place in OLTP between 1:45 AM and 2 AM are available ONLY in the full backup, do I need to restore my full backup from OLTP to DR?Is this the only way to bring these two servers on par? As a standard practice, I do like to keep a full backup of my OLTP database taken at least once a day. Given this scenario, can you Gurus please suggest me what is the best way to implement log shipping for my DR? Please note that my database is about 30 GB and a full restore from OLTP to DR is taking about 4 hours, which is currently unacceptable. Shipping logs and restoring, on the other hand, takes only a few minutes. My only concern is how to get the transactions - that happened between the last transaction log backup and full backup - to my DR database.Any suggestions/solutions/guidance is grately appreciated.Regards,HeliosRex |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-23 : 22:54:42
|
| No, you don't have to restore full backup erevy night, just apply log backups. |
 |
|
|
HeliosRex
Starting Member
7 Posts |
Posted - 2008-01-24 : 12:47:40
|
| Rmiao,Sorry if I am being ignorant, but how are the transactions that took place between last transaction log backup and the full backup are moved to the DR, if I only restore the logs. On the other hand, if I have transaction log backup (call it TL1), full backup (called FB1)and another transaction log backup (call it TL2) in that order...would it be possible to restore TL1 and TL2 and bypass FB1? Is that acceptable?Please enlighten me.Regards,HeliosRex |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-24 : 22:57:15
|
| Yes you can, that's what we do everyday. |
|
|
|
|
|
|
|
|
Clarification sought for SQL2K log shipping