Back Up the Transaction Log When the Database Is Damaged (SQL Server)

Article
02/28/2023

Applies to: SQL Server

This topic describes how to back up a transaction log when the database is damaged in SQL Server by using SQL Server Management Studio or Transact-SQL.

In This Topic

Before you begin:

Limitations and Restrictions

Recommendations

Security
To back up the transaction log when the database is damaged, using:

SQL Server Management Studio

Transact-SQL

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).

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. SQL Server must be able to read and write to the device; the account under which the SQL Server service runs must have write permissions. However, sp_addumpdevice, 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

After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.
Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.
Right-click the database, point to Tasks, and then click Back Up. The Back Up Database dialog box appears.
In the Database list box, verify the database name. You can optionally select a different database from the list.
Verify that the recovery model is either FULL or BULK_LOGGED.
In the Backup type list box, select Transaction Log.
Leave Copy Only Backup deselected.
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.
In the Description text box, enter a description for the tail-log backup.
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.
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.
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).
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).
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 statement:

BACKUP LOG <database_name> TO <backup_device> WITH NORECOVERY

Important

At restore time, the Restore Database dialog box displays the type of a tail-log backup as Transaction Log (Copy Only).
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.
SQL Server 2008 (10.0.x) Enterprise and later supports backup compression. 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

Using Transact-SQL

To create a backup of the currently active transaction log

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 AdventureWorks2022, 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).

This example backs up the currently active transaction log when a database is damaged and inaccessible, if the transaction log is undamaged and accessible.

BACKUP LOG AdventureWorks2022  
   TO MyAdvWorks_FullRM_log1  
   WITH NO_TRUNCATE;  
GO

Share via