Truncate Tlog with DMO

• Martin Balbo

  SSC Eights!

  Points: 824

  More actions

  January 30, 2007 at 3:36 pm

  #117462

  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.

   

   

• February 2, 2007 at 8:00 am

  This was removed by the editor as SPAM

• MohammedU

  SSCoach

  Points: 19644

  More actions

  February 2, 2007 at 4:29 pm

  #687596

  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

• Martin Balbo

  SSC Eights!

  Points: 824

  More actions

  February 5, 2007 at 4:24 am

  #687702

  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.