NO_TRUNCATE

Question

Post reply

NO_TRUNCATE

Imke Cronje

SSCarpal Tunnel

Points: 4826
More actions
July 20, 2007 at 7:24 am

#173855

Hi Guys,
Just need some advice. When is it appropriate to use the NO_TRUNCATE in a backup statement. Can I use it if my database is in simple recovery mode?
Please advise.
IC

Viewing 8 posts - 1 through 7 (of 7 total)

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

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

I would like to put my penny here.

NO_TRUNCATE means no truncate. No log is truncated in the process of log backup.

When a database is in the SIMPLE mode. No inactive will be kept. I cannot see why you need this statement.

Deepak-405225 SSCertifiable Points: 6120 More actions · Answer 2

using NO_TRUNCATE option will not truncate the transaction log and is used in situations where db is damaged but in simple recovery model all the inactive transactions will be flushed out.......

[font="Verdana"]- Deepak[/font]

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 3

It is used to backup the currently active transaction log.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

george sibbald2 SSC Enthusiast Points: 155 More actions · Answer 4

you would use the no_truncate option if you were trying to recover a damaged database where the log file was still intact. The no_truncate option would backup the tran log for you without removing the comitted transactions from the log. To recover your database right up to the point it was lost you would then restore the last full backup, the last differential (if there is one), transaction log backups in order and finally the tran log backup produced with the no_truncate option, ensuring you lose minimum data.

this option would not be relevant if the database was in simple recovery mode.

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

I've been trying to find out about NO_TRUNCATE for a while as there is something I fundementally don't understand about it.

If you are backing up the active portion of the log using this option, with a view to using this backup to restore WITH_RECOVERY, why do you not want the log to be truncated, especially seeing as only the inactive portion of the log can ever be truncated? Why do you need to keep old inactive logs if you are backing them up????

ramankumar_04 SSC Enthusiast Points: 166 More actions · Answer 6

Well Paul...i am new to SQL and trying to learn these days. but i think i see answer to your question - why no_truncate keep the record of old logs - because if we have multiple log backups with no_truncate option then after taking the restore of full backup, we can apply the last backup taken with no_truncate option. this will give you all the data till that time. So simply restore the full backup and last log backup taken with no_truncate option and that's all. I believe this is the reason no_truncate keeps all the inactive logs too.

For detail please refer - http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp

To Paul/Everyone - please let me know if i am wrong.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Please note: 4 year old thread.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass