• Article

How to: Back Up the Transaction Log When the Database Is Damaged (Transact-SQL)

This topic explains how to back up the transaction log when the database is damaged.

To create a backup of the currently active transaction log

  1. Execute the BACKUP LOG statement to back up the currently active transaction log, specifying:

    • The name of the database to which the transaction log to back up belongs.

    • The backup device where the transaction log backup will be written.

    • The NO_TRUNCATE clause.

      This clause allows the active part of the transaction log to be backed up even if the database is inaccessible, provided that the transaction log file is accessible and undamaged.

  2. Optionally, specify:

    • The INIT clause to overwrite the backup media, and write the backup as the first file on the backup media. If no existing media header exists, one is automatically written.

    • The SKIP and INIT clauses to overwrite the backup media, even if there are either backups on the backup media that have not yet expired, or the media name does not match the name on the backup media.

    • The FORMAT clause, when you are using media for the first time, to initialize the backup media and rewrite any existing media header.

      The INIT clause is not required if the FORMAT clause is specified.

      Important

      Use extreme caution when you are using the FORMAT or INIT clauses of the BACKUP statement as this will destroy any backups previously stored on the backup media.

Example

Important

The MyAdvWorks_FullRM database is a copy of AdventureWorks2008R2, which uses the simple recovery model. To permit log backups, before taking a full database backup, the database was set to use the full recovery model, using ALTER DATABASE MyAdvWorks_FullRM SET RECOVERY FULL.

This example backs up the currently active transaction log for the MyAdvWorks_FullRM database even though MyAdvWorks_FullRM has been damaged and is inaccessible. However, the transaction log is undamaged and accessible:

BACKUP LOG MyAdvWorks_FullRM
   TO MyAdvWorks_FullRM_log1
   WITH NO_TRUNCATE;
GO