2

I'm using Powershell to check last backup times across all our sqlserver databases. This seems to work really well, but I've got a question

If I run this (a cut-down version of the actual script):

dir SQLSERVER:\SQL\Server1\default\databases | select parent, name, 
lastbackupdate

I get:

Parent                     Name                       LastBackupDate
------                     ----                       --------------
[Server1]                  ADBA                       10/09/2012 21:15:37
[Server1]                  ReportServer               10/09/2012 21:00:17
[Server1]                  ReportServerTempDB         10/09/2012 21:00:18
[Server1]                  db1                        10/09/2012 21:15:35

If I then run a sql backup of the Server1 default instance, and run the same query the last backup date doesn't change:

PS C:\temp> dir SQLSERVER:\SQL\Server1\default\databases | select parent, name,
lastbackupdate

Parent                     Name                       LastBackupDate
------                     ----                       --------------
[Server1]                  ADBA                       10/09/2012 21:15:37
[Server1]                  ReportServer               10/09/2012 21:00:17
[Server1]                  ReportServerTempDB         10/09/2012 21:00:18
[Server1]                  db1                        10/09/2012 21:15:35

..but if I open a new powershell window, it shows the backup I just took:

PS SQLSERVER:\> dir SQLSERVER:\SQL\Server1\default\databases | select parent, 
name,    lastbackupdate

Parent                     Name                       LastBackupDate
------                     ----                       --------------
[server1]                  ADBA                       12/09/2012 09:03:23
[server1]                  ReportServer               12/09/2012 08:48:03
[server1]                  ReportServerTempDB         12/09/2012 08:48:04
[server1]                  db1                        12/09/2012 09:03:21

My guess is that this is expected behaviour, but could anybody show me where it's documented/explained - I just want to understand what's going on.

This is running the SQlps which came with 2008, against a 2008 instance.

Thanks

Matt

mattypenny
  • 23
  • 3

1 Answers1

2

sqlps uses SMO just like SQL Server Management Studio and have you ever noticed in the database object explorer pane you need to right-click and select refresh when you for example create a new table via T-SQL in the query pane? Well, same concept with sqlps only there's no right click.

Instead you need to call the refresh method:

dir SQLSERVER:\SQL\Server1\default\databases | foreach { $_.Refresh() }
Chad Miller
  • 1,101
  • 8
  • 11