Re: TDP SQL Transaction log not truncating

2004-04-07 09:15:54
Subject: Re: TDP SQL Transaction log not truncating
From: Del Hoobler <hoobler AT US.IBM DOT COM>
Date: Wed, 7 Apr 2004 09:15:24 -0400

I have a few comments:

RESTORE VERIFYONLY checks to see that the backup set is complete
and that all volumes are readable.  RESTORE VERIFYONLY does NOT
check the structure of the data on the volumes.
I always wondered how this is a "verification" of a restore
if you don't really restore the data.

Many of our customers accomplish a true "verification" by restoring
the database to an alternate name which goes further than
RESTORE VERIFYONLY in verifying the backup. It makes sure that the
backup is complete... but also truly verifies that it can be restored
and recovered cleanly. Granted, it does take some additional resource,
but if you need a true "verification" of a restore to prove
the validity of the backup, nothing does that like a real restore.

There are not any current plans for a RESTORE VERIFYONLY function.
If alternate database restore does not meet your needs for some reason,
please submit a requirement through your IBM representative.




"ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU> wrote on 04/07/2004
12:33:44 AM:

> Is your DBA talking about shrinking the log file or truncating the log?
> thing to look out for here is the fact that these are two different
> and lots of SQL people can get confused about this.  Truncating a SQL
> log is not the same as shrinking the physical file that houses the log
> (which is a logical construct, not a physical construct).  When a log
> (backup log dbname to ...) is run, the log is truncated (as long as
> NO_TRUNCATE or NO_LOG is not specified).  This means that space in the
> file that is no longer needed for crash recovery is available for reuse.
> does not, however, mean that the log file will shrink even one byte.  To
> physically shrink the log file, you need to run a command like DBCC
> Good information about this is available in the SQL Server Books Online
> (sp3) in an article entitled "Shrinking the Transaction Log."  This is
> the same article that is online at, by the way.
> It may be that everybody at your shop is already clear on this, but I
> thought it was worth pointing out just in case.  And by the way, TSM
> does standard SQL backup calls - your DBAs can see this if they watch
> sessions in the SQL Profiler.  The biggest problem with the TDP SQL
> and in my opinion it is a fairly glaring one, is that they give you no
> to run RESTORE VERIFYONLY.  This is a nearly crippling problem with
> to verifying backups in some environments - does anybody know if there
> any plans to add this functionality in the future?

<Prev in Thread] Current Thread [Next in Thread>