How can I back up a log-shipped database?

(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-shipped
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 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).
No can do. The recovery procedures etc in SQL Server aren't written to handle 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...
> 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-shipped
> 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 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...
>
|||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...
> Hi John,
>
> No can do. The recovery procedures etc in SQL Server aren't written to handle 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[vbcol=seagreen]
>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
>
>.
>
|||> Any way we can use an undocumented "flag" in some system table to toggle the DB back in
> STANDBY mode? ;-)
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 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 process work or give us some other
option for recovery (NO_RECOVERY, RECOVERY, STANDBY, QUASI_PERMANENT_RECOVERY).
Or MS would need to change SQL Server so it allow us to do a backup of a database 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 check 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...
> 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...
>
|||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 could 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 they're later
resumed.
Could I, then, do a detach and copy the underlying .MDF/.LDF files? Or would 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 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 process work or
> give us some other
> option for recovery (NO_RECOVERY, RECOVERY, STANDBY, QUASI_PERMANENT_RECOVERY).
> Or MS would need to change SQL Server so it allow us to do a backup of a database 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 check 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...
>
|||Hi,
Just for my benefit, what would be the purpose of backing up a database that
does not change? I assume that the secondary DB of the pair resides in DR and
as a result the site will be protected (fire proof etc.) secondly the
Database in the prod environment is being backed up and the backups are sent
off site.
- You might want to consider Replication over logshipping of you really must
backup the secondary DB .
"John Peterson" wrote:

> (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!
>
>
|||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 database will not just be
able to add the log records to the log-shipped database, because the transaction 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 files. 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 could 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 they're later
> resumed.
> Could I, then, do a detach and copy the underlying .MDF/.LDF files? Or would 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...
>
|||Hello Olu!
We had hoped to be able to grab some of these Production databases for Dev and QE testing.
It'd be more convenient to grab them from our DR environment (the log-shipped environment)
because it's already on our corporate network. But, I see that it's proving to be more of
a challenge than we had hoped. ;-)
Out of curiosity, how would I configure Replication over log-shipping? Does that mean I'd
set up a log-shipped DB as the Replication Publisher? I would have thought that couldn't
be done on a read-only DB...
At this point, I'm kind of considering using DTS and the Transfer Database Task to
accomplish what I want. Some of the DBs are big, and I hate the thought of essentially
BCPing everything out, but it *does* appear to work...
"Olu Adedeji" <OluAdedeji@.discussions.microsoft.com> wrote in message
news:C91B47A6-F4D2-4599-819F-D87924CE42D7@.microsoft.com...[vbcol=seagreen]
> Hi,
> Just for my benefit, what would be the purpose of backing up a database that
> does not change? I assume that the secondary DB of the pair resides in DR and
> as a result the site will be protected (fire proof etc.) secondly the
> Database in the prod environment is being backed up and the backups are sent
> off site.
> - You might want to consider Replication over logshipping of you really must
> backup the secondary DB .
> "John Peterson" wrote: