asked
Query all SQL Instances using Powershell script and place the output in a centralized db table
Hi Guys, I have to use a list of sql servers mentioned in the text file and execute a tsql script for each server and then place the output in a centralized database table. For example : - I wanna retrieve info about default data and log paths for all sql servers and place it in a table. Query I am using is DECLARE @DefaultData NVARCHAR(512) EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'DefaultData', @DefaultData OUTPUT DECLARE @DefaultLog NVARCHAR(512) EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'DefaultLog', @DefaultLog OUTPUT DECLARE @DefaultBackup NVARCHAR(512) EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'BackupDirectory', @DefaultBackup OUTPUT DECLARE @MasterData NVARCHAR(512) EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServerParameters', N'SqlArg0', @MasterData OUTPUT SELECT @MasterData = SUBSTRING(@MasterData, 3, 255) SELECT @MasterData = SUBSTRING(@MasterData, 1, LEN(@MasterData) - CHARINDEX('', REVERSE(@MasterData))) DECLARE @MasterLog NVARCHAR(512) EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServerParameters', N'SqlArg2', @MasterLog OUTPUT SELECT @MasterLog = SUBSTRING(@MasterLog, 3, 255) SELECT @MasterLog = SUBSTRING(@MasterLog, 1, LEN(@MasterLog) - CHARINDEX('', REVERSE(@MasterLog))) SELECT ISNULL(@DefaultData, @MasterData) DefaultData , ISNULL(@DefaultLog, @MasterLog) DefaultLog , ISNULL(@DefaultBackup, @MasterLog) DefaultBackup This query needs to get executed on every single sql server mentioned in the text file and place the output in a database table. This needs to run every night where table gets truncated and loads again. Any help would be appreciated?
Well one thing I would point out is if you are going to use PowerShell you can just as easily read those registry keys using the `HKLM:\` provider and `Get-ItemProperty`. The other way (my preferred method) is to get the `DefaultData` and `DefaultLog` via SMO properties, which may be much cleaner of a script. You are missing the table structure you want to use and how you plan on inserting the values. I don't have computer with me but something like this should work: Import-Module SQLPS function Get-DefaultProperty ([string[]]$server) { foreach ($s in $server) { $srv = new-object microsoft.sqlserver.management.smo.server $s $props = @{"Server"="";"BackupDir"="";"DataDir"="";"LogDir"=""} $result = New-Object PSObject -Property $props $result.Server = $s $result.BackupDir = $srv.Settings.BackupDirectory $result.DataDir = $srv.Settings.DefaultFile $result.LogDir = $srv.Settings.DefaultLog $results += $result } $results } $listServers = Get-Content ServerList.txt -Raw Get-DefaultProperty -server $listServers Your alternative also if you are stuck on using the T-SQL script is using Policy Based Management and [Central Management Server][1]. You could then query the results of the PBM that checks if the default properties are set for an instance. [1]:
https://msdn.microsoft.com/en-us/library/bb934126.aspx
Hi Shawn, Thank you very much for your response. The table structure is as below Table will have four columns each column has a not null constraint. Servername - column 1 DefaultDataPath - Column 2 DefaultLogPath - column 3 DeafultBackupPath - column4 I have thought of one process where I can execute a powershell script to pull this information and export to an excel and then use SSIS package to load in to SQL Server table. But this adds additional step and I have to do the same for 15 queries where I am pulling the info to a SQL Table. I just want to avoid SSIS and then use one powershell script to directly query each server and load in to a table. Planning to schedule the script to run as a SQL Agent job from CMS server each night. On every night it has to truncate the table and load fresh results. Thank you.
Hi Shawn, Thank you very much for your response. The table structure is as below Table will have four columns each column has a not null constraint. 1. Servername - column 1 2. DefaultDataPath - Column 2 3. DefaultLogPath - column 3 4. DeafultBackupPath - column4 I have thought of one process where I can execute a powershell script to pull this information and export to an excel and then use SSIS package to load in to SQL Server table. But this adds additional step and I have to do the same for 15 queries where I am pulling the info to a SQL Table. I just want to avoid SSIS and then use one powershell script to directly query each server and load in to a table. Planning to schedule the script to run as a SQL Agent job from CMS server each night. On every night it has to truncate the table and load fresh results. Thank you.
18 People are following this question.
