Tuesday, March 27, 2012

Backing up secondary DB in log shipping ...

Hello can someone clear this up for me?
In BOL "Log Shipping Tips and Best Practices" is the following
recommendation:
"Generating Database Backups from the Secondary
Since the log shipped database is only a small increment of time out
of sync with the primary database, it is a good idea to use it to
generate the full database backups. This would keep the primary
production server free of the potential overhead and contention
incurred by a daily (or scheduled) backup. "
In my experience and from searching other threads in this group this
is actually not true - backing up the log shipped database will not
work, it will fail with the error:
"Database is in warm-standby state (set by executing RESTORE WITH
STANDBY) and cannot be backed up until the entire load sequence is
completed."
Am I missing something or is BOL just being misleading?
thanks,
Peter
Peter,
http://support.microsoft.com/kb/311115 contains the correction.
Note: With SQL Server 2005, you it might be possible to create a snapshot
database over the secondary and back that up. (But I have no experience
with trying.)
RLF
<peter.wismer@.gmail.com> wrote in message
news:1187280994.613717.77730@.l22g2000prc.googlegro ups.com...
> Hello can someone clear this up for me?
> In BOL "Log Shipping Tips and Best Practices" is the following
> recommendation:
> "Generating Database Backups from the Secondary
> Since the log shipped database is only a small increment of time out
> of sync with the primary database, it is a good idea to use it to
> generate the full database backups. This would keep the primary
> production server free of the potential overhead and contention
> incurred by a daily (or scheduled) backup. "
> In my experience and from searching other threads in this group this
> is actually not true - backing up the log shipped database will not
> work, it will fail with the error:
> "Database is in warm-standby state (set by executing RESTORE WITH
> STANDBY) and cannot be backed up until the entire load sequence is
> completed."
> Am I missing something or is BOL just being misleading?
> thanks,
> Peter
>
|||On Aug 16, 1:12 pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> Peter,
> http://support.microsoft.com/kb/311115contains the correction.
> Note: With SQL Server 2005, you it might be possible to create a snapshot
> database over the secondary and back that up. (But I have no experience
> with trying.)
> RLF<peter.wis...@.gmail.com> wrote in message
> news:1187280994.613717.77730@.l22g2000prc.googlegro ups.com...
>
>
>
>
>
> - Show quoted text -
Thanks very much Russell (I was beginning to mistrust my command of
the english language - thought I might need to find a Canadian
translation of BOL...)
Peter
|||I think it was a case of wishes versus reality. - RLF
<peter.wismer@.gmail.com> wrote in message
news:1187288061.528821.290710@.j4g2000prf.googlegro ups.com...
> On Aug 16, 1:12 pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> Thanks very much Russell (I was beginning to mistrust my command of
> the english language - thought I might need to find a Canadian
> translation of BOL...)
> Peter
>

No comments:

Post a Comment