Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

SQLSailor - Personal Notes on Microsoft SQL Server

Browse by Tags · View All
SQL Server 2012 35
General 13
News/Updates 10
Windows Azure 7
HA Solutions 7
Cool Features 4
Connect Items 4
SQL Azure 4
Powershell 4

Archive · View All
January 2012 17
April 2012 11
March 2012 11
June 2012 9
May 2012 9
August 2012 6
September 2012 5
July 2012 5
March 2013 4
February 2013 3

Anup Varier's Blog

BACKUP LOG WITH TRUNCATE_ONLY!!! Thank heaven that it got discontinued from 2008 – Above

Jan 6 2012 12:00AM by Anup Warrier   

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


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.

Conclusion -

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.

Republished from SQLSailor [47 clicks].  Read the original version here [2 clicks].

Anup Warrier
212 · 1% · 224


Your Comment

Sign Up or Login to post a comment.

"BACKUP LOG WITH TRUNCATE_ONLY!!! Thank heaven that it got discontinued from 2008 – Above" rated 5 out of 5 by 1 readers
BACKUP LOG WITH TRUNCATE_ONLY!!! Thank heaven that it got discontinued from 2008 – Above , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]