Many DBAs rely on Windows Registry search to get info about SQL Server Instances. It is not necessary to go through the registry for fetching SQL Server information. In this article, we will learn how to use TSQL and retrieve SQL Server information from Windows Registry.
Get the permission:
Before running the script, user account or the SQL Service account that would be running this script should have view server state permission on all servers from which data would be collected. If you run the script without view server state permission, you would receive this error
Msg 300, Level 14, State 1, Line 1
VIEW SERVER STATE permission was denied on object ‘servername’, database ‘master’.
Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.
What’s in the script?
Through this script, we are creating 2 tables. In TBL1, we will be storing all server names and the TBL2 we would be saving the registry data collected from all servers. For each server listed in TBL1, you should have created linked server. Dynamic SQL statements are created during runtime and it is executed on each linked server to fetch the data. Windows registry of each SQL Server stores the installation and configuration information. We are using the dynamic management view sys.dm_server_registry to read all those data from the registry.
Script:
CREATE TABLE TBL1(cservername VARCHAR(200));
CREATE TABLE TBL2
(cservername VARCHAR(200),
ckey VARCHAR(1000),
cvalue VARCHAR(200),
cdata SQL_VARIANT
);
INSERT INTO TBL1
VALUES('SERVER1');
DECLARE @cmd NVARCHAR(4000);
DECLARE @cmd2 NVARCHAR(4000);
DECLARE @vsvrname VARCHAR(200);
DECLARE cur1 CURSOR
FOR SELECT cservername
FROM TBL1;
OPEN cur1;
FETCH NEXT FROM cur1 INTO @vsvrname;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @vsvrname;
SET @cmd2 = 'INSERT into TBL2 SELECT @@servername, * FROM ['+@vsvrname+'].master.sys.dm_server_registry';
PRINT @cmd2;
EXEC (@cmd2);
FETCH NEXT FROM cur1 INTO @vsvrname;
END;
CLOSE cur1;
DEALLOCATE cur1;
GO
Tweak it:
As of now, the script collects all information from the registry. By adding the where clause you can filter the data set to fetch specific information. For example, you can add %SQLAgent% keyword to the where clause and fetch information about SQL Server and SQL Server agent services. ‘%SQLAgent%’ in where clause would return information about SQL Server Agent registry keys as well. ‘CurrentVersion’ in the where clause would return information about the version of instances of SQL Server. By adding ‘%Parameters’ in the where clause you can get information about parameters that are set as startup parameters for SQL Server instance.
Alternate method
If you want to try an alternate method, you can create registered servers and then run this script in a new query window against all registered servers. You would see the result along with the server name. This will help you to save time but you have to manually copy the result into an Excel sheet and then import into a SQL Server table to analyze it. It is good practice to back up your databases at regular intervals because sql corruption will make databases unusable and you might lose all data saved in it.
Select * from sys.dm_server_registry
Author Introduction:
Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including Outlook recovery and excel recovery software products. For more information visit www.datanumen.com
Leave a Reply