Tuesday, December 14, 2004

Finding when a Exchange Store last backed up via script

There are a couple of ways you can determine when the last time a particular exchange store was backed up if you're using a program that backs up via the Exchange Backup API (Veritas,Brightstore etc).

With Exchange 2003 a new function was added into System Manager to allow you to see the "Time of Last Full Backup" on the database tab. This property is also available in CDOEXM (if you have the 2003 version of system manager loaded on the machine). The ".LastFullBackupTime" property was added to the IMailStoreDB and IPublicStoreDB interfaces. Using this is pretty easy all you have to do is to open the mailstore using the LDAP DN path and access the property eg

set mdbobj = createobject("CDOEXM.MailboxStoreDB")
mdbobj.datasource.open "LDAP://mbdDNpath"
Wscript.echo "Last Backed Up : " & mdbobj.LastFullBackupTime

I've put this together with a similar query from my previous post and this script selects all the exchange stores in a domain and reports when the last time they where backed up. I've posted the code here (this will on work on Exchange 2003)

Another method you can use to do this is to query the eventlog on each of your servers and look for each of the 221 completion events for each of the database files. When the backup runs on a Exchange server you get a bunch of really useful events that tell you when the backup started, how much was backed up and when it finished. In this script im focusing on the finished events which include the path to the database file. So if I combine it with the script from my previous post which was already getting the server name and file path of each of the database files. You end up with a script that will query Active directory for every mail and public folder store then query the event log via WMI to work out when the last time a backup occurred of each file and display the result on the commandline. Note I've limited the search range to the last 7 days so if a server hasn't backed up in the last 7 days you receive a no backup recorded message. This was done to keep the time it takes to run the script to a minumn as searching through a really large event log can be very time consuming. I've posted a copy of the script here The code look like


set conn = createobject("ADODB.Connection")
set com = createobject("ADODB.Command")
Set iAdRootDSE = GetObject("LDAP://RootDSE")
strNameingContext = iAdRootDSE.Get("configurationNamingContext")
rangeStep = 999
lowRange = 0
highRange = lowRange + rangeStep
Conn.Provider = "ADsDSOObject"
Conn.Open "ADs Provider"
mbQuery = ";(objectCategory=msExchPrivateMDB);name,distinguishedName;subtree"
pfQuery = ";(objectCategory=msExchPublicMDB);name,distinguishedName;subtree"
Com.ActiveConnection = Conn
Com.CommandText = mbQuery
Set Rs = Com.Execute
Wscript.echo "Mailbox Stores"
Wscript.echo
While Not Rs.EOF
objmailstorename = "LDAP://" & Rs.Fields("distinguishedName")
set objmailstore = getObject(objmailstorename)
servername = mid(objmailstore.msExchOwningServer,4,instr(objmailstore.msExchOwningServer,",")-4)
slvlastbackuped = queryeventlog(servername,objmailstore.msExchSLVFile)
edblastbackuped = queryeventlog(servername,objmailstore.msExchEDBFile)
Wscript.echo Rs.Fields("name") & " Last Backed up EDB : " & edblastbackuped
Wscript.echo Rs.Fields("name") & " Last Backed up STM : " & slvlastbackuped
wscript.echo
Rs.MoveNext

Wend
Wscript.echo "Public Folder Stores"
Wscript.echo
Com.CommandText = pfQuery
Set Rs1 = Com.Execute
While Not Rs1.EOF
objmailstorename = "LDAP://" & Rs1.Fields("distinguishedName")
set objmailstore = getObject(objmailstorename)
servername = mid(objmailstore.msExchOwningServer,4,instr(objmailstore.msExchOwningServer,",")-4)
slvlastbackuped = queryeventlog(servername,objmailstore.msExchSLVFile)
edblastbackuped = queryeventlog(servername,objmailstore.msExchEDBFile)
Wscript.echo Rs1.Fields("name") & " Last Backed up EDB : " & edblastbackuped
Wscript.echo Rs1.Fields("name") & " Last Backed up STM : " & slvlastbackuped
wscript.echo
Rs1.MoveNext

Wend
Rs.Close
Rs1.close
Conn.Close
Set Rs = Nothing
Set Rs1 = Nothing
Set Com = Nothing
Set Conn = Nothing


function queryeventlog(servername,filename)
SB = 0
dtmStartDate = CDate(Date) - 7
dtmStartDate = Year(dtmStartDate) & Right( "00" & Month(dtmStartDate), 2) & Right( "00" & Day(dtmStartDate), 2)
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & servername & "\root\cimv2")
Set colLoggedEvents = objWMIService.ExecQuery("Select * from Win32_NTLogEvent Where Logfile='Application' and Eventcode = '221' and TimeWritten >= '" & dtmStartDate & "' ",,48)
For Each objEvent in colLoggedEvents
SB = 1
Time_Written = objEvent.TimeWritten
Time_Written = left(Time_Written,(instr(Time_written,".")-1))
if instr(objEvent.Message,filename) then
queryeventlog = dateserial(mid(Time_Written,1,4),mid(Time_Written,5,2),mid(Time_Written,7,2)) & " " & timeserial(mid(Time_Written,9,2),mid(Time_Written,11,2),mid(Time_Written,13,2))
exit for
end if
next
if SB = 0 then queryeventlog = "No Backup recorded in the last 7 Days"
end function

24 comments:

Anonymous said...

Hi there,

My name is Spiro. This is such a cool script and I could really use something like this in our org. Unfortunately, its not working properly - these are the conditions:
1.Running the script on an Exchange 2003 box with Exchange 2003 ESM installed.
2. Error message is:
Line 18, Character 3.
"Last Backup Times are onky available from Exhcange 2000 SP2 and later.
Id no: C103222e
MS CDO for Exchange management.

I would rally appreicate your help with this as I have scoured the web and this is script is IDEAL!!
If y ou can help, thanks....

Glen said...

This script will go out and look at all the mailstores it finds in Active Directory. It will only report sucessfully on Exchange 2003 servers so if it finds a 2000 server it will give this error. One way to diagnose this is to put on error resume next at the top of the script and run it and see what happens. Any servers it has a problem with it should skip over or just return blank but you should get something for the valid servers.

Anonymous said...

Thanks for your comments Glen but could you help further. Where would I put the error resume next line and would the entire script be safe running if errors are encountered i.e. what are the implications?
Thanks again...

Anonymous said...

My environment is mixed 5.5 and 2003 boxes but I am oinly interested in the 2003 backup information. Thanks again...

Glen said...

With on error resume next you just put it on top of the current first line eg

on error resume next
set conn = createobject("ADODB.Connection")

Generally its not something you recomend doing as your better of dealing with the error that comes up rather then just ignoring it. In this case the script is really only trying to read some properties the worst thing that could happen is maybe it will get stuck in a loop or peg the processor on the machine its running on i doubt this will happen in this instance. A smarter way to do it would be to look for a property you could use to detect if it isn't a Exchange 2003 and then get the script to skip over those servers. I dont have any 5.5 servers to test unfortunalty the on error resume next will at least confirm weather this is the problem. I the other thing worth mentioning is i believe the Exchange Best Practices Analyser now gathers this info as well.

Anonymous said...

I will try the analyser next, perhaps this will be more useful in achieving what I need. Thanks for your help, cheers...

Anonymous said...

Glen,

Nice script! My comment on this script is, I have 4 SGs and 4MSs in each SG.
The result of your script gives the server name on first line followed by:
Mailbox store 1:
Mailbox store 2:

But it doesn't give Storage group. Ofcourse I can go through each line and figured it out which is what.

The suggestion would be to include SG name. Ideal thing in our environment is just to take the EDB file name, NOT path of .edb file, which would be very long. The file name can be obtained from AD attribute, however we will have to display only last 10characters of .edb path. In our case ALWAYS the .edb file consists of 10 characters. For ex. SG1MS4.EDB.

So can you suggest couple of lines to include in script that would give me result:

SG1MS4.EDB Last Backup: 9/8/2005

Glen said...

Have a look at the following script its not 100% but i should do what you want http://bluetack.aspxconnection.com/exdevblog/lexchstorebup3.zip

Anonymous said...

Thank you Glen for the zip file.

I ran the file, but it said "object doesn't support this property or method: 'mdbobj.lastfullbackuptime'"

I edited your script and had this one, as I don't want to run on hundreds of server, rather wanted to find out the backup status on this particular server. can you please tell me what'z wrong with this script?

Oops this site didn't allow me to paste the script. Can you pl give me your email where I can send the script to?

Thanks

Glen said...

Are you running this against a Exchange 2003 server or if you are running it remotly does the Pc you are trying to run it on have the Ex2003 version of Exchange Admin tools. That particular property is only availble on 2003. (There are other ways you can do the same thing on 2000 using the Event log i've posted some other scripts to do this). If you want you can send your script to gscales@outlookexchange.com and i can have a look

j_salinas said...

Glen.

I run the script, but it appears the following error : Expected end of statament
code : 80OA0401.
Line :mbQuery = "pfQuery = "Com.ActiveConnection = Conn

Please, Help :)!
My mail is : jsvmsn@gmail.com

Glen said...

Hi Juan,

That looks like a cut and paste issue blogger is not very script freindly. For that reason i allways include a downloadable version of the script you can get the download from http://msgdev.mvps.org/exdevblog/lexchstorecdoexm.zip

Cheers
Glen

Amilcar said...

Hi there Glen,

Is there a property in the Exchange WMI MicrosoftExchangeV2 namespace to get the "time of last full backup" provided in the Exchange System Manager under the MailboxStore/Database Tab? I am using Exchange 2003 and would like to provide this information to a group of people. Any thoughts.

email - croacrek@gmail.com

Thanks....

Glen said...

No you need to use CDOEXM to retrieve this information or use the eventlog this information is available via WMI.

Cheers
Glen

Anonymous said...

Very nice script and works great. I have 50 exchange server with about 200+ databases and it queries them without a problem. Just added a few lines to write out to CSV and email all th admins. I have a few servers still defined in my configuration container and put in the On Error Resume Next line just to keep it running all the way through. I was going to run ESEUTIL /MK on all of the .chk files looking for last backup time, using psexec, but that would have been a bit scarry and nasty.

Anyways, thanks again!

Anonymous said...

Hi there,

How do you tweak this query to only check for an specific server by providing the name, and not all servers in a AD forest.

Thanks.

Glen said...

Have a look at http://www.exchange-faq.dk/comments.php?id=1131_0_1_0_C

Cheers
Glen

Anonymous said...

Dear Glen,
is there any way to enable the script to read all Exchange Servers in the organization from file ?

Regards
John Kean

Glen Scales said...

see http://www.flobee.net/report-the-last-time-your-exchange-servers-were-backed-up/

Anonymous said...

Hello Glen,
Thank you for your email

The script your provided does not work.

Yours does work but i'm looking for a way to enable the script to identify Exchange server in the Organization by reading a file (a File that has all Exchange servers) not from enumerating Active Directory.

Appreciate your kind reply

Thanks

Anonymous said...

Hello Glen,
I tried many times to edit the script to enable it to read all Exchange Servers in the organization from a test file instead of enumerate them from active directory, but I ran all possible solutions, can you help ?
Thanks,
John

Glen Scales said...

Sorry I don't have access to any Exchange 2003 servers or large Organizations (I wrote this script over 9 year ago) so as i suggested I would try the script I liked to above. If something isn't working for you it best to ask questions about particular errors your having with the code your modifying. eg what error what does the change your making look like etc. You can also try asking in scripting forums and such etc.

Glen

Anonymous said...

I see, well i have a large organization with many dead servers, that the script keep crashing when checking all the servers in the organization, i tired to specify only the alive servers in a text file, the missing part now is to allow the script to get all (functioning ) Exchange server names from the text file not from the AD.
Thanks

Glen Scales said...

If you want to read servernames from a text file see http://blogs.technet.com/b/heyscriptingguy/archive/2004/11/16/can-i-read-an-entire-text-file-rather-than-reading-it-line-by-line.aspx.

If you can't get something to work ask question as to what doesn't work and provide the code that you have modified that doesn't work and the error you get and I'll suggest how you can fix it.

Glen