sys.dm_os_host_info–DMV to find Operating System Information in SQL Server 2017

SQL Server 2017 includes a new DMV to find Operating System information

sys.dm_os_host_info

SELECT host_platform,
host_distribution,
host_release,
host_service_pack_level,
host_sku,
os_language_version
FROM   sys.dm_os_host_info 

Output from Windows Server

Output from Ubuntu Linux Server

This DMV will come in handy when writing maintenance scripts based on Operating System ex. Backup Script to get custom backup path based on OS

DECLARE @BackupPath VARCHAR(500)
DECLARE @OSInfo NVARCHAR(256)

SELECT @OSInfo = host_platform
FROM   sys.dm_os_host_info

IF @OSInfo = ‘Windows’
BEGIN
SET @BackupPath = ‘E:\SQLBackup\’
END
ELSE IF @OSInfo = ‘Linux’
BEGIN
SET @BackupPath = ‘/var/opt/BACKUP/’
END

SELECT @BackupPath [Backup Path] 

Output from Windows Server

Output from Ubuntu Linux Server             

Hope you find this post helpful !!!

Author: Arunraj

I am a Microsoft Certified Technology Specialist (Database Developer). I work on SQL Server programming since SQL Server 7.0 specializes in SQL Server Programming and Performance Tuning and has 14 years of hands-on experience. I hold a Master Degree in Computer Applications. I am also part of NJSQL User Group and Northern New Jersey .Net User Group. View all posts by Arunraj