DUMP TRAN <database name> WITH TRUNCATE_ONLY fail in sql server 2008

Question

I have code for 2005 sql server loading data with
DUMP TRAN <database name> WITH TRUNCATE_ONLY
It fails with

Msg 102, Level 15, State 1, Line 48
Incorrect syntax near 'DUMP'.
Msg 319, Level 15, State 1, Line 48
Incorrect syntax near the keyword 'with'

In the link
http://msdn.microsoft.com/en-us/library/ms144262.aspx

stated that DUMP replaced by BACKUP, but

BACKUP LOG <database name> WITH TRUNCATE_ONLY

Fails also.

What I have to do replacing the the

DUMP TRAN <database name> WITH TRUNCATE_ONLY

Thanks for help

Roni

Answer 1

What was the error when you are trying to use backup log dbname with truncate_only ? Logially this syntax is correct

---

Thanks, Leks

Answer 2

Hi Leks,

The code

DUMP TRAN <database name> WITH TRUNCATE_ONLY

was ok for 2005 but as bove mentioned it fails for 2008 sql with

Msg 102, Level 15, State 1, Line 48
Incorrect syntax near 'DUMP'.
Msg 319, Level 15, State 1, Line 48
Incorrect syntax near the keyword 'with'


Tried to change it to

BACKUP LOG <database name> WITH TRUNCATE_ONLY

also fails with

Msg 155, Level 15, State 1, Line 56
'truncate_only' is not a recognized BACKUP option.

I guess this also is no actual for 2008.

Thanks
Roni

Answer 3

Sorry to say this , but from SQL srvr 2008  the truncate option is removed.
You may only shrink the log files , but not TRUCNATE them.

Here is a link to Books On Line for that feature. http://msdn.microsoft.com/en-us/library/ms144262.aspx

The transaction log is automatically truncated when the database is using the simple recovery model. If you must remove the log backup chain from a database, switch to the simple recovery model."

---

Thanks, Leks