Backup the Transaction Log When the Database is Damaged

Backup the Transaction Log When the Database is Damaged
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.

      Backup the Transaction Log When the Database is Damaged

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

      Backup the Transaction Log When the Database is Damaged

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

      Backup the Transaction Log When the Database is Damaged

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

      Backup the Transaction Log When the Database is DamagedBackup the Transaction Log When the Database is Damaged

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

      Backup the Transaction Log When the Database is Damaged

    • 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 integrity
    • For more information on checksums please review technet’s page 
    • In the Transaction Log section, check the button that correspons to Back up the tail log, and leave the database in the restoring state

      Backup the Transaction Log When the Database is Damaged

    • This option is equivalent with 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, wheter 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

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 transasction 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;

About Radu Gheorghiu

Passionate SQL Developer on the journey of trying to become an expert in all things Data (storage, manipulation, gathering etc.) Information is power, and information can only be obtained from data. Thus, in order to harness the power of information, you must be a master of Data.

Leave a Reply

Your email address will not be published. Required fields are marked *