July 8, 2002 at 8:53 am
How can I use DMO to shrink a database and log file?
July 8, 2002 at 8:58 am
The database and logfile objects both support a shrink method.
Andy
July 8, 2002 at 9:06 am
Thanks for the quick response. Can you point to where I can find an example or do you have one? Thanks again.
July 8, 2002 at 10:03 am
First thing I had handy, might need some touching up:
Dim mSQLServer
Dim mDatabase
Dim mBackup
Dim strDB
Dim oSQLSupport
Dim strMessage
Dim strExcludeList
Dim sBakPath
Dim oLogFile
On Error Resume Next
sBakPath = "D:\sqldata\backup"
strExcludeList = "TEMPDB/PUBS/NORTHWIND/DISTRIBUTION/MODEL"
Print "The following db's were excluded from the transaction log backup: " & strExcludeList
Set mSQLServer = CreateObject("SQLDMO.SQLServer")
Set oSQLSupport = CreateObject("HIGSQLSupport.SQLSupport")
mSQLServer.LoginSecure = True
mSQLServer.Connect "."
For Each mDatabase In mSQLServer.Databases
For Each oLogFile In mDatabase.TransactionLog.LogFiles
If oLogFile.Size >= 50 Then
print mdatabase.name
If InStr(strExcludeList, UCase(mDatabase.Name)) = 0 Then
strMessage = strMessage & mDatabase.Name & Chr(13) & Chr(10)
Set mBackup = CreateObject("SQLDMO.Backup")
mBackup.Database = mDatabase.Name
strDB = ""
If UCase(mDatabase.Name) = "MASTER" Then
strDB = sBakPath & "\FULL_" & UCase(mDatabase.Name) & "_" & oSQLSupport.FileName & ".bak"
mBackup.Files = strDB
mBackup.Action = 0
Else
If mDatabase.DBOption.TruncateLogOnCheckpoint <> 0 Then
mDatabase.DBOption.TruncateLogOnCheckpoint = 0
End If
strDB = sBakPath & "\LOG_" & UCase(mDatabase.Name) & "_" & oSQLSupport.FileName & ".bak"
mBackup.Files = strDB
mBackup.Action = 3
End If
If strDB <> "" Then
mBackup.SQLBackup mSQLServer
End If
Set mBackup = Nothing
oLogFile.Shrink 10, 0
End If
End If
Next
Next
mSQLServer.Disconnect
Set mSQLServer = Nothing
Set oSQLSupport = Nothing
Andy
July 8, 2002 at 10:29 am
Thanks Andy - I will give it a try.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply