0

When restoring the db via SSMS UI, there is an option (checkmark) to take the tail log backup and optionally to place the db in NO RECOVERY mode.

Does such a tail log backup truncate the tlog? And is there any way (UI option) to prevent tlog truncation?

2 Answers 2

1

Yes, any transaction log backup, which a "tail" one still is, truncates the transaction log up to the beginning of the active portion of the transaction log and discards the information in the inactive portion.

1

No, that option in the GUI doesn't truncate the log. See script at the end for proof.

The GUI is a bit strange in the sense that it adds two options to the BACKUP command when you do that selection. These two options are designed for two very different situations:

NORECOVERY which disallows any more modifications in the database, making it the guaranteed last log backup. What you can call "tail log backup" if you want. As a side effect, it will fail if there are connections to the database.

NO_TRUNCATE which allow us to do a log backup on a database which is broken, for instance is missing a database file. As a side effect, it doesn't truncate the log.

Here's proof (execute script with care):

USE master
GO
DROP DATABASE IF EXISTS db1
GO

CREATE DATABASE db1
GO
CREATE TABLE db1..t(c1 int)
ALTER DATABASE db1 SET RECOVERY FULL
INSERT INTO db1..t (c1) VALUES(1)

BACKUP DATABASE db1 TO DISK = 'db1.bak' WITH FORMAT
INSERT INTO db1..t (c1) VALUES(2)

BACKUP LOG db1 TO DISK = 'db1_1.trn' WITH FORMAT
INSERT INTO db1..t (c1) VALUES(3)

--Do the "tail log" backup, non-relevant options from the GUI removed
BACKUP LOG [db1] TO  DISK = 'db1_2.trn' WITH  NO_TRUNCATE ,   NORECOVERY, FORMAT

--Get the database out from "restoring" state
RESTORE DATABASE db1 WITH RECOVERY
GO

--Do a subsequent log backup
INSERT INTO db1..t (c1) VALUES(4)
BACKUP LOG db1 TO DISK = 'db1_3.trn' WITH FORMAT

--Restore, skip the "GUI/tail log" backup
RESTORE DATABASE db1 FROM DISK = 'db1.bak' WITH NORECOVERY, REPLACE
RESTORE LOG db1 FROM DISK = 'db1_1.trn' WITH NORECOVERY, REPLACE
RESTORE LOG db1 FROM DISK = 'db1_3.trn' WITH RECOVERY, REPLACE
--Note how we skipped the GUI/tai log backup (db1_2.trn) in our restore sequence

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.