SQL Server Home
BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated
as Won't Fix
8/20/2008 6:00:50 AM
User(s) can reproduce this bug
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.
SQL Server 2008 - Developer Edition
Win2003 Standard Server (SP2)
Operating System Language
Steps to Reproduce
BACKUP LOG WITH TRUNCATE_ONLY
incorrect syntax near keyword with
it should work like in SQL Server 2005
to post a comment.
Please enter a comment.
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.
on 8/26/2008 at 2:02 AM
I agree, we are using is only during maintenance which is done by a sysadmin anyway.
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.
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
BACKUP LOG TO DISK = 'NUL:'
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.
to post a workaround.
Please enter a workaround.
© 2014 Microsoft