logging and vunerability.

logging and vunerability.

Post by Ben Brugm » Sat, 06 Jul 2002 12:17:13



Problems with understanding the safety of logging and
the backup of logging.

Suppose:
One single machine. With a one set of disks which are mirrored.
(hardware raid).
Regular full backups. (Say each 24 hours)
Very regular log backups. (Say each 10 minutes).

Suppose something goes wrong with the backup of the
log. (The backup file is lost or corrupted).

If the log was truncated then the system is vunerable until the
next full backup, or incremental backup. Is this true ?

If the log was not truncated, can this log be used again to
backup this log ? As far as I understand you cann't do that.
Or is this possible ?

Is it possible to have logging taking place real time on two
very different locations, to protect against callamities ?

(The issue here is dataloss, and although performance and
availability are important that is not the issue here).

Thanks for your thoughts,

ben brugman.
Ben Brugman

 
 
 

logging and vunerability.

Post by Russell Field » Sat, 06 Jul 2002 20:36:21


Ben

You might want to review the SQL Server 2000 Operations Guide:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/pr...
ol/sql/maintain/operate/opsguide/sqlops4.asp

Quote:> Suppose something goes wrong with the backup of the
> log. (The backup file is lost or corrupted).
> If the log was truncated then the system is vunerable until the
> next full backup, or incremental backup. Is this true ?

Sadly true.

Quote:> If the log was not truncated, can this log be used again to
> backup this log ? As far as I understand you cann't do that.
> Or is this possible ?

Hmmm, never tried, but the manual suggests that it is not possible.  (You
could experiment and become the expert.)   The caution:  If this works (as
neither of us really thinks it will) then the log will continue to grow,
eventually leading to disk space problems.

Quote:> Is it possible to have logging taking place real time on two
> very different locations, to protect against callamities ?

Log Shipping moves a log to another server.  Even if you don't want to do
this, the concept of copying the log file to another location is good.

Quote:> (The issue here is dataloss, and although performance and
> availability are important that is not the issue here).

Suggestion:
Backup your logs to disk.
Copy the backups to another disk.
Backup one of these copies to tape.

 Russell Fields

 
 
 

logging and vunerability.

Post by ben brugma » Sun, 07 Jul 2002 18:09:44



Quote:> Ben

> You might want to review the SQL Server 2000 Operations Guide:

http://www.microsoft.com/technet/treeview/default.asp?url=/technet/pr...
ol/sql/maintain/operate/opsguide/sqlops4.asp

I had a look overthere, but this issue is not handled there.

Problem is :
Even if the log is not truncated, after a backup the log is not usable
anymore. Although there is a backup of the transactinal log, it is not
really a backup because it is a 'SINGLE COPY' of the information
which was in the log. Because the part which was backupped is not
usable anymore. (Can't be used for another backup, can't be used for
recovery.) So actualy the so called log 'backup, is now the ONLY copy
of this data. If it was called 'SINGLE COPY' and not 'BACKUP' more people
would be worried ?

For me the 'SINGLE COPY' and ONLY copy rings alarm bells. If something
hapens to this single copy the database becomes very funarable.

Log shipping and making extra copies of the backup's only solve this problem
partly.

ben brugman.

Quote:

>  Russell Fields