WITH NOINIT vs TRUNCATE_ONLY

Question

WITH NOINIT vs TRUNCATE_ONLY

Paul Kalinski

Old Hand

Points: 323
More actions
June 2, 2005 at 1:44 am

#110891

Hi
Is there a fundamental/important difference between these two?
Thanks for any replies
Paul

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

NOINIT is a BACKUP argument that preserves any existing backup sets in a backup device. It's opposite is INIT which will overwrite any existing backup sets.

TRUNCATE_ONLY is a BACKUP LOG argument that removes the inactive part of a transaction log without making a backup copy of it. This frees space in the log.

You can find more detailed descriptions under BACKUP in BooksOnLine.

Greg

Paul Kalinski Old Hand Points: 323 More actions · Answer 2

Thanks Greg

That makes sense, now the follow up question.

I'm backing up user databases, although most of them are DEV, have a full BK running weekly, differentials running daily OBH, and log BK hourly.

The log BK is set WITH NOINIT and NO_TRUNCATE, but at some point the BK device needs to be overwritten, and the inactive part of the log truncated.

I have read BOL, books (like M. Spenik's et al Survival Guide) and can't get a straight answer re when and how is the best time/way to do this, although I can think of many ways myself. What would you suggest?

Thanks again

Paul

Greg Charles SSC-Forever Points: 45403 More actions · Answer 3

Hi Paul,

The Tlog is truncated every time a log backup is taken when a database recovery model is “Full”.

So, you shouldn’t need to use TRUNCATE_ONLY. In fact, BOL advises not to use it because it breaks the log backup chain.

As for initializing the backup device, you can build that into your scheduled backup plan. With your current plan of weekly full, daily differential, and hourly log backups, you could keep a week’s worth of backups in a device and copy it to disk before initializing. It would look something like this for any given database:

Full backup 06/01/2005 5:00 AM (WITH INIT) weekly full

Log backup 06/01/2005 6:00 AM (WITH NOINIT) hourly log

Log backup 06/01/2005 7:00 AM (WITH NOINIT) hourly log

Log backup 06/01/2005 8:00 AM (WITH NOINIT) hourly log

.

differential backup 06/01/2005 7:00 PM (WITH NOINIT) daily diff

Log backup 06/01/2005 8:00 PM (WITH NOINIT) hourly log

.

Log backup 06/07/2005 4:00 AM (WITH NOINIT) houly log (last backup for Week)

We use this scheme with a set of scheduled backup jobs and two devices for each database. One device holds backups for Saturday thru Tuesday and the other holds backups for Wednesday thru Friday.

Hope this helps.

Greg

Paul Kalinski Old Hand Points: 323 More actions · Answer 4

Thanks a lot Greg

I realise what you've suggested is repeated - probably in great detail - in BOL, but your version in plain English is finally sticking in my virginal DBA brain.

Cheers

Paul