BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated - by Raphael Affolter

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 362628 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 8/20/2008 6:00:50 AM
Access Restriction Public


Even if there is already an article about this issue, I want to bring another usage of these operations into your mind:

While doing maintenance, we are used BACKUP LOG WITH TRUNCATE_ONLY to truncate the transaction log. After the maintenance, we inform the user that a full backup must be performed to restore the normal backup operation chain. Even if the this is not immediately done, the problem will be automatically fixed after the next (scheduled) full backup.

This is now not possible anymore! Why is it so bad to have a command like this? If the user/sysadmin/developer knows what (s)he is doing, there is no problem with this command.

As a workaround, I now have to switch to the simple recovery model during maintenance. But if our maintenance application crashes, the database will stay in the simple recovery mode. Therefore the workaround is causing a higher chance of data loss, exactly the opposite of the intention to remove it. Somebody must now manually set the recovery model back to full!

Please consider bringing this helpful option of the backup command back.

Raphael Affolter
Sign in to post a comment.
Posted by Rj3 on 4/20/2011 at 10:44 AM
I definitely do not believe that removing a feature that is been around for more than 15years (e.g. SQL 6.5) is the right approach to protect users from themselves.

Restricting it to sysadmin or making it an option that could be enabled (disabled by default) would be a definitely a better/smart solution for the "user" problem.

Please reconsider this decision.
Posted by Raphael Affolter on 8/26/2008 at 2:02 AM
I agree, we are using is only during maintenance which is done by a sysadmin anyway.
Posted by Raphael Affolter on 8/26/2008 at 1:02 AM
Do you see what you are doing? Instead of increasing the backup reliability, you make it even worse.

After this "null device" backup, there is to error/warning in subsequent transaction log backups! Big surprise if you want to restore the backup.

With the old "truncate only" approach, there was a proper error message "BACKUP LOG cannot be performed because there is no current database backup.".

After database maintenance, we are informing the sysadmin, that a full backup must be performed. If (s)he forgets it, the mentioned error message appears.

None of our customers ever had a problem with this behaviour!

So all developers will replace a dangerous operations by even more dangerous operations. We have to do maintenance, what else can we do?

If you really want to protect the users from themselves, the better option would be to have some kind of force mechanism.
Posted by Microsoft on 8/25/2008 at 2:04 PM
While I understand that in your case you know what you are doing and are covering the eventualities, the sad fact is that many customers have used these commands without understanding the implications, and have lost valuable data as a result. Even in your case if the customer overlooks your guidance to immediately perform a full backup, their data will be exposed. In normal operations, this command is very rarely useful and more often destructive.

For those customers who really understand what they are doing, the same result can still be achieved by using the command
Posted by ChrisAVWood on 8/21/2008 at 9:53 AM
On systems running with either the FULL or BULK-LOGGED recovery model having code with BACKUP LOG WITH TRUNCATE_ONLY causes major headaches until a FULL BACKUP is run.

If you must have this then I would suggest that only a sysadmin should be able to run it rather than just anyone.