How can I back up a log-shipped database?

Hi,
You can not backup a database or log that is standby mode
with regular backups.
As a work around, you have to restore the database and
then back it up.
Here is something you could use:
USE MASTER
RESTORE DATABASE DB_NAME
WITH RECOVERY
This changes the standby status to normal db use and then
you can back it up.
The only thing that I am not sure is what happens at the
next log shipped/restored because it depends how you have
it setup.
hth
DeeJay
>--Original Message--
>(SQL Server 2000, SP3a)
>Hello all!
>I've got a database that is the secondary server in a log-
shipped pair. Whenever I try
>and do a BACKUP on this database, I get an error message
that the database is in a
>READ-ONLY STANDBY mode.
>Is there any way to circumvent this, temporarily, and
make a database backup of a
>log-shipped database?
>Thanks!
>
>.
>Thanks DeeJay!
What we're thinking (if you'll humor us for a moment):
* Temporarily disable the Job that's responsible for processing the log-ship
ped
transaction logs.
* Change the status of the database to get it out of STANDBY mode (as per yo
ur
recommendation).
* Back up the database.
* Change the status of the database back to STANDBY (dunno how to do this ye
t).
* Re-enable the Job.
I'm not sure if this is advisable, though. For instance, what does the Log-
Shipping
Monitor service do? Is it sensitive to any of the proposed elements above?
Thanks for any additional help you can provide! :-)
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:3a6001c48f88$24958740$a301280a@.phx.gbl...[vbcol=seagreen]
> Hi,
> You can not backup a database or log that is standby mode
> with regular backups.
> As a work around, you have to restore the database and
> then back it up.
> Here is something you could use:
> USE MASTER
> RESTORE DATABASE DB_NAME
> WITH RECOVERY
> This changes the standby status to normal db use and then
> you can back it up.
> The only thing that I am not sure is what happens at the
> next log shipped/restored because it depends how you have
> it setup.
> hth
> DeeJay
> shipped pair. Whenever I try
> that the database is in a
> make a database backup of a|||Hi John,

> * Change the status of the database back to STANDBY (dunno how to do this yet).[/v
bcol]
No can do. The recovery procedures etc in SQL Server aren't written to handl
e this scenario, quite simply. Any
way you can grab the log backups already on the fallback machine?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Peterson" <j0hnp@.comcast.net> wrote in message news:uXJ9wm4jEHA.632@.TK2MSFTNGP12.phx.g
bl...[vbcol=seagreen]
> Thanks DeeJay!
> What we're thinking (if you'll humor us for a moment):
> * Temporarily disable the Job that's responsible for processing the log-sh
ipped
> transaction logs.
> * Change the status of the database to get it out of STANDBY mode (as per
your
> recommendation).
> * Back up the database.
> * Change the status of the database back to STANDBY (dunno how to do this
yet).
> * Re-enable the Job.
> I'm not sure if this is advisable, though. For instance, what does the Lo
g-Shipping
> Monitor service do? Is it sensitive to any of the proposed elements above
?
> Thanks for any additional help you can provide! :-)
>
> "DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> news:3a6001c48f88$24958740$a301280a@.phx.gbl...
>|||Thanks Tibor. I don't think that we have the main database backup available
from the
fallback machine.
Any way we can use an undocumented "flag" in some system table to toggle the
DB back in
STANDBY mode? ;-)
Just to give you the heads up: we've got our production databases log-shipp
ed into our
corporate network. If we can leverage these log-shipped databases, we won't
have to pay
the network price to copy from production again.
Thanks for any additional help you might be able to provide! :-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:uDrJTt4jEHA.2908@.TK2MSFTNGP10.phx.gbl...
> Hi John,
>
> No can do. The recovery procedures etc in SQL Server aren't written to han
dle this
> scenario, quite simply. Any
> way you can grab the log backups already on the fallback machine?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:uXJ9wm4jEHA.632@.TK2MSFTNGP12.phx.gbl...
>|||You might want to test this out in dev first.
I am not sure if this is supported by MS or if your log
shipping will work properly again.
You might have to re-configure log-shipping.
DeeJay
>--Original Message--
>Thanks Tibor. I don't think that we have the main
database backup available from the
>fallback machine.
>Any way we can use an undocumented "flag" in some system
table to toggle the DB back in
>STANDBY mode? ;-)
>Just to give you the heads up: we've got our production
databases log-shipped into our
>corporate network. If we can leverage these log-shipped
databases, we won't have to pay
>the network price to copy from production again.
>Thanks for any additional help you might be able to
provide! :-)
>
>"Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in message
>news:uDrJTt4jEHA.2908@.TK2MSFTNGP10.phx.gbl...
(dunno how to do this yet).[vbcol=seagreen]
aren't written to handle this[vbcol=seagreen]
fallback machine?[vbcol=seagreen]
processing the log-shipped[vbcol=seagreen]
STANDBY mode (as per your[vbcol=seagreen]
(dunno how to do this yet).[vbcol=seagreen]
instance, what does the Log-Shipping[vbcol=seagreen]
proposed elements above?[vbcol=seagreen]
mode[vbcol=seagreen]
and[vbcol=seagreen]
then[vbcol=seagreen]
the[vbcol=seagreen]
have[vbcol=seagreen]
a log-[vbcol=seagreen]
message[vbcol=seagreen]
>
>.
>|||> Any way we can use an undocumented "flag" in some system table to toggle the DB back in[vb
col=seagreen]
> STANDBY mode? ;-)[/vbcol]
Nope, none that I know of. Just think about it. When you bring a db out of s
tandby mode, you get the recovery
work persisted. Stuff has been rolled forward and rolled back. Period. How w
ould you be able to apply a later
log backup onto this, as the log records in that log backup are totally out-
of sync with the database you have
performed a permanent recovery on?
For this to work, MS would need to do some changes in how the recovery proce
ss work or give us some other
option for recovery (NO_RECOVERY, RECOVERY, STANDBY, QUASI_PERMANENT_RECOVER
Y).
Or MS would need to change SQL Server so it allow us to do a backup of a dat
abase in STANDBY mode. (And here
I'm too tired right now to consider what ramifications that would have on lo
g record sequencing and recovery
;-) ).
I know this question has been on the table before, so you might want to chec
k the archives to see if someone
came up with anything. I have a feeling that you are out of luck, though...
Perhaps you should opt for a home-grown log shipping solution, to give you b
etter control of handling of the
backup files?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Peterson" <j0hnp@.comcast.net> wrote in message news:%23UXfE54jEHA.2412@.TK2MSFTNGP15.ph
x.gbl...
> Thanks Tibor. I don't think that we have the main database backup availab
le from the
> fallback machine.
> Any way we can use an undocumented "flag" in some system table to toggle t
he DB back in
> STANDBY mode? ;-)
> Just to give you the heads up: we've got our production databases log-shi
pped into our
> corporate network. If we can leverage these log-shipped databases, we won
't have to pay
> the network price to copy from production again.
> Thanks for any additional help you might be able to provide! :-)
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:uDrJTt4jEHA.2908@.TK2MSFTNGP10.phx.gbl...
>|||Thanks Tibor! It's clear I don't understand the whole RECOVERY business.
I had *hoped* that, by temporarily suspending the log file processing, I cou
ld somehow get
the DB in a state where it was backup-able. But, if I'm understanding you c
orrectly, it
sounds as if, by virtue of performing a backup on the DB, I'd be "marking" t
he transaction
log in such a way as to be incompatible with the normal log files when they'
re later
resumed.
Could I, then, do a detach and copy the underlying .MDF/.LDF files? Or woul
d that break
the whole log-shipping "linkage"?
Thanks again!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:%23tykrd5jEHA.3896@.TK2MSFTNGP15.phx.gbl...
> Nope, none that I know of. Just think about it. When you bring a db out of
standby mode,
> you get the recovery
> work persisted. Stuff has been rolled forward and rolled back. Period. How
would you be
> able to apply a later
> log backup onto this, as the log records in that log backup are totally ou
t-of sync with
> the database you have
> performed a permanent recovery on?
> For this to work, MS would need to do some changes in how the recovery pro
cess work or
> give us some other
> option for recovery (NO_RECOVERY, RECOVERY, STANDBY, QUASI_PERMANENT_RECOV
ERY).
> Or MS would need to change SQL Server so it allow us to do a backup of a d
atabase in
> STANDBY mode. (And here
> I'm too tired right now to consider what ramifications that would have on
log record
> sequencing and recovery
> ;-) ).
> I know this question has been on the table before, so you might want to ch
eck the
> archives to see if someone
> came up with anything. I have a feeling that you are out of luck, though..
.
> Perhaps you should opt for a home-grown log shipping solution, to give you
better
> control of handling of the
> backup files?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:%23UXfE54jEHA.2412@.TK2MSFTNGP15.phx.gbl...
>|||I think that when you do recovery, log records are either removed or added (
possibly both) to the
transaction log. This means that a later log backup from the production data
base will not just be
able to add the log records to the log-shipped database, because the transac
tion log has been
changed. The LSN (log sequence numbers) doesn't match anymore.
I haven't tested whether you can detach and attach a database a database in
STANDBY mode. Give it a
try. If not, you might consider stopping SQL server and just grabbing the fi
les. Not supported and
not guaranteed that you can attach such files, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Peterson" <j0hnp@.comcast.net> wrote in message news:O991LI6jEHA.1348@.TK2MSFTNGP15.phx.
gbl...
> Thanks Tibor! It's clear I don't understand the whole RECOVERY business.
> I had *hoped* that, by temporarily suspending the log file processing, I c
ould somehow get
> the DB in a state where it was backup-able. But, if I'm understanding you
correctly, it
> sounds as if, by virtue of performing a backup on the DB, I'd be "marking"
the transaction
> log in such a way as to be incompatible with the normal log files when the
y're later
> resumed.
> Could I, then, do a detach and copy the underlying .MDF/.LDF files? Or wo
uld that break
> the whole log-shipping "linkage"?
> Thanks again!
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23tykrd5jEHA.3896@.TK2MSFTNGP15.phx.gbl...
>sql