dakota5
asked on
DUMP TRANSACTION command for SQL Server 2008
I have a line of code from SQLServer 2005
DUMP TRANSACTION dbname WITH TRUNCATE_ONLY
DUMP command was eliminated in SQL2008. The appropriate statement would be (I think)
BACKUP LOG dbname
But what is the equivalent option for TRUNCATE_ONLY (which is not accepted in SQL2008)
NO_LOG? or COPY_ONLY, or NO_TRUNCATE? Something else?
Thanks in advance.
DUMP TRANSACTION dbname WITH TRUNCATE_ONLY
DUMP command was eliminated in SQL2008. The appropriate statement would be (I think)
BACKUP LOG dbname
But what is the equivalent option for TRUNCATE_ONLY (which is not accepted in SQL2008)
NO_LOG? or COPY_ONLY, or NO_TRUNCATE? Something else?
Thanks in advance.
here is recent answer fro EE
https://www.experts-exchange.com/questions/26461742/Is-there-a-Truncate-Only-alternative-in-SQL-2008.html
and other discussions
http://experiencing-sql-server-2008.blogspot.com/2008/02/sql-server-2008-truncating-transaction.html
http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/d0958b81-8cfb-4b2b-8b5a-8e50c835f920
https://www.experts-exchange.com/questions/26461742/Is-there-a-Truncate-Only-alternative-in-SQL-2008.html
and other discussions
http://experiencing-sql-server-2008.blogspot.com/2008/02/sql-server-2008-truncating-transaction.html
http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/d0958b81-8cfb-4b2b-8b5a-8e50c835f920
ASKER
OK. I See. Excellent explanation.
The issue is that the database needs to run in a full recovery model; this obsoleted command (DUMP TRANSACTION dbname WITH TRUNCATE_ONLY) is in the middle of a utility script that we run every month or so (that the vendor wrote). The database runs 24/7 and we really can't (or shouldn't) change it to simple recovery mode.
Are there any way (and is it safe) to on the fly
1. change to simple recovery mode
2. run the utility
3. change back to full recovery
Or safer, just include in the utility code a TLOG backup-- that will shrink the transaction log. How would I do this inside a procedure?
The issue is that the database needs to run in a full recovery model; this obsoleted command (DUMP TRANSACTION dbname WITH TRUNCATE_ONLY) is in the middle of a utility script that we run every month or so (that the vendor wrote). The database runs 24/7 and we really can't (or shouldn't) change it to simple recovery mode.
Are there any way (and is it safe) to on the fly
1. change to simple recovery mode
2. run the utility
3. change back to full recovery
Or safer, just include in the utility code a TLOG backup-- that will shrink the transaction log. How would I do this inside a procedure?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This answer was outstanding; appreciate the links to other references.
If you routinely do DUMP TRANSACTION dbname WITH TRUNCATE_ONLY
You better switch your DB to simple recovery mode and you will no longer need to worry about log.