Hi,
Old but important database, Transaction Log has grown quite large.
Somewhere along the line we have lost the plot on how to correctly
truncate the log.
Nooo, we are not proper DBA's, first to admit it.
So...
I want to be in the situation that when a full back up is done the
transaction log is truncated.

I have set the option 'Truncate on Checkpoint' on in the database
options tab.
I also want to save transaction logs every 20 minutes so that I can
roll forward to within 20 minutes of now in the case of a disaster.

Is there a whitepaper or something that shows how to safely configure
SQLServer 7 to do this?

thanks
Bob

On Oct 19, 7:03*pm, bob <startatbob_cl... (AT) cutthis (DOT) adriley.co.nz>
wrote:
When you enable the 'Truncate on Checkpoint' option, it takes away
your ability to make transaction log backups. Everytime the database
checkpoints it wipes out any committed transactions in the transaction
log, so your backup (if you could take on) would be missing
transactions. If you disable this option and take regular transaction
log backups, like you are saying, it should manage the size of your
transaction log. Of course you also need to take regular full backups
too (daily). In the event of a failure you would need to restore the
full backup with no recovery option and then restore the appropriate
transaction log backups.

You should have the 7.0 books online installed. If you can't find
that you could use the 2000 books online. Truncate on Checkpoint is
called "simple recovery model" the concepts are mostly the saem.

SQL Server 2000 backup and recover books online
http://msdn.microsoft.com/en-us/libr...5(SQL.80).aspx


Hope this helps you get started.

On Mon, 19 Oct 2009 19:10:27 -0700 (PDT), Dave <treschaud33 (AT) yahoo (DOT) com>
wrote:

Hi Dave,
Thanks.
That's cleared it up.
regards
Bob

Hi Dave,
Thank you.
I now have two jobs

1) Database backing up with
BACKUP DATABASE Meteror
TO DISK = 'H:\MeterorFull.bck'
WITH FORMAT ,
NAME = 'Meteror Full'
This job runs every morning at 10.00am


2) Transaction logs being backed up with
BACKUP Log Meteror
TO DISK = 'H:\MeterorTrans.bck'
This job goes every 30 minutes.

H Drive is on another box on the LAN

I tried restoring to another server but am having problems with the
Transaction Log LSN.

The database I restore as 'non operational allow more transaction
logs'
When I tried to restore the transaction log bck file Enterprise
manager complained that the required LSN nnnnnn was missing because
the transaction logs started too late.
I then did a manual backup, figuring that the transaction log was
already there so it can't complain about it starting too late.
When I tried the restore this time it complained that the transaction
log terminating too early and ask for a later log set.

I can't see what I am doing wrong. Any ideas?

thanks
Bob






On Mon, 19 Oct 2009 19:10:27 -0700 (PDT), Dave <treschaud33 (AT) yahoo (DOT) com>
wrote:

Hi Dave,
Another point I am restoring onto an SQL server 2000 platform.

On Mon, 19 Oct 2009 19:10:27 -0700 (PDT), Dave <treschaud33 (AT) yahoo (DOT) com>
wrote:

bob (startatbob_clegg (AT) cutthis (DOT) adriley.co.nz) writes:
Since you've restored a full backup which is later than the transaction
log backups, the latter are of course of no use.

You could take a new log backup and restore that.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Sat, 24 Oct 2009 21:59:43 +0000 (UTC), Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

thanks
for your reply.
So what is the correct way to set up if you want to take a full backup
daily and have transaction logs backed up every say 20 minutes?
To rehash;
Currently I have two separate jobs.
The full bck going off daily and the translog bck going off every 20
minutes.

thanks
Bob

bob (startatbob_clegg (AT) cutthis (DOT) adriley.co.nz) writes:
If you don't know which backup is the last in the chain, restore all
dumps with NORECOVERY. Then when you are done, do

RESTORE DATABASE db WITH RECOVERY



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Mon, 26 Oct 2009 22:49:22 +0000 (UTC), Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

Hi Erland,
Sorry but I don't think I made myself clear.
it is not a case of not knowing which is the latest backup, it is more
likely I don't properly understand the process.

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

I therefore expected to:
1) Restore MeterorFull.bck
2) Roll forward using MeterorTrans.bck

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.
2) The correct way of doing a restore given that 1) is implemented.

regards
Bob

bob (startatbob_clegg (AT) cutthis (DOT) adriley.co.nz) writes:
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 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.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx