« SQL Server Differential Backup
SQL Server Database Backup Options »

SQL Server Transaction Log Backup

Transaction Log Backup includes all log records that were not backed up in a previous log backup.

Transaction Log Backup

  1. It will backup transaction log only.
  2. Its backed up from the last successful full backup, differential backup, or log backups
  3. Transaction logs are backed up for two reasons: (to ensure log is truncated periodically and to reduce amount of possible data loss in case of disk failure )
  4. Transaction logs can be backed up as part of full/transaction log backup strategy, or as parto f full/differential/transaction backup strategy.
  5. Transaction logs can be backed up when recovery model is et to full or bulk logged.
  6. Transaction log backups are not available for simple recovery model.
  7. The basic syntax to perform a transaction log backup follows. It will fail if a full backup doesn’t exist.
  8. Simple recovery mode causes the transaction log to be truncated prior to backup
--First need to change the recovery model from simple to full
ALTER DATABASE [AdventureWorks] SET RECOVERY FULL WITH NO_WAIT

-- A Full database backup must be taken after the recovery mode
-- has been changed in order set a base for future tlog backups.
--The Following full backup utilizes two devices on the same drive.
BACKUP DATABASE [AdventureWorks]
TO DISK = N’C:\backup\AdventureWorks_Full_Dev1.bak’,
DISK = N’C:\backup\AdventureWorks_Full_Dev2.bak’
WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10

--Transaction log backups can be taken now that a base has been established
--The following tlog backup is written to a single file
BACKUP LOG [AdventureWorks]
TO DISK = N’C:\backup\log\AdventureWorks_FirstAfterFull.trn’
WITH NOFORMAT, INIT, NAME = N’AdventureWorks-Transaction Log Backup’,
SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM

NO_TRUNCATE Options

  1. You can use NO_TRUNCATE option when the log is available but database is not.
  2. Its function opposite to NO_LOG and TRUNCATE_ONLY.
  3. You can BACKUP LOG with NO_TRUNCATE option, log is backed up but checkpoint is not run.
  4. Tthis new log backup can restore as well, enableing recovery to time of failure.
  5. Only transaction lost rare those were not yet commited

NORECOVERY | STAND = undo_file_name Options

  1. NoRecovery option cause tail of the log to be backup up and leave database in restoring state
  2. The tail of the log is active portion of the log that contains transactions that have not yet been backup up.
  3. Typically, NoRecovery option is used with NO_TRUNCATE option to retain contents of the log.
  4. STANDBY option also backs up tail of log, but it leaves the database in readonly/standby state.
  5. The read-only state allows inquery on the database and allow additional transaction logs to be applied to database as well.
  6. STANDBY option proces same results as executing BACKUP LOG WITH NORECOVERY followed by a RESTORE WITH STANDBY
WITH [NO_TRUNCATE],
NORECOVERY | STANDBY=undo_file_name
OR
WITH NO_LOG | TRUNCATE_ONLY

NO_LOG | TRUNCATE_ONLY

  1. NO_LOG | TRUNCATE_ONLY options are synonymous.
  2. They cause inactive portion of transaction log to be discarded ( be carefull in useing it )
  3. These options are n o more in future SQL Server

Tags:

This entry was posted on Saturday, January 12th, 2008 at 4:20 am and is filed under Backup Database, SQL Server. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply