January 30, 2007 at 3:36 pm
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
February 2, 2007 at 4:29 pm
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
February 5, 2007 at 4:24 am
I found the solution:
Set
srv = CreateObject("SQLDMO.SQLServer2")set
objBackup = CreateObject("SQLDMO.Backup2")srv.LoginTimeout = 15
srv.loginsecure =
Truesrv.Connect
"."Set
objDB = CreateObject("SQLDMO.Database")For
Each objDB In srv.DatabasesIf objDB.SystemObject = False and objDB.DBOption.readonly = False ThenobjBackup.Database = objDB.Name
objDB.Checkpoint()
objbackup.Action = 3
objbackup.TruncateLog = 3
objBackup.SQLBackup srv
objDB.shrink -1,2
end ifNext
set
srv= nothingset
objDb = nothingset
objBackup = nothingHope it helps.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply