--- title: "Back Up the Transaction Log When the Database Is Damaged (SQL Server) | Microsoft Docs" ms.custom: "" ms.date: "06/13/2017" ms.prod: "sql-server-2014" ms.reviewer: "" ms.technology: backup-restore ms.topic: conceptual helpviewer_keywords: - "databases [SQL Server], damaged" - "backing up [SQL Server]. damaged database" - "transaction log backups [SQL Server], damaged databases" ms.assetid: 9b8873cc-df54-4336-ab9b-8f525132c2b0 author: MikeRayMSFT ms.author: mikeray manager: craigg --- # Back Up the Transaction Log When the Database Is Damaged (SQL Server) This topic describes how to back up a transaction log when the database is damaged in [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)] by using [!INCLUDE[ssManStudioFull](../../includes/ssmanstudiofull-md.md)] or [!INCLUDE[tsql](../../includes/tsql-md.md)]. **In This Topic** - **Before you begin:** [Limitations and Restrictions](#Restrictions) [Recommendations](#Recommendations) [Security](#Security) - **To back up the transaction log when the database is damaged, using:** [SQL Server Management Studio](#SSMSProcedure) [Transact-SQL](#TsqlProcedure) ## Before You Begin ### Limitations and Restrictions - The BACKUP statement is not allowed in an explicit or implicit transaction. ### Recommendations - For a database that uses either the full or bulk-logged recovery model, you generally need to back up the tail of the log before beginning to restore the database. You also should back up the tail of the log of the primary database before failing over a log shipping configuration. Restoring the tail-log backup as the final log backup before recovering the database avoids work loss after a failure. For more information about tail-log backups, see [Tail-Log Backups (SQL Server)](tail-log-backups-sql-server.md). ### Security #### Permissions BACKUP DATABASE and BACKUP LOG permissions default to members of the **sysadmin** fixed server role and the **db_owner** and **db_backupoperator** fixed database roles. Ownership and permission problems on the backup device's physical file can interfere with a backup operation. [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] must be able to read and write to the device; the account under which the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] service runs must have write permissions. However, [sp_addumpdevice](/sql/relational-databases/system-stored-procedures/sp-addumpdevice-transact-sql), which adds an entry for a backup device in the system tables, does not check file access permissions. Such problems on the backup device's physical file may not appear until the physical resource is accessed when the backup or restore is attempted. ## Using SQL Server Management Studio #### To back up the tail of the transaction log 1. After connecting to the appropriate instance of the [!INCLUDE[msCoName](../../includes/msconame-md.md)] [!INCLUDE[ssDEnoversion](../../includes/ssdenoversion-md.md)], in Object Explorer, click the server name to expand the server tree. 2. Expand **Databases**, and, depending on the database, either select a user database or expand **System Databases** and select a system database. 3. Right-click the database, point to **Tasks**, and then click **Back Up**. The **Back Up Database** dialog box appears. 4. In the **Database** list box, verify the database name. You can optionally select a different database from the list. 5. Verify that the recovery model is either **FULL** or **BULK_LOGGED**. 6. In the **Backup type** list box, select **Transaction Log**. 7. Leave **Copy Only Backup** deselected. 8. In the **Backup set** area, either accept the default backup set name suggested in the **Name** text box, or enter a different name for the backup set. 9. In the **Description** text box, enter a description for the tail-log backup. 10. Specify when the backup set will expire: - To have the backup set expire after a specific number of days, click **After** (the default option), and enter the number of days after set creation that the set will expire. This value can be from 0 to 99999 days; a value of 0 days means that the backup set will never expire. The default value is set in the **Default backup media retention (in days)** option of the **Server Properties** dialog box (**Database Settings** page). To access this dialog box, right-click the server name in Object Explorer and select properties; then select the **Database Settings** page. - To have the backup set expire on a specific date, click **On**, and enter the date on which the set will expire. 11. Choose the type of backup destination by clicking **Disk** or **Tape**. To select the paths of up to 64 disk or tape drives containing a single media set, click **Add**. The selected paths are displayed in the **Backup to** list box. To remove a backup destination, select it and click **Remove**. To view the contents of a backup destination, select it and click **Contents**. 12. On the **Options** page, select an **Overwrite Media** option, by clicking one of the following: - **Back up to the existing media set** For this option, click either **Append to the existing backup set** or **Overwrite all existing backup sets**. Optionally, select **Check media set name and backup set expiration** to cause the backup operation to verify the date and time at which the media set and backup set expire. Optionally, enter a name in the **Media set name** text box. If no name is specified, a media set with a blank name is created. If you specify a media set name, the media (tape or disk) is checked to see whether the actual name matches the name you enter here. If you leave the media name blank and check the box to check it against the media, success will equal the media name on the media also being blank. - **Back up to a new media set, and erase all existing backup sets** For this option, enter a name in the **New media set name** text box, and, optionally, describe the media set in the **New media set description** text box. For more information about media set options, see [Media Sets, Media Families, and Backup Sets (SQL Server)](media-sets-media-families-and-backup-sets-sql-server.md). 13. In the **Reliability** section, optionally, check: - **Verify backup when finished**. - **Perform checksum before writing to media**. - **Continue on checksum error** For information on checksums, see [Possible Media Errors During Backup and Restore (SQL Server)](possible-media-errors-during-backup-and-restore-sql-server.md). 14. In the **Transaction log** section, check **Back up the tail of the log, and leave database in the restoring state**. This is equivalent to specifying the following [BACKUP](/sql/t-sql/statements/backup-transact-sql) statement: `BACKUP LOG TO WITH NORECOVERY` > [!IMPORTANT] > At restore time, the Restore Database dialog box displays the type of a tail-log backup as **Transaction Log (Copy Only)**. 15. If you are backing up to a tape drive (as specified in the **Destination** section of the **General** page), the **Unload the tape after backup** option is active. Clicking this option activates the **Rewind the tape before unloading** option. 16. [!INCLUDE[ssEnterpriseEd10](../../includes/ssenterpriseed10-md.md)] and later supports [backup compression](backup-compression-sql-server.md). By default, whether a backup is compressed depends on the value of the **backup-compression default** server configuration option. However, regardless of the current server-level default, you can compress a backup by checking **Compress backup**, and you can prevent compression by checking **Do not compress backup**. **To view the current backup compression default** - [View or Configure the backup compression default Server Configuration Option](../../database-engine/configure-windows/view-or-configure-the-backup-compression-default-server-configuration-option.md) ## Using Transact-SQL #### 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. ### Example (Transact-SQL) > [!NOTE] > This example uses the [!INCLUDE[ssSampleDBobject](../../includes/sssampledbobject-md.md)], 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. For more information, see [View or Change the Recovery Model of a Database (SQL Server)](view-or-change-the-recovery-model-of-a-database-sql-server.md). This example backs up the currently active transaction log when a database is damaged and inaccessible, if the transaction log is undamaged and accessible. ```scr BACKUP LOG AdventureWorks2012 TO MyAdvWorks_FullRM_log1 WITH NO_TRUNCATE; GO ``` ## See Also [Restore a Transaction Log Backup (SQL Server)](restore-a-transaction-log-backup-sql-server.md) [Restore a SQL Server Database to a Point in Time (Full Recovery Model)](restore-a-sql-server-database-to-a-point-in-time-full-recovery-model.md) [Back Up Database (Backup Options Page)](back-up-database-backup-options-page.md) [Back Up Database (General Page)](../../integration-services/general-page-of-integration-services-designers-options.md) [Apply Transaction Log Backups (SQL Server)](transaction-log-backups-sql-server.md) [BACKUP (Transact-SQL)](/sql/t-sql/statements/backup-transact-sql) [File Restores (Simple Recovery Model)](file-restores-simple-recovery-model.md) [File Restores (Full Recovery Model)](file-restores-full-recovery-model.md)