Last week I ran a PowerShell lab at SQL Relay in Cardiff. There are still a few places available for SQL Relay week 2. Take a look here for more details and follow the twitter hashtag #SQLRelay for up to date information

The link for my slides and demos from the second part are here https://t.co/Fik2odyUMA

Whilst we were discussing Show-LastDatabaseBackup Kev Chant @KevChant asked where it was getting the information from and I answered that PowerShell was running SQL commands under the hood against the server and if you ran profiler that is what you would see. We didn’t have time to do that in Cardiff but I thought I would do it today to show what happens

A reminder of what Show-LastDatabaseBackup function does

If we start a trace with Profiler and run this function we get these results in PowerShell

In Profiler we see that it is running the following T-SQL for

exec sp_executesql N' SELECT dtb.name AS [Name] FROM master.sys.databases AS dtb WHERE (dtb.name=@_msparam_0)',N'@_msparam_0 nvarchar(4000)',@_msparam_0=N'RageAgainstTheMachine'

 

and then for

exec sp_executesql N' create table #tempbackup (database_name nvarchar(128), [type] char(1), backup_finish_date datetime) insert into #tempbackup select database_name, [type], max(backup_finish_date) from msdb..backupset where [type] = ''D'' or [type] = ''L'' or [type]=''I'' group by database_name, [type] SELECT (select backup_finish_date from #tempbackup where type = @_msparam_0 and db_id(database_name) = dtb.database_id) AS [LastBackupDate] FROM master.sys.databases AS dtb WHERE (dtb.name=@_msparam_1) drop table #tempbackup ',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'D',@_msparam_1=N'RageAgainstTheMachine'

 

For

exec sp_executesql N' create table #tempbackup (database_name nvarchar(128), [type] char(1), backup_finish_date datetime) insert into #tempbackup select database_name, [type], max(backup_finish_date) from msdb..backupset where [type] = ''D'' or [type] = ''L'' or [type]=''I'' group by database_name, [type] SELECT (select backup_finish_date from #tempbackup where type = @_msparam_0 and db_id(database_name) = dtb.database_id) AS [LastDifferentialBackupDate] FROM master.sys.databases AS dtb WHERE (dtb.name=@_msparam_1) <mailto:dtb.name=@_msparam_1)> drop table #tempbackup ',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'I',@_msparam_1=N'RageAgainstTheMachine'

 

And for

exec sp_executesql N' create table #tempbackup (database_name nvarchar(128), [type] char(1), backup_finish_date datetime) insert into #tempbackup select database_name, [type], max(backup_finish_date) from msdb..backupset where [type] = ''D'' or [type] = ''L'' or [type]=''I'' group by database_name, [type] SELECT (select backup_finish_date from #tempbackup where type = @_msparam_0 and db_id(database_name) = dtb.database_id) AS [LastLogBackupDate] FROM master.sys.databases AS dtb WHERE (dtb.name=@_msparam_1) <mailto:dtb.name=@_msparam_1)> drop table #tempbackup ',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'L',@_msparam_1=N'RageAgainstTheMachine'

 

So the answer to your question Kev is

Yes it does get the information from the msdb database