Disaster Recovery Options

4 Dec 2008 4:50 PM

Bryan

I am looking for general ideas to explore based on the following requirement:
We want to be able to backup a database or database files (off site or to
tape) that is currently the recipient of Log Shipping without disrupting the
updates being applied to it.
The physical files cannot be copied while processes are still accessing
them. Log Shipping or Database Mirroring cannot resume after the DB is online
and backed up.
I would like to avoid replication, but will entertain it if it is the only
option.
There is no activity on the DR server except for the restore of logs from
the production site.
Any creative ideas are welcome.

--

4 Dec 2008 5:47 PM

dbenoit

Just a thought, and would require a bit of scripting but send the log files
to another location, someplace the tape backup could process from, and when
the tape backup process is complete and verified you can move the files from
the tape backup location to the location that the log shipping process is
going to look for them at.

Again, a bit of scripting and tweaking of the backup process job to include
the file move but it might work.

Just a thought.

--
David

Show quoteHide quote

"Bryan" wrote:

> I am looking for general ideas to explore based on the following requirement:
> We want to be able to backup a database or database files (off site or to
> tape) that is currently the recipient of Log Shipping without disrupting the
> updates being applied to it.
> The physical files cannot be copied while processes are still accessing
> them. Log Shipping or Database Mirroring cannot resume after the DB is online
> and backed up.
> I would like to avoid replication, but will entertain it if it is the only
> option.
> There is no activity on the DR server except for the restore of logs from
> the production site.
> Any creative ideas are welcome.
>
> --
>

4 Dec 2008 6:52 PM

Bryan

I have already had a process that log shipped shipped logs (say that ten
times fast) to a QA server from the DR site, while using that technique of
different folders. But that is not where the problem is. The problem is how
to periodically backup a DR database that is in the state of restoring. If I
copy logs to a tape, then I would still need the initial database SEED and
every log file afterwards. This can be a lot since the logs ship every 15
minutes. I think my best option is to suggest that they grab the offsite
backup from the source and not the DR site. But thanks, these are the ideas I
want to hear.

--

Show quoteHide quote

"dbenoit" wrote:

> Just a thought, and would require a bit of scripting but send the log files
> to another location, someplace the tape backup could process from, and when
> the tape backup process is complete and verified you can move the files from
> the tape backup location to the location that the log shipping process is
> going to look for them at.
>
> Again, a bit of scripting and tweaking of the backup process job to include
> the file move but it might work.
>
> Just a thought.
> --
> David
>
>
> "Bryan" wrote:
>
> > I am looking for general ideas to explore based on the following requirement:
> > We want to be able to backup a database or database files (off site or to
> > tape) that is currently the recipient of Log Shipping without disrupting the
> > updates being applied to it.
> > The physical files cannot be copied while processes are still accessing
> > them. Log Shipping or Database Mirroring cannot resume after the DB is online
> > and backed up.
> > I would like to avoid replication, but will entertain it if it is the only
> > option.
> > There is no activity on the DR server except for the restore of logs from
> > the production site.
> > Any creative ideas are welcome.
> >
> > --
> >

5 Dec 2008 9:13 PM

Paul Ibison

Not a particularly creative idea (:)) but we have a nightly full backup of
the production databases which goes to disk and then to tape, and meanwhile
log shipping continues quite happily. For your situation this is pretty much
equivalent as the log shipped database will at this point in time be in the
same state as the full backup.
HTH,
Paul Ibison

8 Dec 2008 3:20 PM

Bryan

That is a solution that I am recommending. It is much easier to grab the
nightly backup off the production system. However, the requirement is to get
the backup from the DR site and not the production site. The best solution I
came up with so far is to set up a windows task that executes a batch file
nightly. The batch file stops the SQL Server cluster resource, copies the
physical files to the backup location and restarts the cluster resource. Once
the services is restarted, log shipping resumes as normal. This is not pretty
either, but it does get the job done. The only thing I want to add is a check
to see that the services restart on the correct node of the cluster. I am
still interested if there are any other solutions. I appreciate all comments.
Thre batch file looks something like this:

cluster res "SQL Server" /off

xcopy "<source path of data file>" "<destination path of data file>" /y
xcopy "<source path of log file>" "<destination path of log file>" /y

xcopy "<source path shipped transaction log files>" "<destination path of
shipped transaction log files >" /y
cluster res "SQL Server" /on
cluster res "SQL Server Agent" /on

--
Bryan Bitzer MCP
Senior Database Administrator

Show quoteHide quote

"Paul Ibison" wrote:

> Not a particularly creative idea (:)) but we have a nightly full backup of
> the production databases which goes to disk and then to tape, and meanwhile
> log shipping continues quite happily. For your situation this is pretty much
> equivalent as the log shipped database will at this point in time be in the
> same state as the full backup.
> HTH,
> Paul Ibison