Backup of Standby databases

I'm running an offsite replica of our database using Log shipping.
This is working very well, but I'd like a way to take a backup of the
replica without
breaking the log shipping. Making a backup at the hosting center and copying
to our development hq takes over a day.
I've got the full database at our shop now, being updated constantly with
log shipping. A backup operation on a database that is in standby mode
fails. I can restore ... with recovery, backup, but then I can't continue to
log ship.
Even though it really isn't documented, "backup database dbname with standby
"= 'xxx.tuf' " works fine. I've used it to switch my secondary to primary
and so forth. I'm wondering if I can
restore ... with recovery
backup the database
backup with standby
This would give me a backup, and put the database back into standby so it
could continue to apply logs, but I'm pretty sure that the backup operation
would write to the log and cause the LSN's to get out of sync, and so the
next log restore would not happen correctly.
My other idea is to stop the server, copy the database and log file, and
then reattach these copies as a new database name. I'm pretty sure this idea
is going to work, but I have to stop my server, and it is a bit manual.
Is there anyway to backup a database while it is in standby or no recovery
mode?
thanksI doubt what you suggest would work, because the LSNs would be out of sync.
Most people use the normal backups of the orginal database, and do not worry
about backups of the log shipped db.. Will that not work for you?
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"et" <et@.discussions.microsoft.com> wrote in message
news:96F275B6-D92F-46E9-99E3-996DCB59EADC@.microsoft.com...
> I'm running an offsite replica of our database using Log shipping.
> This is working very well, but I'd like a way to take a backup of the
> replica without
> breaking the log shipping. Making a backup at the hosting center and
copying
> to our development hq takes over a day.
> I've got the full database at our shop now, being updated constantly with
> log shipping. A backup operation on a database that is in standby mode
> fails. I can restore ... with recovery, backup, but then I can't continue
to
> log ship.
> Even though it really isn't documented, "backup database dbname with
standby
> "= 'xxx.tuf' " works fine. I've used it to switch my secondary to
primary
> and so forth. I'm wondering if I can
> restore ... with recovery
> backup the database
> backup with standby
> This would give me a backup, and put the database back into standby so it
> could continue to apply logs, but I'm pretty sure that the backup
operation
> would write to the log and cause the LSN's to get out of sync, and so the
> next log restore would not happen correctly.
> My other idea is to stop the server, copy the database and log file, and
> then reattach these copies as a new database name. I'm pretty sure this
idea
> is going to work, but I have to stop my server, and it is a bit manual.
> Is there anyway to backup a database while it is in standby or no recovery
> mode?
> thanks
>