none
BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated

    Question

  • We have c# application using SQL Server 2000 and 2005. At one place the application has a routine to dump the transaction log which runs for every 30 minutes using the following commands:
    BACKUP LOG 'databasename' WITH NO_LOG
    BACKUP LOG TempDB WITH NO_LOG.


    These commands work fine in SQL 2000 but generate the above error in SQL 2005.
    When I looked at Microsoft help forums they recommend using database model to simple instead of full.


    But our problem is that most of our customers are still using SQL 2000 and we need that routine to run.
    Is there any way to work around for the above command for SQL 2005?


    Thanks in advance.
    Thursday, January 31, 2008 8:51 PM

Answers

  • Here is a nice article by Paul Randal about this issue and alternatives to avoid this.

     

    Friday, February 01, 2008 12:49 AM
  • Thanks Sankar that link was helpful Smile Just adding to that, the command will be deprecated in future version of Sql Server. If you use that command it will break the LSN and incase if you had log shipping configurred it will fail as a result of it. Hence it should NEVER BE USED. The ONLY case it can be used is when the disk in which the transaction log resides is completely FULL and there is no way to clear the disk space.In general terms the better option would be to take regular transaction log backup if you have full recovery model..

    - Deepak
    Friday, February 01, 2008 1:17 AM
  • Here is an work around you can do in your SQL code:

     

    Code Snippet

    IF @@Version LIKE '%2000%'

    BEGIN

     

    --put SQL 2000 specific code here

     

    END

    ELSE IF @@Version LIKE '%2005%'

    BEGIN

     

    --put SQL 2005 specific code here

     

    END

     

     

    I had to do this to keep some of my server management processing on a single SQL base for 2000 and 2005.  It works for me, but adds to your code.

    Monday, February 04, 2008 12:15 AM

All replies

  • Here is a nice article by Paul Randal about this issue and alternatives to avoid this.

     

    Friday, February 01, 2008 12:49 AM
  • Thanks Sankar that link was helpful Smile Just adding to that, the command will be deprecated in future version of Sql Server. If you use that command it will break the LSN and incase if you had log shipping configurred it will fail as a result of it. Hence it should NEVER BE USED. The ONLY case it can be used is when the disk in which the transaction log resides is completely FULL and there is no way to clear the disk space.In general terms the better option would be to take regular transaction log backup if you have full recovery model..

    - Deepak
    Friday, February 01, 2008 1:17 AM
  •  

    As my article stated, it's been removed from the product in SQL Server 2008 - it was previously deprecated.
    Sunday, February 03, 2008 6:03 AM
  • Here is an work around you can do in your SQL code:

     

    Code Snippet

    IF @@Version LIKE '%2000%'

    BEGIN

     

    --put SQL 2000 specific code here

     

    END

    ELSE IF @@Version LIKE '%2005%'

    BEGIN

     

    --put SQL 2005 specific code here

     

    END

     

     

    I had to do this to keep some of my server management processing on a single SQL base for 2000 and 2005.  It works for me, but adds to your code.

    Monday, February 04, 2008 12:15 AM