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?
thanks
I 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
>

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
>

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 t
o
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
>

Backup log With Truncate Only (Log Shipping)

SQL Server 2000 Standard Edition running on Win2k
I've setup log shipping according to an article I found here.
http://www.sql-server-performance.c...og_shipping.asp
Which truncates the log before doing the database backup then does the backu
p
and
subsequent log backups without truncating. Of course the truncate causes an
error
to be shown in the event viewer.
Since books describes the default behavior of log backups to be an automatic
truncate
after backup my initial thought is to drop the log backup before the db back
up
and just let
the truncate happen automatically with the normal log shipping backups.
Remove This -- BACKUP LOG database_name WITH TRUNCATE_ONLY
Remove This -- WAITFOR DELAY '00:00:05'
BACKUP DATABASE database_name TO database_name_backup_device WITH INIT
Remove the NO_TRUNCATE and let the log truncate automatically.
BACKUP LOG database_name TO log_backup_device WITH INIT, NO_TRUNCATE
They do mention in the article that you don't want to truncate the log after
you do
your normal log shipping backups because you might need them later. Would yo
u
possibly
need them later? I archive all my log and db backups each time they run.Brad <seveni7@.yahoo.com> wrote:

quote:

---

>SQL Server 2000 Standard Edition running on Win2k
>I've setup log shipping according to an article I found here.
>http://www.sql-server-performance.c...og_shipping.asp
>Which truncates the log before doing the database backup then does the back
up
>and
>subsequent log backups without truncating. Of course the truncate causes an
>error
>to be shown in the event viewer.
>Since books describes the default behavior of log backups to be an automati
c
>truncate
>after backup my initial thought is to drop the log backup before the db bac
kup
>and just let
>the truncate happen automatically with the normal log shipping backups.
>

---

My bad!
Found it on MS Knowledge Base Article - 818202
Just a warning.

Backup log With Truncate Only (Log Shipping)

SQL Server 2000 Standard Edition running on Win2k
I've setup log shipping according to an article I found here.
http://www.sql-server-performance.com/sql_server_log_shipping.asp
Which truncates the log before doing the database backup then does the backup
and
subsequent log backups without truncating. Of course the truncate causes an
error
to be shown in the event viewer.
Since books describes the default behavior of log backups to be an automatic
truncate
after backup my initial thought is to drop the log backup before the db backup
and just let
the truncate happen automatically with the normal log shipping backups.
Remove This -- BACKUP LOG database_name WITH TRUNCATE_ONLY
Remove This -- WAITFOR DELAY '00:00:05'
BACKUP DATABASE database_name TO database_name_backup_device WITH INIT
Remove the NO_TRUNCATE and let the log truncate automatically.
BACKUP LOG database_name TO log_backup_device WITH INIT, NO_TRUNCATE
They do mention in the article that you don't want to truncate the log after
you do
your normal log shipping backups because you might need them later. Would you
possibly
need them later? I archive all my log and db backups each time they run.Brad <seveni7@.yahoo.com> wrote:
>SQL Server 2000 Standard Edition running on Win2k
>I've setup log shipping according to an article I found here.
>http://www.sql-server-performance.com/sql_server_log_shipping.asp
>Which truncates the log before doing the database backup then does the backup
>and
>subsequent log backups without truncating. Of course the truncate causes an
>error
>to be shown in the event viewer.
>Since books describes the default behavior of log backups to be an automatic
>truncate
>after backup my initial thought is to drop the log backup before the db backup
>and just let
>the truncate happen automatically with the normal log shipping backups.
>
My bad!
Found it on MS Knowledge Base Article - 818202
Just a warning.