none
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

    Friday, December 11, 2009 8:51 PM

Answers

All replies

  • What was the error when you are trying to use backup log dbname with truncate_only ? Logially this syntax is correct
    Thanks, Leks
    Saturday, December 12, 2009 4:50 AM
  • 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

    Saturday, December 12, 2009 11:03 AM
  • 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
    Sunday, December 13, 2009 1:10 AM