Today I witnessed yet another user who was dealing with BACKUP LOG [database] WITH TRUNCATE_ONLY.
This is one of the features which got discontinued from SQL Server 2008 and above,and this was one of the best decisions Microsoft had taken after SQL Community experts gave valid reasons for discontinuing it.
I decided to write this blog post to explain why this feature is an evil and force SQL Server 2000(Yes,you heard it right! There are many companies which still uses it) and SQL Server 2005 users not to use it.
When you do a Truncate_Only it simply means that the log backup chain is broken and you are loosing your ability of doing a point in time restore.
This is a worst case situation if you are hit with a disaster.
Lets do a quick demo to demonstrate this –
[Using SQL Server 2005 with SP4 for the demo]
1. I have created a test database named as Test_Truncate
CREATE DATABASE [Test_Truncate]
2. Created a table and inserted one record
USE [Test_Truncate] CREATE TABLE dbo.Test_Truncate (ID INT IDENTITY (100,1), Description VARCHAR (50)) USE [Test_Truncate] INSERT INTO dbo.Test_Truncate VALUES('Mercedes')
3. Now we will proceed and take a backup of this database
BACKUP DATABASE Test_Truncate TO DISK='D:\Test_Truncate_1st_Fullbackup.bak'
4. Insert one more record
USE [Test_Truncate] INSERT INTO dbo.Test_Truncate VALUES('BMW')
5. Now we will proceed and take a transaction log backup
BACKUP LOG Test_Truncate TO DISK='D:\Test_Truncate_1st_Tranlog.trn'
6.Insert 3 more records
USE [Test_Truncate] INSERT INTO dbo.Test_Truncate VALUES('Mini Cooper') GO INSERT INTO dbo.Test_Truncate VALUES('Ford') GO INSERT INTO dbo.Test_Truncate VALUES('GMC')
7. Now we will do a BACKUP lOG WITH TRUCATE_ONLY
Command(s) completed successfully.
8. Insert 3 more records
USE [Test_Truncate] INSERT INTO dbo.Test_Truncate VALUES('Impala') GO INSERT INTO dbo.Test_Truncate VALUES('Cruz') GO INSERT INTO dbo.Test_Truncate VALUES('Nissan')
9. Now we try to take a transaction log backup again
BACKUP LOG Test_Truncate TO DISK='D:\Test_Truncate_2nd_Tranlog.trn'
As soon as we execute this tran_log backup command we will get the error
Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
This means that we truncated the transaction log prior to this backup and broke the log chain.
If you dont take a full backup immediately after the error and continue with normal log backups,then you dont have a recovery chain to recover the database in the event of a failure.
I have observed a different behavior in SQL Server 2000(Yes! Again I said 2000) were transaction log backups are created after Truncate_Only,however there are not valid for a restore and you will end up getting error message like this if you try to restore them after a full backup restore.
Server: Msg 4330, Level 16, State 4, Line 1
The log in this backup set cannot be applied because it is on a recovery path inconsistent with the database.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
SQL Server 2005 is much better that it doesn’t allow any transaction_log backups soon after Truncate_Only.
You have two options to deal with log filling issues
1 . Take a regular tran_log backup if you are running on FULL RECOVERY MODEL
2. Put your databases to Simple RECOVERY MODEL if you dont need point in time recovery.
Thanks for reading.
Thanks, this was very helpful
Glad to hear that,thank you !
Nice Information, That’s what i was looking for and also i have found another helpful post see here: http://www.sqlserverlogexplorer.com/sql-server-backup-log-with-truncate_only-or-with-no_log/
Thanks for the feedback!