This is a step-by-step tutorial on how to backup the Tail of the Transaction Log using the graphical interface of SSMS.
In order to find more information about the Tail of the Transaction Log, please read our article, which offers more details regarding this type of backup, here.
How to backup the Tail of the Transaction Log with SSMS
The first step would be to connect to the appropriate instance of SQL Server Database Engine and then in the Object Explorer window you have to double click the server name which will expand the server tree.
The next step would be to expand the Databases folder and then you can either choose a User Database or a System Database.
The next step would be to right-click the database you want the backup to be made for and select Tasks and then click Back Up.
In the Back Up Database dialog that appears, please make sure that in the Database list box the database you want to back up is selected. At this point, you can opt to select a different database for which you will do the tail backup.
Verify that the selected recovery model is selected is FULL or BULK_LOGGED.
Select Transaction Log in the Backup type list box.
Make sure that the Copy Only Backup tickbox remains deselected.
In the Backup list section, you can leave or change the name of the backup and optionally add a description for the backup.
In the same section, you also have the possibility to add an expiration date or period for the backup.
In order to have the backup expire after a set number of days, click the After option, if it’s not defaulted, and input the number of days after the creation date when you want the backup to expire.
This value has to be an integer ranging from 0 to 99999, in which case 0 will guarantee that your backup will never expire.
Pick the type of backup destination by selecting either Disk or Tape. You have the possibility of selecting up to 64 paths corresponding to disk or tape drives containing a single media set.
In the Select Page (top left side of the window) select Options.
In the section called Overwrite Media, choose one of the following options:
Backup to existing media set
In case of this option, choose either Append to the existing backup set or Overwrite all existing backup sets.
By appending another backup to the existing media set, the previous contents of the media remain intact and the new backup is written at the end of the last backup of the media.
By overwriting all existing backup sets, all other backup sets on the media are going to be overwritten but will preserve the media header, if it is present. In case no media header exists, one is created.
Another available option is to select Check media set name and backup set expiration, which will cause the operation to check the date and time at which the media set and the backup set are going to expire.
If the previous checkbox is ticked, you can input a name in the Media set name textbox, and in case no name is inputed, a media set with a blank name will be created. If a name is specified, the media is checked to see whether the actual name matches the name that has been entered.
Backup to a new media set and erase all existing backup sets
With this option, you enter a name in the New Media set name text box and an optional description.
The process of creating a new media set implies formatting the backup media (one or more tapes or disk files).
The formatting process changes the backup media as follows: deletes the old header, effectively deleting the previous contents of the backup media, and writes a new media header on the backup media on each of the backup devices.
The Reliability section provides optional checkups after the backup is made.
Verify backup when finished
Perform checksum before writing to media
Continue on checksum error
We recommend ticking at least the first two checkboxes when doing backup
In the Transaction log section, check Back up the tail of the log and leave the database in restoring state.
Optionally, you can select the compression of the backup you are going to make from the dropdown list. For more details regarding the compression of backups, please review our article.
This is a step-by-step tutorial on how to backup the Tail of the Transaction Log using the graphical interface of SSMS.
In order to find more information about the Tail of the Transaction Log, please read our article, which offers more details regarding this type of backup, here.
