Indianrock (6/3/2009)
You can't run a backup on a log-shipping standby database normally because it's either in no recovery or standby. To reduce the performance hit on our primary server that results from a full backup running for hours, we want to try backing up the standby copy instead.What I thought might work, without breaking log shipping, is to script out a job that does the following;
1) disable log ship alert on standby
2) disable log ship restore job on standby
3) run restore database mydatabase with recovery
4) run full backup or differential backup
5. restore database with no recovery or standby
6. re-enable jobs
I'm not sure about the part where you resume restoring t log backups following a full backup, but will run a test.
What you're describing, requires the process of a log shipping role switch which will leave the former primary database inaccessible. The switch takes the form
I'm pretty sure this is not what you want to do though, if you have high backup times consider a 3rd party tool or native compression (if avail). Also, as mentioned, consider a new regime incorporating differential backups. Full backups once a week and diffs through the week will make it easier to resume a broken log shipping scenario using a differential backup instead of a full re init. See my article on this at this link[/url]
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉