T-SQL Tuesday: Multiple ways to skin a cat

This month’s T-SQL Tuesday’s topic is Powershell.  Powershell is a tool I love greatly because it makes automating easier than with other scripting languages that I’ve used.  Among the things that come in handy are the SMO hooks that you can use to query SQL directly from Powershell.  While working on a script that could be used to restore databases I found that the SMO objects are not quite as direct as you might think.

Here’s the Powershell code to pull the details for a backup file:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO" ) | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "DatabaseServer"
$backupDevice = New-Object ("Microsoft.SqlServer.Management.Smo.BackupDeviceItem" ) ("C:\Backup\Backup.bak", "File")
$smoRestore = new-object("Microsoft.SqlServer.Management.Smo.Restore")
$smoRestoreDetails = $smoRestore.ReadBackupHeader($server)

During testing I had put in a local path to the backup file (as shown above). This is the output I was greeted with:

Exception calling “ReadBackupHeader” with “1” argument(s): “An exception occurred while executing a Transact-SQL statement or batch.”
At line:14 char:1
+ $smoRestoreDetails = $smoRestore.ReadBackupHeader($server)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ExecutionFailureException

I found the message referring to a Transac-SQL statement interesting so I fired up Profiler against the server and found that this was being executed there:


Which means you could accomplish the exact same task by running this Powershell to call the SQL directly which is five fewer lines of code:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO" ) | Out-Null
$smoRestoreDetails = Invoke-Sqlcmd -ServerInstance DatabaseServer -Query "RESTORE HEADERONLY FROM  DISK = N'C:\Backup\Backup.bak' WITH  NOUNLOAD"

Now, I had been trying to focus on using SMO almost exclusively in my scripts to stick with pure Powershell but this discovery had me wondering if that is actually a good idea.  Here I was going to be checking a lot of files for the backup finish date and it would be much easier to update the SQL string and run that instead of add and remove devices from a restore object.  What other cases are you better off sticking to T-SQL?

Lets look at the case where you’re trying to find what databases are in full recovery mode and when the last log backup is.  Here’s the code with some timers built in to see how long each step takes:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO" ) | Out-Null
$timer = New-Object System.Diagnostics.Stopwatch
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "DatabaseServer"
$DatabasesSMO = $server.Databases | Select-Object Name, RecoveryModel, LastLogBackupDate
$DatabasesTSQL = Invoke-Sqlcmd -ServerInstance DatabaseServer -Query "select d.name, recovery_model_desc, [LastLogBackup] = max(bs.backup_finish_date)
from sys.databases d
left outer join msdb.dbo.backupset bs on d.name = bs.database_name
	and bs.type ='L'
group by d.name, is_auto_shrink_on, page_verify_option_desc, recovery_model_desc"

On first run the TSQL method takes much longer but on subsequent runs it takes less time.  On first run the SMO method takes 0.8 seconds while the TSQL method takes 7.7 seconds.  On a second run in the same Powershell window SMO takes 0.05 seconds and TSQL takes 0.001 seconds.  Looking at the Profiler trace for completed procedures and completed batches gives us an interesting look into how SMO does it’s thing:

SMO makes many calls (for each database) and does a lot more reads than the TSQL method.  Why the first connection takes much longer using Invoke-Sqlcmd I’m not entirely sure but the work isn’t being done on the SQL server (you can confirm yourself by adding logins to the trace).

This doesn’t mean that you shouldn’t use SMO to do work in Powershell.  But you should be aware that there are multiple ways to get the job done and that you should choose what’s most appropriate for the situation.  When working with SQL in Powershell what’s your preference, SMO or TSQL calls?

Leave a Reply

Your email address will not be published. Required fields are marked *