Home
About me and Nucleus
Partners
Articles, tips etc.
My blog
Links
Microsoft articles
Forums
Course demo files
The BACKUP LOG command has an option named NO_TRUNCATE. This is used when you have an inaccessible database (suspect, for instance) and you want to backup the orphaned transaction log for that database.
Because of the name of the parameter, however, a common misunderstanding is that the purpose of this option is that you can do several log backups using NO_TRUNCATE and only apply the most recent when you do RESTORE. That is not the case (see version note below, though).
Acknowledgements
Thanks to Peter Yeoh at yohz Software, www.yohz.com. I’ve never recommended using NO_TRUNCATE except when you have an inaccessible database. But Peter opened my eyes for the fact that SQL Server only include log records since last log backup, regardless of whether that one was produced using NO_TRUNCATE or not.
Versions
This article was originally written for SQL Server 2000. SQL Server 2005 has a new option for the backup command, called COPY_ONLY which allow you to do a backup for purpose of copying a database without affecting the backup sequence (the option applies for full and log backups). Also, a quick test on 2005 suggest that you can skip intermediate log backups done using NO_TRUNCATE. This seems to make NO_TRUNCATE pretty much the same as COPY_ONLY for log backups. Personally, I still prefer to think of NO_TRUNCATE as "allow me to backup the log of a damaged database". Below is how SQL Server 2005 Books Online expresses it:
"The NO_TRUNCATE option of BACKUP LOG is equivalent to specifying both COPY_ONLY and CONTINUE_AFTER_ERROR."
Scenario
Say you have the below backup sequence:
- BACKUP DATABASE
- BACKUP LOG ... WITH NO_TRUNCATE
- BACKUP LOG ... WITH NO_TRUNCATE
- BACKUP LOG ... WITH NO_TRUNCATE
Size of the active transaction log
A side effect of NO_TRUNCATE option is that SQL Server does not empty the active transaction log. My guess is that this is because the database might be inaccessible, so SQL server cannot perform a CHECKPOINT in the database.
End result is that the ldf file(s) will keep growing until you handle this somehow.
Size of the log backups
This might come as a surprise, but on SQL Server 2000 each log backup will only contain the modifications produced since the prior log backup. Se below for how this affects the ability to restore.
Restore options
As always, you have to start by restoring a database backup. One might think that you then only have to the most recent log backup (since we used the NO_TRUNCATE option), i.e.:
- RESTORE DATABASE from step 1
- RESTORE LOG from step 4
- RESTORE DATABASE from step 1
- RESTORE LOG from step 2
- RESTORE LOG from step 3
- RESTORE LOG from step 4
Is Microsoft misleading us for SQL Server 2000?
I would say both "yes" and "no" to this.
First, I want to stress that the documentation does not state that NO_TRUNCATE can be used to restore from only 1 and 4 above. The documentation states that NO_TRUNCATE is used when you have an inaccessible database.
However, the name of this option suggests that you don't have to apply all log backups, that only the most recent will do. I have suggested to MS to change the name of this option so it reflects what the purpose of the option is, instead of the technical implementation. I have a feeling that this will not happen, though.
Script proving above points:
The script below performs a database backup and three log backups. All log backups are produced using the NO_TRUNCATE option. Before each log backup is produced a number of modifications are performed in the database. I suggest that you monitor the size of both the ldf file as well as the log backup files produced. You will see on 2000 that each log backup is about the same size, while on 2005 the size will acumulate.
Warning: Below will delete a database named 'test', if such exists!
SET NOCOUNT ON
USE master
GO
IF EXISTS (SELECT * FROM sysdatabases WHERE name = 'test') DROP DATABASE test
GO
CREATE DATABASE test
GO
USE test
CREATE TABLE t(c1 INT IDENTITY, c2 CHAR(5000) DEFAULT 'hello')
BACKUP DATABASE test TO DISK = 'c:\db.bak' WITH INIT
GO
DECLARE @a INT SET @a = 1
WHILE @a <= 1000
BEGIN
INSERT INTO t DEFAULT VALUES
SET @a = @a + 1
END
BACKUP LOG test TO DISK = 'C:\1.bak' WITH NO_TRUNCATE, INIT
GO
DECLARE @a INT SET @a = 1
WHILE @a <= 1000
BEGIN
INSERT INTO t DEFAULT VALUES
SET @a = @a + 1
END
BACKUP LOG test TO DISK = 'C:\2.bak' WITH NO_TRUNCATE, INIT
GO
DECLARE @a INT SET @a = 1
WHILE @a <= 1000
BEGIN
INSERT INTO t DEFAULT VALUES
SET @a = @a + 1
END
BACKUP LOG test TO DISK = 'C:\3.bak' WITH NO_TRUNCATE, INIT
USE master
RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY, REPLACE
--Below fails on 2000
RESTORE LOG test FROM DISK = 'C:\3.bak' WITH RECOVERY
--You have to restore the transaction log backups in sequence:
RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY, REPLACE
RESTORE LOG test FROM DISK = 'C:\1.bak' WITH NORECOVERY
RESTORE LOG test FROM DISK = 'C:\2.bak' WITH NORECOVERY
RESTORE LOG test FROM DISK = 'C:\3.bak' WITH RECOVERY
--Clean up
DROP DATABASE test