Re: How to backup a standby database

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Tommy (Tommy_at_discussions.microsoft.com)
Date: 10/11/04 

Date: Mon, 11 Oct 2004 11:57:04 -0700

No, I don't have the backups. My standby environment is remote, so I can't
readily access production from standby or vice versa. I initialized the
standby environment from a tape backup of the production environment, and
then I transmit logs using an encrypted secure shell internet transfer.
Periodically I want to activate my standby database and test my disaster
recovery environment. Afterwards, I want to go back to standby mode, and
it's not feasible to have hundreds of sites send new tapes to reinitialize
their standby databases. The only other option is to save my original
production backup and all subsequent log backups in order to restore the
standby environment, but this could span months and thousands of logs.

I tried detaching the database, but when I re-attach it SQL Server recovers
it so I can't apply any more logs. The only other solution I can think of is
to shutdown the SQL Server, make copies of the data files, activate the
standby database, create a new dummy database, change the dummy database
status to "Loading", stop the SQL Server, replace the dummy database files
with the standby database files, then restart SQL Server. What a kluge! But
I don't know what else to do if SQL Server won't let me create a backup.

Tommy

"Keith Kratochvil" wrote:

> You cannot backup a database that is in standby mode. You already have the
> necessary backups. They are from your production database!
>
> --
> Keith
>
>
> "Tommy" <ziggy.971485@bloglines.com> wrote in message
> news:1f1201c4afad$7df77970$a501280a@phx.gbl...
> > I have a remote standby database that I use for disaster
> > recovery, and I apply logs from the production environment
> > on an hourly basis. I would like to backup this standby
> > database, but when I try I get an error 927 - "Database
> > cannot be opened - it is in the middle of a restore".
> >
> > So my question is how can I backup this standby
> > environment? I don't want to activate the database
> > because I will no longer be able to apply logs. I tried
> > opening the database in read only mode, but I can't backup
> > then either.
> >
> > Thanks,
> >
> > Tommy
>
>

Relevant Pages

  • Re: Stanby cannot be moved forward
    ... Recovered and opened read only to acertain the validity of backup. ... Then this isn't a proper backup for standby instantiation. ... suitable to create a standby database: ... it won't recover by that mechanism. ...
    (comp.databases.oracle.server)
  • Re: How to backup a standby database
    ... You cannot backup a database that is in standby mode. ... "Tommy" wrote in message ...
    (microsoft.public.sqlserver.server)
  • Re: How to backup a standby database
    ... Why not back up the production/primary database using the SQL Backup command. ... > readily access production from standby or vice versa. ...
    (microsoft.public.sqlserver.server)
  • Re: How to backup a standby database
    ... How do you plan to 'test' your standby databases? ... Need smaller SQL2K backup files? ... My standby environment is remote, ... > I tried detaching the database, but when I re-attach it SQL Server ...
    (microsoft.public.sqlserver.server)
  • Re: Copying Backup of 32-bit Win2000 10g Database into a 64 Bit Win2003 Server
    ... 2000 environment. ... source database, the RMAN "CONVERT DATABASE" command can be used: ... The source database is from a Windows 2000 32-bit environment. ... I am trying to copy a database backup from February 2010; ...
    (comp.databases.oracle.server)