none
truncate_only' is not a recognized BACKUP option

    Question

  • Hello All,

    I am able to run the below command in sql 2005 but in 2008 getting error : truncate_only' is not a recognized BACKUP option.


    Back Up Log [DBName] with Truncate_Only 
    dbcc shrinkfile([logFileName])

    Thanks in Advance

    Ashwin

    Friday, April 24, 2009 10:34 AM

Answers

All replies

  • Hi,

    Check this link might be helpful

    http://sqlserverpedia.com/blog/sql-server-management/backup-log-with-truncate_only/
    Rajesh Jonnalagadda http://www.ggktech.com
    Friday, April 24, 2009 10:39 AM
  • it became deprecated because of 'misuse'  in 2005 & removed in 2008.  You can achieve the same result by sending the file to the NUL blackhole 

    BACKUP LOG [DBNAME] TO DISK='NUL'

    Make sure that if you are taking transaction log backups this will break your chain of transaction logs & you will need to take a full backup otherwise any log backups taken after the TRUNCATE_ONLY (or the 2008 equivalent) are useless
    • Edited by Nick Kavadias Friday, April 24, 2009 11:06 AM more info
    • Proposed as answer by Nick Kavadias Sunday, April 26, 2009 12:03 AM
    Friday, April 24, 2009 10:40 AM
  • Hello,

    Go throught the link.
    http://experiencing-sql-server-2008.blogspot.com/2008/02/sql-server-2008-truncating-transaction.html


    Hope this might be help ful.

    Thanks,
    Shanthi
    Friday, April 24, 2009 10:40 AM
  • No, it's not supported any more in sql 2008, if you want to remove log chain, try to switch to simple recovery mode.


    Regards,

    Andrian
    Friday, April 24, 2009 10:52 AM
  • Hey,

    Thanks for your reply.

    I have done this.
    It worked by using

    dbcc shrinkfile ([DBName])

    Friday, April 24, 2009 11:51 AM
  • For shrinking log file in sql server 2008, first u have to change the recovery model of database to simple,shrink the log file and then change back to the previous recovery model. following is the code

    USE dbname;
    GO
    -- Truncate the log by changing the database recovery model to SIMPLE.
    ALTER DATABASE dbname
    SET RECOVERY SIMPLE;
    GO
    -- Shrink the truncated log file to 1 MB.
    DBCC SHRINKFILE (2, 1);  -- here 2 is the file ID for trasaction log file,you can also mention the log file name (dbname_log)
    GO
    -- Reset the database recovery model.
    ALTER DATABASE dbname
    SET RECOVERY FULL;
    GO
    • Proposed as answer by RTTAdmin Tuesday, March 22, 2011 10:32 AM
    Tuesday, July 28, 2009 6:57 AM
  • Amit, Thank You!  I just recovered over 100GB!!!
    Saturday, September 18, 2010 2:35 PM
  • Awesome, exactly what I was looking for.  100 gig here too.
    Monday, October 04, 2010 7:10 PM
  • it became deprecated because of 'misuse'  in 2005 & removed in 2008
    Now that's typical Microsoft..."We understand your needs better than you do"
    Friday, January 21, 2011 9:11 PM
  • Thanks for sharing right information. It helped us alot.
    Sunday, February 06, 2011 7:16 AM
  • Thanks, very useful information!

    Sunday, April 03, 2011 7:09 PM
  • I'm using SQL 2008 to build a BIDS package to migrate database objects from one database to another.  The SSIS package is throwing this error that this TRUNCATE_ONLY option no longer exists.  It's nice that Microsoft took away the function, but then they still call it in the Integration Services  Looking for a workaround.  The two databases I'm trying to transfer are both set to simple recovery.

    Friday, April 15, 2011 4:19 PM
  • When you take log backup with Truncate_only.

    The inactive portion becomes reuse and breaks the LSN chain. And it will not generate any backup file on the disk.

    After you perform Truncate_only immedietly you should perform full backup.

    Saturday, April 16, 2011 1:18 PM
  • When you take log backup with Truncate_only.

    The inactive portion becomes reuse and breaks the LSN chain. And it will not generate any backup file on the disk.

    After you perform Truncate_only immedietly you should perform full backup.


    The thing is Microsoft has removed the commands in SQL server 2008 onwards.

    Read this post you may get it http://www.sqlserverblogforum.com/2011/03/difference-between-truncating-and-shrinking-the-transaction-log-file/


    Muthukkumaran Kaliyamoorthy SQL DBA MyBlog-->sqlserverblogforum Please click the Mark as Answer button if a post solves your problem!

    Monday, April 18, 2011 10:20 AM
  • Just switch to simple then switch back to full, then you can shrink the log file already

    http://myitsolution.info/?p=77

    Thursday, April 21, 2011 1:10 PM
  • Absoltely helpful....

    I was looking for the same and it really helped me ..

    Thanks very much........

    • Proposed as answer by MrKMosher Tuesday, May 07, 2013 2:01 AM
    • Unproposed as answer by MrKMosher Tuesday, May 07, 2013 2:01 AM
    Monday, July 02, 2012 10:38 AM
  • I struggled with this doing it manually using the shrink Files option. Some DBs would, some wouldn't. I found the problem seemed to be the intial size.

    Right click on the DB > Properties > files and I was able to change the inital size which in my case for some reason was 79. I changed it to 1.


    Kim

    Tuesday, May 07, 2013 2:04 AM
  • It doesn't exist exactly how it was in 2005, but truncating the transaction log can be done in 2008 using a different command:

    backup log <your db name> to disk = 'NUL'

    Thursday, May 23, 2013 3:48 PM