What Runs on the SQL Server when you run a PowerShell script?–Question from #SQLRelay

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

image

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

image

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

image

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

image

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

image

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

image

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s