This post will address the issue of backing up the Transaction Log in the unfortunate eventuality when your SQL Server Database is damaged.

We will offer details regarding how you can do your Transaction Log backup with either T-SQL queries or by using the graphical interface of SSMS (SQL Server Management Studio) in a step-by-step example.

Backup the Transaction Log when the Database is damaged using SQL Server Management Studio (SSMS):

• You begin by connecting to the appropriate instance of SQL Server Database Engine, in the Object Explorer window double-click on the server name in order to expand the server tree.
• The next step is to expand the folder Databases and select the database for which you want to backup the transaction log.
• Right-click the database, go to Tasks, and then click Back Up, which will bring up the Back Up Database dialog box.
• In the list box that corresponds to Database option, verify the database name.
• Check if the recovery model selected is FULL or BULK_LOGGED.
• In the list box Backup type, select Transaction Log.
  

  

• Leave Copy Only DESELECTED.
• In the Backup set area, you can choose to accept the backup set name which is suggested in the Name text box, or you can choose to enter a different name for the backup set.
• In the next textbox, Description, enter a description for the tail-log backup.
• The next step is to specify when the backup set will expire:
  • In order for the backup set to expire after a specific number of days, click After and then enter a number of days after set creation that the set will expire. This value can be anywhere in the range of 0 to 99999 days.
  • In order for the backup to NEVER expire, leave the default value of 0.
  • To have the backup expire on a specific date, choose the On option and enter the date you want the backup to expire on.
• You will then have to select the type of backup destination by clicking either Disk or Tape.
  

  

• In order to select the paths of up to 64 disk or tape drives containing a single media set, click Add.
• Select the path and the filename you want to backup to.
  

  

• Remove the backup from the list by selecting it and pressing Remove
• In the Options page (top left side menu), choose an Overwrite Media option (which you consider might be the best option for your case), by choosing one of the following:
  • Backup to the existing media set.
  • Backup to a new media set, and erase all existing backup sets.
    

    

• In the Reliability section, you can opt for a few checkups after the backup is finished.
  • Verify backup when finished
  • Perform checksum before writing to media
  • Continue on checksum error
• In case you decide to use reliability options, we recommend checking the first two options as they are helpful in determining and maintaining the integrity
• For more information on checksums please review TechNet’s page
• In the Transaction Log section, check the button that corresponds to Back up the tail log, and leave the database in the restoring state
• This option is equivalent to the following BACKUP statement:

  BACKUP LOG < database_name > TO < backup_device > WITH NORECOVERY

• In case you are backing up to a tape drive, the Unload the tape after backup option is available. By clicking this option you will activate the Rewind the tape before unloading option.
• The last section on this page is Compression. As we have already mentioned up to this point, SQL Server 2008 and later support backup compression.
• By default, whether a backup is compressed depends on the default value of the server configuration backup-compression option. In case you want to configure backup compression for your backups, please take a look at this post.
• Although, you can override the current server-level default, and you can compress a backup during this operation by selecting Compress backup,  or you can prevent compression of your backup by selecting Do not compress backup.
  

  

Backup the Transaction Log when the Database is damaged using T-SQL:

You can do this by executing a BACKUP LOG statement while specifying:

• the name of the database to which the transaction log to back up belongs
• the backup device where the transaction log 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).

We will use as an example the AdventureWorks database for our query:

BACKUP LOG myDatabase TO myDatabase_FullRM_log1 WITH NO_TRUNCATE;