BACKUP LOG {database_name | @database_name_var}{[WITH { NO_LOG | TRUNCATE_ONLY }]}|{TO <backup_device> [,...n]
[WITH [BLOCKSIZE = {blocksize | @blocksize_variable}][[,] DESCRIPTION = {text | @text_variable}][[,] EXPIREDATE = {date | @date_var} | RETAINDAYS = {days | @days_var}][[,] FORMAT | NOFORMAT][[,] {INIT | NOINIT}][[,] MEDIADESCRIPTION = {text | @text_variable}][[,] MEDIANAME = {media_name | @media_name_variable}][[,] [NAME = {backup_set_name | @backup_set_name_var}][[,] NO_TRUNCATE][[,] {NOSKIP | SKIP}][[,] {NOUNLOAD | UNLOAD}][[,] [RESTART][[,] STATS [= percentage]]]}

The command to backup a transaction log is as follows with the myriad options the same as described in the options table previously.

backup log <database> to <device> with <options>

You will find three additional options in the backup transaction command that have not been discussed previously. These options are truncate_only, no_log, and no_truncate.. These options have been provided due to the unique nature of the transaction log.

There are times when you do not want to store or retain the transaction log for recovery purposes. This can occur in databases where lareg transactions have been processed but recreating the modifications can be accomplished more quickly than the restore of the transaction log. This is also an extremely useful option to use just before a full database backup. During a full database backup, the inactive portion of the transaction log is also backed up and then applied if the need to restore arises. To speed up the backup of the database, most administrators will truncate the transaction log using the truncate_only option immediately before a full backup. It is also used very frequently on development servers. When you use the truncate_only option, a backup device is not specified since the inactive portion of the transaction log is discarded and not backed up.

There are times when the transaction log can fill up and no further entries are able to be written to the transaction log. The truncate_only option will write an entry into the transaction log that marks the point where it was truncated. Since the transaction log is full, this command will fail. The no_truncate provides the same funcationality as truncate_only, except it does not write an entry into the transaction log. Just like in the truncate_only option, a backup device is not specified since the inactive portion of the transaction log is removed.

The no_truncate option can be used in several cases, but normally has one use. If a database becomes corrupted a restore of your most recent backup is necessary. However, a number of transaction could have been processed and would be lost. As long as you can still connect to the database and the transaction log is readable, the no_truncate option allows you to backup the inactive part of the transaction log to the point of corruption. This can give you the ability to bring the database back to the last state it was in without any data loss.

The difference between no_log and truncate_only in previous versions of SQL Server caused great confusion among many DBAs. Because of this, Microsoft changed the behavior of the truncate_only option in version 7.0. Both options now function the same and are completely interchangeable.

The no_truncate option does not remove the inactive portion of the transaction log and does accept a backup device. Because of this feature, the no_truncate option can be used to capture the entire transaction log in a single file. This can streamline the restore process, because only one transaction log needs to be restored to bring the database up to date. The no_truncate option is rarely used for this purpose and has been superceded by differential backups when used in this manner.

Backup log to an explicit disk device and begin a new backup set.

backup log pubs to tranbackup with format

Backup log to an undefined network disk device and overwrite the existing backups

backup log pubs to disk = ‘\\myserver\tranbackups\pubs.bak’ with init

Backup log to a tape device and leave the tape in the drive

backup log pubs to tapebackup with nounload

Backup a log to a tape device using a variable

backup log @mydb to tapebackup with format, unload

Backup Overview 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32