SQLServer 7 Truncate log with checkpoint

#11

On Tue, 27 Oct 2009 22:50:46 +0000 (UTC), Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

Quote:

bob (startatbob_clegg (AT) cutthis (DOT) adriley.co.nz) writes:
I assumed that my log backup command (executes every 30 minutes)

BACKUP Log Meteror
TO DISK = 'H:\MeterorTrans.bck'

was simply appending the transaction logs to the MeterorTrans.bck file
therefore all logs being in order and available from this file.

My full backup job (daily)

BACKUP DATABASE Meteror
TO DISK = 'H:\MeterorFull.bck'
WITH FORMAT ,
NAME = 'Meteror Full'

is overwriting its target bck file each time.

So my assumption (which appears wrong) is that full bck is always
occuring in the 'middle' of a contiguous series of transaction logs
which have been appended, in order, to the MeterorTrans.bck

No, that assumption is correct. With the disclaimer that SQL 7 is the
SQL version that I've given the least attention.

I reviewed the thread, and you said two things: 1) you are restoring
to another server 2) you are using Enterprise Manager. EM may read
from msdb, and if you are another server, it is not going to work
out.

Why not try to RESTORE HEADERONLY, and then try to apply all those
logs, or at least the logs that are the most recent.

I guess what I am looking for is simply:
1) The best practice for setting up a job or jobs for having a
daily full backup and transaction logs stored every 30 minutes that
can be used to roll forward.

I don't think overwriting the full back up everyday is a good idea,
unless you copy the backup file somewhere else. That last backup
may prove to be bad. And what if the disk goes south while the backup
is running. Infinitely appending to the log device also impractical.

You could use some dynamic SQL and add datename (WEEKDAY, getdate()) to
the filename of the backup devices, and also a mechanism to clear out
the log-file backups when they are six days old.

Hi Erland,
Things are in a state of flux here and I think that is contributing to
the problems.
You have confirmed how the Full log / Transaction log scenario works
and I'll keep experimenting until I get it happening.
Thanks
for your efforts on this.
regards
Bob

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SQLServer 7 Truncate log with checkpoint

microsoft.public.sqlserver.setup microsoft.public.sqlserver.setup

Re: SQLServer 7 Truncate log with checkpoint - 10-28-2009 , 12:02 PM