Truncate Tlog with DMO

  • Hi, iam writing a DMO script to Truncate Transaction Log. This is the Code:

    Set

    srv = CreateObject("SQLDMO.SQLServer")

    set

    objBackup = CreateObject("SQLDMO.Backup")

    srv.LoginTimeout = 15

    srv.loginsecure =

    True

    srv.Connect

    "serverDB"

    Set

    objDB = CreateObject("SQLDMO.Database")

    For

    Each objDB In srv.Databases

    If objDB.SystemObject = False and objDB.DBOption.readonly = False Then

    objDB.Checkpoint()

    objbackup.Action = 3

    objbackup.TruncateLog = 0

    objBackup.Database = objDB.Name

    objBackup.SQLBackup srv

    wscript.echo

    "DB Shrink: " & objDB.Name

    objDB.shrink 500,SQLDMOShrink_TruncateOnly

    wscript.echo

    "Finish. Error: " & err.number & "." & err.description

    end if

    Next

    set

    srv= nothing

    set

    objDb = nothing

    set

    objBackup = nothing

    When i Ran it, it recieve the following error:

    (16, 9) Microsoft SQL-DMO: [SQL-DMO]You must specify a backup device for Backup or Restore.

    Essentially i want to reproduce these 2 T-SQL commands:

     EXEC("BACKUP LOG " + @databasename + " WITH TRUNCATE_ONLY")

     EXEC("DBCC SHRINKDATABASE (" + @databasename + ",TRUNCATEONLY)")

    This 2 sentences does not requiere Backup device or filename

    Thanks for help.

     

     

  • This was removed by the editor as SPAM

  • Looks like you are passing db name instead of file name to shrink command..

    You can make use of the code from the following...

    http://www.codeproject.com/database/ShrinkingSQLServerTransLo.asp

     

    MohammedU
    Microsoft SQL Server MVP

  • I found the solution:

    Set

    srv = CreateObject("SQLDMO.SQLServer2")

    set

    objBackup = CreateObject("SQLDMO.Backup2")

    srv.LoginTimeout = 15

    srv.loginsecure =

    True

    srv.Connect

    "."

    Set

    objDB = CreateObject("SQLDMO.Database")

    For

    Each objDB In srv.Databases

    If objDB.SystemObject = False and objDB.DBOption.readonly = False Then

    objBackup.Database = objDB.Name

    objDB.Checkpoint()

    objbackup.Action = 3

    objbackup.TruncateLog = 3

    objBackup.SQLBackup srv

    objDB.shrink -1,2

    end if

    Next

    set

    srv= nothing

    set

    objDb = nothing

    set

    objBackup = nothing

    Hope it helps.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply