How to create a backup of the currently active transaction log (Transact-SQL)

To create a backup of the currently active transaction log

Important The transaction log should not be backed up:

• If the trunc. log on chkpt. database option is set to TRUE; create a database or differential database backup instead.
• If any nonlogged operations have occurred in the database since the last database backup was created. Create a database or differential database backup instead.
• Until a database backup has been created, because the transaction log contains the changes made to the database after the last database backup was created.
• If the transaction log has been truncated, unless a database or differential database backup is created after the transaction log truncation occurs.
• If any files are added or deleted from the database; a database backup should be immediately created instead.

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 to back up the transaction log without truncating the inactive part of the transaction log.

     Allows the active part of the transaction log to be backed up even if the database is inaccessible, provided that the transaction log file(s) 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 using media for the first time to completely 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 using the FORMAT or INIT clauses of the BACKUP statement as this will destroy any backups previously stored on the backup media.

Examples

This example backs up the currently active transaction log for the MyNwind database even though MyNwind has been damaged and is inaccessible. The transaction log, however, is undamaged and accessible.

BACKUP LOG MyNwind

    TO MyNwind_log1

    WITH NO_TRUNCATE

GO

See Also