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/12/04 

Date: Tue, 12 Oct 2004 07:21:08 -0700

We need to be able to run a mock disaster, which means running the
application against the activated standby database. This requires that the
database be in read/write mode.

What we currently do with the Oracle version of the application is save the
standby database, activate it, run the test, then restore the standby and
continue applying logs. I'd like to be able to do the same thing with the
SQL Server version.

Tommy

"Peter Yeoh" wrote:

> How do you plan to 'test' your standby databases? Would it be an option for
> you to set the standby databases to read-only standby mode (WITH STANDBY =
> '<undo log file>'), and run DBCC CHECKDB / DBCC CHECKTABLE periodically?
>
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
>
> "Tommy" <Tommy@discussions.microsoft.com> wrote in message
> news:FA5D0311-BB11-4020-8CAB-9F5616495A24@microsoft.com...
> > 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: Detaching and Attaching Standby Database
    ... >> Have you read the article in the BOL ... > have a local backup of the standby database (45GB production database is ... > Yesterday I had some kind of disk hiccup during a log restore and the ... > backup and a ton of log restores. ...
    (microsoft.public.sqlserver.server)
  • Re: standby database on standard edition (once again) 10g
    ... I try to follow procedure from Oracle Data Guard Concepts and ... Administration, chapter 3 Creating a Physical Standby Database, ... unfortunetly still dont know how should I create stanby from backup ...
    (comp.databases.oracle.server)
  • Re: Dataguard
    ... automatically for instance i want to delete one month archived log ... from primary and standby database with out RMAN. ... I am not using RMAN at all. ... backup weekly and hot backup daily. ...
    (comp.databases.oracle.misc)
  • Re: Detaching and Attaching Standby Database
    ... >> I need to be able to detach my standby database so I can back it up. ... >> I thought about doing a NET STOP MSSQLSERVER, backup files, and NET ... Yesterday I had some kind of disk hiccup during a log restore and the ...
    (microsoft.public.sqlserver.server)
  • Re: FYI the last command I issued was :-alter database recover managed standby
    ... I have got the same situation in one of my new manual standby database, ... I have scripts that copy the generated archive logs to the remote ... alter database recover managed standby database cancel; ...
    (comp.databases.oracle.server)