June 14, 2005 at 5:35 pm
Creating an HTA with a button that will automatically show the date/time of the last full backup of the database using vbscript. I'm having a difficult time doing this and getting plenty of syntax errors. Here is the code. Please help:
Sub LoadMaintenanceInf
strDBServerName = "."
Set objSQLServer = CreateObject("SQLDMO.SQLServer")
Set objDatabase = CreateObject("SQLDMO.Database")
objSQLServer.LoginSecure = True
objSQLServer.Connect strDBServerName
Set colDatabases = objSQLServer.Databases
For Each objDatabase In colDatabases
objSQLServer2.executewithresultsandmessages2 "SELECT Backup_Finish_Date FROM MSDB.dbo.BackupSet where Database_Name = 'objDatabase.Name'", strHTML as queryresults
Next
DataArea.InnerHTML = strHTML
end sub
June 15, 2005 at 1:51 pm
Nevermind. I've figured it out.
June 16, 2005 at 10:16 am
can you share your code and the solution? Thanks.
June 16, 2005 at 2:44 pm
It may not be the prettiest, but it works for me:
Sub Fulldatabasebackup
strDBServerName = "."
Set objSQLServer = CreateObject("SQLDMO.SQLServer")
Set objDatabase = CreateObject("SQLDMO.Database")
objSQLServer.LoginSecure = True
objSQLServer.Connect strDBServerName
Set colDatabases = objSQLServer.Databases
'Loop through each database
For Each objDatabase In colDatabases
'Skip the unnecessary databases
If objDatabase.Name <> "distribution" Then
If objDatabase.Name <> "tempdb" Then
If objDatabase.Name <> "Northwind" Then
If objDatabase.Name <> "pubs" Then
Set strQueryResults = objSQLServer.ExecuteWithResults("SELECT MAX(Backup_Finish_Date) FROM MSDB.dbo.BackupSet where type = 'D' and Database_Name = N'" & objDatabase.Name & "'")
' What I want to display if no full backups have been taken
If strQueryResults.GetColumnString(1, 1) = "" Then
strHTML = strHTMl & "No full database backup has been taken of the " & objDatabase.Name & " database" & "<br>"
Else
' Display the date the last backup was taken
strHTML = strHTML & " Date of last backup " & strQueryResults.GetColumnString(1, 1) & " " & objDatabase.Name & " database" & "<br>"
End if
End If
End If
End If
End If
Next
' Display the information
DataArea.InnerHTML = strHTML
end Sub 'Fulldatabasebackup
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply