| Author |
Topic |
|
maximus_vj
Yak Posting Veteran
88 Posts |
 Posted - 2004-07-22 : 10:43:49
|
| Hello,I am planning to do the Log Shipping on my primary Server. From what I understand if I am log shipping a DB it will put the transaction logs on a network share and the warm-standby Server takes this log and restores on the warm-standby server so that we have an identical copy of the database on warm-standy server with some delay.From what I understand I can only have transaction log backups.Can I implement this scenariao with SQL Server Enterprise Editions Log Shipping....I will take a full backup of the database at the beginning of the week and then take transaction log backups for every 15 minutes. So what I want on teh Warm-Standby Server is full DB restore at the beginning of the week and transaction log restores subsequently. Can I implement this?Thanks a bunch. |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-22 : 10:46:32
|
| You probably want to do a full backup everyday in case you need to ever restore from tape.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
maximus_vj
Yak Posting Veteran
88 Posts |
Posted - 2004-07-22 : 11:00:05
|
quote:
Originally posted by derrickleggett
You probably want to do a full backup everyday in case you need to ever restore from tape.
Hello,Thanks for the reply. That makes sense. So I am chaning my scenario:I will take a full backup of the database at the beginning of the day and then take transaction log backups for every 15 minutes. So what I want on the Warm-Standby Server is full DB restore at the beginning of the day and transaction log restores subsequently. Can I implement this through the Log Shipping wizard. So that my warm-standby server takes the full backup and does a full DB restore and translog restores subsequently.Can I do this?Thanks |
|
|
|
gercr
Yak Posting Veteran
53 Posts |
Posted - 2004-07-22 : 11:06:45
|
| Is better is you just, make a full backup at the beginning of the day and store on a safe place, and then take transaction logs backups for every 15 minutes. On your Stand-by server you just need to restore (for 1 time only) the full Bd Backup, and the subsequently transaction logs, you will never need to make a full restore of your database, just apply the transaction logs. |
|
|
|
maximus_vj
Yak Posting Veteran
88 Posts |
Posted - 2004-07-22 : 11:58:07
|
| Hello,If you take a full Backup of the database. Doesn't the Transaction log gets cleared? If that happens, every day I take a Full Backup of the database on Primary and trans logs every 15 mins. And on Standby server I just do Full resotre (Only First time) and using Log shipping trans logs are restored automatically on the stand by serevr.Now consider this I did a full backup of the database on my primary and lets assume my last translog from my prmary(before Full Backup on Primary) was successfully restored on the standby server. Now dosing a full backup will clear all teh trans log so tehre are soem transactions that teh Stand by Server might not know the once between translog before Full Back up and FUll Back up. SO my Stand by server is in inconsistent State.Is this correct?Thanks |
|
|
|
gercr
Yak Posting Veteran
53 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-22 : 14:05:16
|
| Standby server is not in an inconsistent state as you only need to apply the transaction logs. You only apply the full backup to initially create the standby database. After that, you only need to apply the transaction log backups. The full backup contains only enough information from the transaction log to restore to a consistent state. It does not clear it out when doing it. The information is still in the log.Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-22 : 14:29:07
|
| Tara, can I just be sure that I understand this (not related to Log Shipping specifically).Lets assume:At 22:55 a transaction backup runs. This is running every 10 minutes, so it previously ran at 22:45.At 23:00 a Full Backup runs. Lets assume this happens once every day at this time.At 23:05 the transaction backup runs again.Am I right in thinking:1) The Full Backup grabs all appropriate data (the database and anything from the logs which is committed but, for whatever reason, not yet physically in the database). Thus if I restore from this backup I will get a database equivalent to the state of the original at 23:002) The transaction backup has all "database changes" since the previous transaction backup. It has no correlation to the Full Backup. So I could, if I wanted to, restore a Full Backup from Last night and the intervening transaction logs OR I could restore a full backup from any previous "night" and ALL the intervening transaction logs and I would get to exactly the same result.2a) Therefore, I presume, it is the Transaction backup that detects that there is slack space at the end of the log file and reduces the size of the LDF file. The Full Backup has NO bearing on this task (I do know that the LDF file will grow forever if transaction backups are not done - or Recovery Model = SIMPLE - but I just want to be sure that if Transaction Backups ARE running then the Full Backup process is NOT a factor in the size-reduction of LDF files)I hope I've got this right, because in describing this I've had a "eureka moment" of understanding. Or maybe that should be a "Damascus moment"!!Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-22 : 14:32:40
|
| Yes, yes, yes, and yes (if I understood all of your statements correctly that is).Tara |
|
|
|
maximus_vj
Yak Posting Veteran
88 Posts |
Posted - 2004-07-22 : 14:47:08
|
| Hi Tara,In my Database plan for Log Shipping I will tell it do trans log backups every 10 minutes. And I also have another job Scheduled to do a Full Backup once a day.So the Standby Server will do a full restore only ONCE. From then onwards it will just restore the trans logs. And my other job (Full Backup) will not affect Standby Server as Log Shipping will restore only transaction Logs.Am I correct?1)Lets say sth went wrong on my Primary Server. So my Standby Server is exactly the same since my last Transaction log backup.Is this correct?2) If I want to use my Full backup from the second job and use trans logs from the Log Shipping Job after the Full backup, Can I construct my Database?Thanksmaximus |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-22 : 14:49:29
|
| Yes you are correct. We've got a job that manually copies over the full backup to the standby server just in case we need to use it though. Log shipping does not handle this. 1) Only the same up to the last transaction log restored2) Yes, you would restore the full backup and all (or up to the point in time that you need) of the transaction logs that occurred after the full backupTara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-22 : 15:04:45
|
| I've not done log shipping, although of course I've read lots about it!When the logs are shipped to the Standby Server they remain on there for a while do they? The process [I suppose I mean SQL Enterpise Version's Log Shipping Wizard!!] has some housekeeping to kill them off after a couple of days, does it?Or is that down to the DBA to "roll their own" routine to kill them off after a sensible interval?Kristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-22 : 15:05:37
|
quote:
Originally posted by tduggan
Yes, yes, yes, and yes (if I understood all of your statements correctly that is).
Cheers Tara, I'll post a follow up under a new thread so as not to pollute this oneKristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-22 : 15:11:17
|
quote:
Originally posted by Kristen
When the logs are shipped to the Standby Server they remain on there for a while do they? The process [I suppose I mean SQL Enterpise Version's Log Shipping Wizard!!] has some housekeeping to kill them off after a couple of days, does it?Or is that down to the DBA to "roll their own" routine to kill them off after a sensible interval?
In the log shipping configuration, you give it a file retention value.Tara |
|
|
|
maximus_vj
Yak Posting Veteran
88 Posts |
Posted - 2004-07-22 : 15:31:57
|
| Thank you everyone for helping me out!! |
|
|
|
|
Log Shipping - SQL Server 2000 Enterprise edition