Re: How can I back up a log-shipped database?

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 08/31/04 

Date: Tue, 31 Aug 2004 22:16:35 +0200

> 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...
> > 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...
> >> > 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!
> >> >>
> >> >>
> >> >>.
> >> >>
> >>
> >>
> >
> >
>
>

Relevant Pages

  • Re: How can I back up a log-shipped database?
    ... 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 ... It's clear I don't understand the whole RECOVERY business. ...
    (microsoft.public.sqlserver.server)
  • Re: Audacity and Gentoo
    ... > What's the UPS, btw? ... Still, a difference system would save a lot of space, unless database ... It would also be useful in case of data recovery as you'd at least be ... > Backup and Point-In-Time Recovery'). ...
    (uk.comp.os.linux)
  • Re: Shared databases and improving reliability
    ... For me that's one of the major tasks of a database! ... The mere job of data conversion is rather cumbersome for FMP itself ... After recovery the 700 000 records where available ... The backup solution ...
    (comp.databases.filemaker)
  • Re: Oracle: how to demonstrate successful restore?
    ... > We could always backup the database in tape, but we might NEVER get it ... That's called a black hole backup. ... tape could be bad after leaving there for a while. ... Are you sure that the tape is ripe enough for a recovery ...
    (comp.databases.oracle.server)
  • Re: How can I back up a log-shipped database?
    ... The recovery procedures etc in SQL Server aren't written to handle this scenario, ... > * Change the status of the database to get it out of STANDBY mode (as per your ... > * Back up the database. ... >> You can not backup a database or log that is standby mode ...
    (microsoft.public.sqlserver.server)