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.

Answer 1

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

 

Answer 2

Thanks Sankar that link was helpful 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

Answer 3

 

As my article stated, it's been removed from the product in SQL Server 2008 - it was previously deprecated.

Answer 4

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.