Tail Log Backup

Tail Log Backup takes the backup of log records that have not yet been backed up. We can use this backup to return to the last moment. After Tail Log Backup, the database switches to restoring mode, ensuring that it is the last log backup.

You can get a tail log backup in the following way.

First, right click on the database and click Tasks and Backup.

We select Transaction Log from the Backup type section on the screen and select Disk in the Back up to section and click Add. We choose the path we will get Backup and give the backup a name with the .trn extension.

Then we go to the Media Options section and select “Back up the tail of the log, and leave the database in the restoring state” from the Transaction log section as follows to get our tail log backup.

We can get the script of the actions we have done by clicking on the above Script button. You can see this script below.

We can be a little more flexible with the script. If we add NORECOVERY in the script, it puts the database in restoring mode. In this way, as I explained at the beginning of the article, we guarantee that the tail log backup we received is the last log backup. NO_TRUNCATE should only be used in damaged databases. If your database is not damaged, you should remove this part from the script.

You may receive an error as follows when you run the Tail Log Backup script.

Msg 3101, Level 16, State 1, Line 3

Exclusive access could not be obtained because the database is in use.

Msg 3013, Level 16, State 1, Line 3

BACKUP LOG is terminating abnormally.

We’re running the script again by adding the following code to the beginning of the script.