SQL Server 2017 RC1 dropping recently reminded me of a couple things I wanted to blog about finding in there. One that I thought was rather interesting is a new iTVF called
dm_os_enumerate_filesystem. It looks like a partial replacement for
xp_cmdshell in that, well, you can do the equivalent of running a
dir command, with some filtering.
The addition of a search filter is particularly nice, since the
dir command isn’t exactly robust in that area. If you’ve ever wanted to filter on a date, well… That’s probably when PowerShell got invented.
If I run a simple call to the new function like so…
SELECT * FROM sys.dm_os_enumerate_filesystem('C:\', '*')
I get this back:
This can be particularly useful for backup tasks, or folder watching tasks. For instance, I can look in a backup directory for recent backups with a size greater than 0 bytes that haven’t been written to in the last 30 seconds (a pretty good sign that a backup is completed, no?)
SELECT * FROM sys.dm_os_enumerate_filesystem('C:\Backups\', '*.bak') WHERE creation_time >= GETDATE() AND last_access_time <= DATEADD(SECOND, -30, last_write_time) AND size_in_bytes > 0
And that’s a heck of a neck easier than what we have to do, currently.
Thanks for reading!