In search for the best way to check sql back ups, I tried SQL drive and SMO management objects. To my surprise I got different answers from both methods:



PS C:\scripts> ls SQLSERVER:\SQL\servername\default\databases | where {$_.name -eq "databasename"} | select lastbackupdate

LastBackupDate
--------------
4/09/2011 21:06:39




PS C:\scripts> $srv = New-Object "microsoft.sqlserver.management.smo.server" servername
PS C:\scripts> ($srv.databases | where {$_.name -eq "databasename"}).lastbackupdate

maandag 5 september 2011 21:06:34




I also checked the .bak files, the Agent Job History, the \\servername\d$\databases\MSSQL10_50.MSSQLSERVER\MSSQL\Log\DatabaseBackup_0x......txt log, and a SELECT from the msdb.dbo.backupset table. All of those report sep 5 as lastbackupdate.
My server is SQL Server 2008R2 Standard Edition on WINDOWS Server 2008R2 Standard.
I use Ola Hallengren's solution to take back ups, and for the 16 other db's on this server the property is equal to the SMO method.

Why is the SQL drive lastbackupdate property different for this one database?

Update:
3 hours later all databases on the same server now give the last but one date instead of the lastbackupdate. This morning they returned the right date over and over again, and suddenly they're all wrong.

One thing to keep in mind is that you may be looking at cached objects in the provider or SMO object. Did you run both at the same time, starting a fresh sqlps console?

Hi Chad


that mmay have been the problem: I leave my consoles open for a week, or until ping doesn't work anymore. I never use sqlps.

Actually I have a script that collects this information from all SQLservers and inserts it in a database. So I presumed I should have 'new' information every day.

The strangest thing about this problem is that I got sep 5 as lastbackupdate from SQL provider for about 10 runs, and the 11th time it changed to sep 4. How is that possible?
I'm very confused now; get-childitem caches objects? Is this only for the SQLServer: drive? And I have to restart my console to refresh?

I've changed the script so that it uses SMO for all queries now. Seems more reliable.

sqlps provider behaves just like SQL Server Management Studio. For instance you've probably seen where you create a new table via T-SQL, but then to see the new table you have to refresh the tables folder in Object Explorer. Well, same concept for the drive. Here's a quick test I ran which illustrates the problem. The reason you aren't seeing the issue with straight SMO is that you are creating a refresh database object each time.

That's a perfect explanation. Thank you for clarifying this.
I think I like SMO more now.