Log Shipping Q - Using a database from a WarmStandby

Question

Log Shipping Q - Using a database from a WarmStandby

Chief78CJ7

SSC Veteran

Points: 237
More actions
February 11, 2004 at 6:07 am

#161618

I have log shipping running on three servers. The source (in a distant city over our WAN), a warm standby in that city and another warm standby local to me here.
Its big enough (4gb) that copying the database back from the source to here takes a long time over the WAN.
I would love to be able to get a copy of the log shipped databases here and restore them to another server (dev server) to be usable. I cannot back up the warm standby databases because they're in warm standby mode.
Does anyone have any experience in what I'm trying to do. It seems a shame to have to copy a backup over the WAN when I have these databases 'right here' all up to date and all...

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

vhanda SSCrazy Points: 2240 More actions · Answer 1

I've had a log shipping / warm standby setup operating successfully for a couple of years (apart from some minor network hiccups and a recent cranial vapour lock on my part) and it works great. You can use the integrated log shipping if you have Enterprise Edition or roll your own set of scripts. I did the latter, since I have Standard Edition. I hear that scripting your own gives you more control, anyhow.

Here's my setup:

The production server does a full db backup every Sunday night (which is downtime) and restores to my standby. The standby server is accessible on my production box as a linked server. Then every 15 or 20 minutes I truncate the log, back it up, copy it to a share on my warm standby, and run a sproc on my standby box to restore it.

Notice that this setup only works with the current log file, if you want to get more complicated you can name each shipped log file differently with some kind of naming convention and keep them around, in case your standby gets corrupted or some such. You can also do differential backups, say, hourly, then log backups at a smaller time increment. Check out BOL for some info on log shipping.

Let me know if you want more details...

Vik

Chief78CJ7 SSC Veteran Points: 237 More actions · Answer 2

I'm seeming having a hard time describing what I want, or there is a hard time others understanding what I'm saying..

You know what a warm-standby database is? Of course you do.. I want a copy of THAT database. Of course this isn't directly possible. You can't back up a warm-standby database, and you can't copy the MDF file.

I want a copy of the warm-standby database. Why? Because it takes too long to copy a backup of the Live Production server over our WAN. It seems a waste to have to waith 5-6 hours for a copy to happen over our WAN when the local warm-standby database has all the data and is physically 'right there' next to me. ya know?!

There has to be a way to do this--or should...........

vhanda SSCrazy Points: 2240 More actions · Answer 3

OK... maybe I didn't read your question closely enough...

But, and I ask this out of partial ignorance, why not set up another warm standby server, log ship to it as well, and then restore with recovery to make IT useable? Assuming that you can't just restore with recovery on the warm standby that is 'right there' because it needs to remain as a warm standby. KnowwhatImean?

Vik

vhanda SSCrazy Points: 2240 More actions · Answer 4

I know what you're saying, though. It would be nice to just grab hold of the standby database right there, so my above suggestion assumes that there is no easy way of doing so...

Chief78CJ7 SSC Veteran Points: 237 More actions · Answer 5

If I setup another warm-standby, I'm in the same boat. To set that up, I would have to get a new copy of the live database. If I have a copy of that database.. I wouldn't need to go through log shipping and recovery,etc.. I'd already have a copy of the live database, which is what I'm looking for.

vhanda SSCrazy Points: 2240 More actions · Answer 6

So do you need an up-to-date copy once or on an ongoing basis? If you've got some downtime, you could take your local warm standby, restore with recovery to make the db useable, back it up immediately for your dev db, then restore the newly "live" db back "with standby" over the warm standby (which should be the same) ?

Mind you, I've never tried this myself & don't know if it's even possible... worst case the restore with recovery will "break" the log shipping process & you'll hafta get another live db, which would solve your initial problem anyhow. For that matter, if you just need a live db once, why can't you just get a zipped full backup burned onto CD from wherever the live database is at?

Chief78CJ7 SSC Veteran Points: 237 More actions · Answer 7

I need an up-to-date copy whenever.. Not necessary routinely, but every so often. I do have down time.. 28 hours from Sat evening to Sun evening.

I like your idea of taking the current warm-standby and then making it useable, then immediately using that as the copy to reenable log shipping. That just might work. I won't like having to break/re-enable log shipping, etc.. but you're right that it might be what I'm looking for.

I could get a zipped backup, but I can already suck down a copy once a week.. It's party I don't wanna wait 6 hours, partly I wanna solve this. It doesn't make sense that I have the database locally, but have to resort to pulling down a copy from the distant server. If I were to back it up, zip it and burn it on a CD and have it sent to me, that would take even longer.

Thanks a LOT for you input Vhanda!