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 |
|
methodology
Starting Member
31 Posts |
 Posted - 2008-10-31 : 10:03:28
|
| HiWe've been using mirroring in sync mode but weve found that due to the performance contranits that this method of repl introduces, it's no good for us (and people dont want to afford the ent version & get async mirroring or clustering etc etc). We have SQL 2005 standard.I've been testing Log Shipping for a couple of days, and whilst its more 'cumbersome' than mirroring, it works well - i can ship logs, failover gracefully between two servers, failback gracefully etc etc. swapping roles and getting the rep going either way is NO problem.The problem however is when I come to do an 'inelegant' failover, or rather the FAILBACK from same. I kill all the sql services on server A, do a RESTORE WITH RECOVERY on B and re-enable the 'logship backup' jobs that I have been so carefull to disable / reenable when ive been swapping roles. Again, all is groovy - Server B is live and backing up its logs every x timeframe.On SQL server 2005 standard is it POSSIBLE to restore these trn backups taken on server B to server A once its 'livened' up again? no matter what order I do thing in, or how careful i am with disabling jobs before I do anything i can NOT get these logs to apply - not those taken before the forced failure, or those after. I KNOW that I can just do full backup of the dbases on server B and overwrite Server A with them and I KNOW that I can get the log shipping mechanism to do the same for me - an initial Re-seed to get log shipping going again. however we are looking at this for DR and resyncing 200GB of databases over the 10Mb P2P line is going to take a while...(plus DR testing weekends - 4 a year - will be a REAL pain in the arse..)I do NOT want to do a full resync - only to reapply the logs taken on B to A and fail back to A....Help me obe-wan-kenobe, or im jumping out the window.Cheers."A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-31 : 11:21:52
|
| You will have to take full backup from New primary(old secondary) to restore in old primary again to failover. |
 |
|
|
methodology
Starting Member
31 Posts |
Posted - 2008-10-31 : 11:27:55
|
| so I CANT apply the trans logs taken on 'B' to 'A' to get both DBases in sync and then reactivate 'A' - is that what youre saying? I HAVE to do a full back / restore jobby?I need to be totally clear on this...Cheers"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-31 : 11:30:12
|
| Yes you have to take full backup and restore it in Primary like you did before. |
|
|
|
methodology
Starting Member
31 Posts |
Posted - 2008-10-31 : 11:31:52
|
| That is EXTREMELY ANNOYING.howeverThankyou Sodeep for your clarification."A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips. |
|
|
|
mpetanovitch
Yak Posting Veteran
52 Posts |
Posted - 2011-10-04 : 13:02:11
|
| Can anyone answer why you have to do a full backup (in the failback scenario) before applying the logs from the old secondary -> old primary?This seems to contradict that.If the primary database is accessible, back up the active transaction log and apply the log backup to the secondary databases. If the original primary server instance is not damaged, back up the tail of the transaction log of the primary database using WITH NORECOVERY. This leaves the database in the restoring state, and eventually you will be able to roll this database forward by applying transaction log backups from the replacement primary database. http://msdn.microsoft.com/en-us/library/ms191233(v=SQL.90).aspxMike Petanovitch |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-10-04 : 14:36:50
|
| I suspect the trick you are looking for is to restore the Primary (as was) server to the same backup that the log shipping secondary (as was) had restored, and then restore the backups that were made on the Secondary (after it took over as the Primary)If the originally primary is restored to less/more backups, that were originally made on the Primary, than the Secondary did then all bets are off. |
|
|
|
|
|
|
|
|
Log Shipping Fail back - a definitve answer?