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 |
|
wig_tightener
Starting Member
2 Posts |
 Posted - 2005-12-19 : 12:14:25
|
| Hi. This is my first post so please be gentle :-) I have searched everywhere to find an answer to this question but have so far found nothing - I was hoping that some wise individual on these forums might help.Situation - I have a log shipping solution set up (SQL 2000 Ent/Windows 2003). The standby machine (warm standby) is happily receiving its logs from the primary, but I need a way to backup the standby machine. It may sound like a strange request (backing up a standby machine!!), but thats what the "men in suits" want me to achieve. I'm able to perform a backup of the secondary machine, which was a headache in itself as it has to be in a Read-Only state, but the when attempting to restore it fails with complaints that the machine is in a "Warm Standby Mode". Please help this Newbie. If you need any more info just let me know.Kindest Regards. |
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2005-12-19 : 14:43:28
|
| RESTORE DATABASE <database> WITH RECOVERY to get it operational (note no FROM clause there)Then RESTORE with full backup and subsequent logs. (or however you would like to do it, see below, but I'm guessing you just want full standby, back on full standby)But basically anything you are doing here is going to break log shipping. As the syncronization will be lost.Really the shipped logs ARE the standby machines backups, That is, they ARE EXACTLY the same data. Can't you just send those to tape and convince the suits you are done. I guess there may be a situation where this wouldn't be sufficient but I can't imagine it.As long as the logs are being happily received there is absolutely no difference between the log backups from the live server and the standby. But that dosen't mean all the databases on the standby are dedicated to logshipping or vise versa so maybe that is the real spec of what is to be accomplished.And interesting thing you will probably find out of this is logs backups are continuous. That is say I have a set of backups in this patternFull-log1-log2-log3Full-log11-log21-log31I can restoreFull-log1-log2-log3-log11-log21-log31So reallyit's more like thisFull-log1-log2-log3Full-log4-log5-log6forever.I think that's pretty neat really.One more thing maybe, is that I haven't ever been in a situation where restoring the system databases from one machine to the next is necesssary, so your standby machine would want it's own set of backups for those."it's definitely useless and maybe harmful". |
 |
|
|
wig_tightener
Starting Member
2 Posts |
Posted - 2005-12-21 : 07:27:58
|
| Hi. Many thanks for the response but your suggestion brought me back to the same errors that I have been seeing for the past few days - the computer says "No". When trying a restore, it complains that SQLServer is in Warm Standby and fails the restore.Anyway, I have figured out a work around. What I've implemented is a scheduled SQLServer Agent Service stop, then take a backup of disks within the box using Veritas. Once thats complete, use another batch file to restart the SQLServer Agent to get the TLogs back in sync.I suspect that this is the only way to do this without breaking log shipping. Many thanks for the advice. |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2005-12-21 : 08:21:36
|
| Interesting, I'm sorry I can't provide anymore direct help, this is especially difficult when from here (everybody's situation and experience is different) your setup appears unnecessarily unorthodox. But let's take it to a very simple question.What is your failover plan if you can't get the standby databases out of standby mode?"it's definitely useless and maybe harmful". |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2005-12-27 : 02:57:50
|
| Hi wig_tightener, I just got one question for u, can those file backup be restore? I'm not too good with Veritas, but if you just backup the disk itself while SQL is running (as in backing up the mdf and ldf files), doesn't the file is in-use state and it cannot be restore (as in attach)? I guess there will be no transaction lost since its warm standby server, but just want to know if that will work or not.Thanks |
|
|
|
|
|
|
|
|
Backing Up a Warm Standby (Log Shipping)