SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Object:  SQL_SCALAR_FUNCTION  fun_FolderExist       Script Date: 2026-05-15 19:31:09.400 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[fun_FolderExist]')) DROP FUNCTION [dbo].[fun_FolderExist];
GO


CREATE FUNCTION [dbo].[fun_FolderExist] (
  @dirname VARCHAR(265))
RETURNS BIT
AS
BEGIN
/*
SQLServer User-Defined Function

PURPOSE
Check if folder exists.

Return value (BIT):
 0 (False) - Folder does not exist
 1 (True)  - Folder exists

EXAMPLES
SELECT dbo.fun_FolderExist('C:\Program Files')
1

SELECT dbo.fun_FolderExist('C:\xxxx')
0

-- IIF is available starting with SQL Server 2012
SELECT IIF(dbo.fun_FolderExist('C:\Program Files')=1,'Yes','No')
Yes

HISTORY
2015-11-24 - Created function
2024-02-09 - Changed history table
2026-01-23 - Changed return value type to BIT
2026-03-30 - Changed examples

TAGS
<program>
  <description>Check if folder exists</description>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2015-11-24</created>
  <lastmodified>2026-03-30</lastmodified>
</program>
*/
  DECLARE @fso INT
  DECLARE @hr INT
  DECLARE @ofolder INT
  DECLARE @name VARCHAR(265)
  DECLARE @result BIT

  EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @fso OUT;
  EXEC @hr = sp_OAMethod @fso, 'FolderExists', @ofolder OUT, @dirname;
  EXEC @hr = sp_OADestroy @fso;

  SET @result = @ofolder;
  RETURN @result;
END


GO

/****** Object:  SQL_STORED_PROCEDURE  prc_save_text_to_file       Script Date: 2026-05-15 19:31:09.400 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[prc_save_text_to_file]')) DROP PROCEDURE [dbo].[prc_save_text_to_file];
GO


CREATE PROCEDURE [dbo].[prc_save_text_to_file] (
  @text VARCHAR(MAX), 
  @filename VARCHAR(265))
AS
BEGIN
/*
SQLServer Stored Procedure

PURPOSE
Write text variable to ansi file.

OLE Automation Procedures has to be enabled.
Existing files will be overwritten.
If path does not exist, no error is raised. Check path before with function fun_FolderExist.
If file could not be created, no error is raised. Check file afterwards with function fun_FileExist.
Character set Windows-1252 is used, 1 byte per character.
Character set Windows-1252 includes ascii extended characters dec 128-159, except dec 129, 141, 143, 144 and 157.
Character set iso-8859-1 does not handle ascii extended characters dec 128-159 in a proper way, so this character set is unusable here.

Parameters
1 @text       VARCHAR(MAX)  = (nonbinary) text
2 @filename   VARCHAR(265)  = filespec

HISTORY
2017-01-25 - Created procedure
2017-01-31 - iso-8859-1 (Latin-1) character set is used
2025-06-06 - Windows-1252 character set is used

TAGS
<program>
  <description>Write text variable to ansi file</description>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2017-01-25</created>
  <lastmodified>2025-06-06</lastmodified>
</program>
*/
  DECLARE @objtoken INT

  EXEC sp_OACreate 'ADODB.Stream', @objtoken OUTPUT;
  EXEC sp_OASetProperty @objtoken, 'Type', 2;
  EXEC sp_OASetProperty @objtoken, 'ContentType', 'text/plain'
  EXEC sp_OASetProperty @objtoken, 'Encoding', 'quoted-printable'
  EXEC sp_OASetProperty @objtoken, 'Charset', 'Windows-1252'
  EXEC sp_OAMethod @objtoken, 'Open';
  EXEC sp_OAMethod @objtoken, 'WriteText', NULL, @text;
  EXEC sp_OAMethod @objtoken, 'SaveToFile', NULL, @filename, 2;
  EXEC sp_OAMethod @objtoken, 'Close';
  EXEC sp_OADestroy @objtoken;
END


GO

/****** Object:  SQL_STORED_PROCEDURE  prc_dump_survive_info_sqlserver       Script Date: 2026-05-15 19:31:09.400 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[prc_dump_survive_info_sqlserver]')) DROP PROCEDURE [dbo].[prc_dump_survive_info_sqlserver];
GO

CREATE PROCEDURE [dbo].[prc_dump_survive_info_sqlserver] (
  @dump_path VARCHAR(255) = '')
AS
BEGIN
/*
SQLServer Stored Procedure

PURPOSE
Generate sqlserver settings text file.

The text filename name is info_sqlserver.txt and is dumped to the give dump_path.
The dump_path must exist and will be checked.

PARAMETERS
 - 1 @dump_path VARCHAR(255)   Path to dump file to.

USED OBJECTS
 - master.sys.server_principals
 - master.sys.dm_server_services
 - master.sys.dm_os_sys_info
 - master.sys.dm_os_memory_clerks
 - master.sys.configurations
 - master.sys.master_files
 - master.sys.databases
 - master.sys.dm_os_memory_nodes
 - master.sys.dm_os_nodes
 - xp_readerrorlog (undocumented extended procedure)
 - xp_instance_regread (undocumented extended procedure)

SQLSERVER CONFIGURATION
 - OLE Automation has to be enabled.

EXAMPLES
EXEC dbo.prc_dump_survive_info_sqlserver @dump_path='C:\Data\MSSQL\survivekit'

HISTORY
2026-04-09 - Created procedure

TAGS
<program>
  <description>Generate sqlserver settings text file</description>
  <generic>0</generic>
  <minversion>12</minversion>
  <author>Gerrit Mantel</author>
  <created>2026-04-09</created>
  <lastmodified>2026-04-09</lastmodified>
</program>
*/
  SET NOCOUNT ON

  DECLARE @message VARCHAR(512)
  DECLARE @crlf VARCHAR(2)
  DECLARE @body NVARCHAR(MAX)
  DECLARE @dump_file VARCHAR(265)

  DECLARE @info_date VARCHAR(20)
  DECLARE @server_name NVARCHAR(128)
  DECLARE @machine_name NVARCHAR(64)
  DECLARE @instance_name NVARCHAR(32)
  DECLARE @domain_name NVARCHAR(64)
  DECLARE @computername NVARCHAR(64)
  DECLARE @version NUMERIC(5,1)
  DECLARE @release VARCHAR(50)
  DECLARE @edition NVARCHAR(128)
  DECLARE @product_version NVARCHAR(128)
  DECLARE @product_level NVARCHAR(128)
  DECLARE @product_update_level NVARCHAR(128)
  DECLARE @product_build_type NVARCHAR(128)
  DECLARE @patch_level NVARCHAR(128)
  DECLARE @install_date DATETIME
  DECLARE @service_account NVARCHAR(128)
  DECLARE @instant_file_init_enabled NVARCHAR(1)
  DECLARE @collation NVARCHAR(128)
  DECLARE @isintegratedsecurityonly INT
  DECLARE @mixed_mode INT
  DECLARE @is_clustered INT
  DECLARE @active_node NVARCHAR(64)
  DECLARE @node_name_list NVARCHAR(255)
  DECLARE @is_hadr_enabled INT
  DECLARE @filestream_configured_level INT
  DECLARE @filestream_effective_level INT
  DECLARE @filestream_share_name NVARCHAR(128)
  DECLARE @errorlog_path VARCHAR(128)
  DECLARE @default_data_path NVARCHAR(128)
  DECLARE @default_log_path NVARCHAR(128)
  DECLARE @default_backup_path NVARCHAR(128)
  DECLARE @tempdb_data_path NVARCHAR(128)
  DECLARE @tcp_listen_port INT
  DECLARE @errorlog TABLE (
    [Id] INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    [LogDate] DATETIME NULL,
    [ProcessInfo] NVARCHAR(100) NULL,
    [Text] NVARCHAR(4000) NULL);
  DECLARE @os NVARCHAR(128)
  DECLARE @logical_cpu_cnt INT
  DECLARE @hyperthread_ratio INT
  DECLARE @socket_count INT
  DECLARE @cores_per_socket INT
  DECLARE @physical_memory_mb INT
  DECLARE @min_mem INT
  DECLARE @max_mem INT
  DECLARE @max_dop INT
  DECLARE @cost_threshold INT
  DECLARE @tempdb_file_count INT
  DECLARE @model_recovery_model NVARCHAR(128)
  DECLARE @locked_pages INT
  DECLARE @startup_params NVARCHAR(MAX)

  SET @crlf = CHAR(13)+CHAR(10);

  IF ISNULL(@dump_path,'') = ''
  BEGIN
    SET @message = 'ERROR - No dump path given';
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  -- Strip '\' from dump_path
  IF RIGHT(@dump_path,1) = '\' SET @dump_path = LEFT(@dump_path,LEN(@dump_path)-1);

  -- Check if dump_path exists
  IF dbo.fun_FolderExist(@dump_path) = 0
  BEGIN
    SET @message = 'ERROR - Dump path "' + @dump_path + '" does not exist';
    RAISERROR(@message, 16, 1);
    RETURN 1;
  END

  -- Dump file name
  SET @dump_file = @dump_path + '\info_sqlserver.txt';

  -- Info date
  SET @info_date = CONVERT(VARCHAR,GETDATE(),120);

  -- Server name
  SET @server_name = @@SERVERNAME;

  -- Machine name
  SET @machine_name = CONVERT(NVARCHAR(64),SERVERPROPERTY('MachineName'));

  -- Instance name
  SET @instance_name = CONVERT(NVARCHAR(32),SERVERPROPERTY('InstanceName'));

  -- Domain name
  EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SYSTEM\ControlSet001\Services\Tcpip\Parameters', N'Domain', @domain_name OUTPUT

  -- Computername
  SET @computername = CONVERT(NVARCHAR(64),SERVERPROPERTY('ComputerNamePhysicalNetBIOS'));

  -- Version
  SET @version = CONVERT(NUMERIC(5,1), (@@MICROSOFTVERSION / 0x01000000) + ((@@MICROSOFTVERSION & 0x0FFF000) /0x010000) /100.0);

  -- Release
  SET @release = CASE @version 
    WHEN 7 THEN '7.0'
    WHEN 8 THEN 'SQL2000'
    WHEN 9 THEN 'SQL2005'
    WHEN 10 THEN 'SQL2008'
    WHEN 10.5 THEN 'SQL2008R2'
    WHEN 11 THEN 'SQL2012'
    WHEN 12 THEN 'SQL2014'
    WHEN 13 THEN 'SQL2016'
    WHEN 14 THEN 'SQL2017'
    WHEN 15 THEN 'SQL2019'
    WHEN 16 THEN 'SQL2022'
    WHEN 17 THEN 'SQL2025'
    ELSE '' END;
  
  -- Edition
  SET @edition = CONVERT(NVARCHAR(128),SERVERPROPERTY('Edition'));
  
  -- Product_version
  SET @product_version = CONVERT(NVARCHAR(128),SERVERPROPERTY('ProductVersion'));
  
  -- Product_level
  SET @product_level = CONVERT(NVARCHAR(128),SERVERPROPERTY('ProductLevel'));
  
  -- Product_ipdate_level
  SET @product_update_level = CONVERT(NVARCHAR(128),SERVERPROPERTY('ProductUpdateLevel'));
  
  -- Product_build_type
  SET @product_build_type = CONVERT(NVARCHAR(128),SERVERPROPERTY('ProductBuildType'));

  -- Patch_level
  SET @patch_level = @product_level;
  IF @product_update_level IS NOT NULL SET @patch_level = @patch_level + '-'+@product_update_level;
  IF @product_build_type IS NOT NULL SET @patch_level = @patch_level + '-'+@product_build_type;
  
  -- Install_date
  SET @install_date = (SELECT [create_date] FROM master.sys.server_principals WHERE [sid] = 0x010100000000000512000000);
  
  -- Service_account
  SET @service_account = (SELECT [service_account] FROM master.sys.dm_server_services WHERE [filename] LIKE '%sqlservr%');
  
  -- Instant_file_init_enabled
  -- Field instant_file_initialization_enabled is only available from sql2012-SP4 and sql2016-SP1
  SET @instant_file_init_enabled = (SELECT [instant_file_initialization_enabled] FROM master.sys.dm_server_services WHERE [filename] LIKE '%sqlservr%');

  -- Collation
  SET @collation = CONVERT(NVARCHAR(128),SERVERPROPERTY('Collation'));
  
  -- Mixed_mode
  SET @isintegratedsecurityonly = CONVERT(INT,SERVERPROPERTY('IsIntegratedSecurityOnly'));
  IF @isintegratedsecurityonly = 1 SET @mixed_mode = 0 ELSE SET @mixed_mode = 1;

  -- Is_clustered
  SET @is_clustered = CONVERT(INT,SERVERPROPERTY('IsClustered'));

  -- Active_node and node_name_list (on clusters only)
  IF @is_clustered = 1
  BEGIN
    SET @active_node = @computername;
    SET @node_name_list = '';
    SELECT @node_name_list = @node_name_list+[NodeName]+', ' FROM sys.dm_os_cluster_nodes ORDER BY [NodeName];
    SET @node_name_list = LEFT(@node_name_list,LEN(@node_name_list)-1);
  END

  -- Is_hadr_enabled
  SET @is_hadr_enabled = CONVERT(INT,SERVERPROPERTY('IsHadrEnabled'));
  
  -- Filestream_configured_level
  SET @filestream_configured_level = CONVERT(INT,SERVERPROPERTY('FilestreamConfiguredLevel'));

  -- Filestream_effective_level
  SET @filestream_effective_level = CONVERT(INT,SERVERPROPERTY('FilestreamEffectiveLevel'));

  -- Filestream_share_name
  SET @filestream_share_name = CONVERT(NVARCHAR(128),SERVERPROPERTY('FilestreamShareName'));

  -- Errorlog_path
  -- Serverproperty ErrorLogFileName is undocumented
  SET @errorlog_path = REPLACE(CONVERT(VARCHAR(128),SERVERPROPERTY('ErrorLogFileName')),'\ERRORLOG','');
  
  -- Default_data_path
  -- Serverproperty InstanceDefaultDataPath is only available on sql2012 and higher
  SET @default_data_path = CONVERT(NVARCHAR(128),SERVERPROPERTY('InstanceDefaultDataPath'));
  IF RIGHT(@default_data_path,1) = '\' SET @default_data_path = LEFT(@default_data_path,LEN(@default_data_path)-1);
  
  -- Default_log_path
  -- Serverproperty InstanceDefaultLogPath is only available on sql2012 and higher
  SET @default_log_path = CONVERT(NVARCHAR(128), SERVERPROPERTY('InstanceDefaultLogPath'));
  IF RIGHT(@default_log_path,1) = '\' SET @default_log_path = LEFT(@default_log_path,LEN(@default_log_path)-1);

  -- Default_backup_path
  -- Serverproperty InstanceDefaultBackupPath is only available on sql2019 and higher
  SET @default_backup_path = CONVERT(NVARCHAR(260), SERVERPROPERTY('InstanceDefaultBackupPath'));
  IF RIGHT(@default_backup_path,1) = '\' SET @default_backup_path = LEFT(@default_backup_path,LEN(@default_backup_path)-1);

  -- Tempdb_data_path
  SET @tempdb_data_path = (SELECT REPLACE(physical_name,'tempdb.mdf','') FROM tempdb.sys.database_files WHERE physical_name LIKE '%tempdb.mdf')

  -- Tcp_listen_port
  INSERT INTO @errorlog EXEC xp_readerrorlog 0, 1, N'Server is listening on', N'ipv4', NULL, NULL, 'ASC';
  SET @tcp_listen_port = (SELECT CONVERT(INT,SUBSTRING([Text], CHARINDEX('>',[Text])+1, CHARINDEX(']',[Text])-CHARINDEX('>',[Text])-1)) FROM @errorlog WHERE [Id] = 1);
  
  -- Os
  SET @os = SUBSTRING(@@VERSION,CHARINDEX(' on ', @@VERSION)+4,LEN(@@VERSION)-4 -CHARINDEX(' on ', @@VERSION));

  -- Logical_cpu_count
  SET @logical_cpu_cnt = (SELECT cpu_count FROM master.sys.dm_os_sys_info);
  
  -- Hyperthread_ratio
  SET @hyperthread_ratio = (SELECT hyperthread_ratio FROM master.sys.dm_os_sys_info);
  
  -- Socket_count
  -- From SQL2014 and upwards socket_count is available in master.sys.dm_os_sys_info
  SET @socket_count = (SELECT socket_count FROM master.sys.dm_os_sys_info);
  
  -- Cores per socket
  -- From SQL2014 and upwards cores_per_socket is available in master.sys.dm_os_sys_info
  SET @cores_per_socket = (SELECT cores_per_socket FROM master.sys.dm_os_sys_info);

  -- Physical_memory_mb
  -- From SQL2012 and upwards physical memory is in physical_memory_kb
  SET @physical_memory_mb = (SELECT CONVERT(INT,ROUND(physical_memory_kb/1024.0,0)) FROM master.sys.dm_os_sys_info);
  
  -- Min_mem parameter
  SET @min_mem = (SELECT CONVERT(INT,[value]) FROM master.sys.configurations WHERE [configuration_id] = 1543); 

  -- Max_mem parameter
  SET @max_mem = (SELECT CONVERT(INT,[value]) FROM master.sys.configurations WHERE [configuration_id] = 1544); 

  -- Max_dop parameter
  SET @max_dop = (SELECT CONVERT(INT,[value]) FROM master.sys.configurations WHERE [configuration_id] = 1539); 

  -- Cost_threshold parameter for parallellism
  SET @cost_threshold = (SELECT CONVERT(INT,[value]) FROM master.sys.configurations WHERE [configuration_id] = 1538); 

  -- Tempdb_file_count (number of tempdb datafiles)
  SET @tempdb_file_count = (SELECT COUNT(*) FROM master.sys.master_files WHERE DB_NAME(database_id) = 'tempdb' AND [type_desc] = 'ROWS')
  
  -- Model_recovery_model (recovery model of the model database)
  SET @model_recovery_model = (SELECT [recovery_model_desc] FROM master.sys.databases WHERE DB_NAME(database_id) = 'model')

  -- Locked_pages
  -- Tables master.sys.dm_os_memory_nodes and master.sys.dm_os_nodes only are present in version SQL2008 (10.0) and higher
  SET @locked_pages = (SELECT CASE SUM(t1.locked_page_allocations_kb) WHEN 0 THEN 0 ELSE 1 END FROM master.sys.dm_os_memory_nodes t1 INNER JOIN master.sys.dm_os_nodes t2 on t2.memory_node_id = t1.memory_node_id WHERE t2.node_state_desc = 'ONLINE');

  -- Startup params
  -- From SQL2008R2-SP1 and upwards sys.dm_server_registry is available (needs VIEW SERVER STATE permission on SQL2022 and later)
  SET @startup_params = (SELECT STRING_AGG(LEFT(CONVERT(NVARCHAR(256),[value_data]),LEN(CONVERT(NVARCHAR(256),[value_data]))-1),CHAR(13)+CHAR(10)+'                            : ') WITHIN GROUP (ORDER BY [value_name] ASC)
  FROM sys.dm_server_registry
  WHERE [registry_key] LIKE N'%MSSQLServer\Parameters');
 
  SET @body =
    'SQL Server settings' + @crlf +
    '------------------------------'+ @crlf +
    'Info_date                   : ' + ISNULL(@info_date,'') + @crlf +
    'Server_name                 : ' + ISNULL(@server_name,'') + @crlf +
    'Machine_name                : ' + ISNULL(@machine_name,'') + @crlf +
    'Instance_name               : ' + ISNULL(@instance_name,'') + @crlf +
    'Domain_name                 : ' + ISNULL(@domain_name,'') + @crlf +
    'Computername                : ' + ISNULL(@computername,'') + @crlf +
    'Version                     : ' + ISNULL(CONVERT(VARCHAR,@version),'') + @crlf +
    'Release                     : ' + ISNULL(@release,'') + @crlf +
    'Edition                     : ' + ISNULL(CONVERT(VARCHAR(128),@edition),'') + @crlf +
    'Product_version             : ' + ISNULL(@product_version,'') + @crlf +
    'Patch_level                 : ' + ISNULL(@patch_level,'') + @crlf +
    'Install_date                : ' + ISNULL(CONVERT(VARCHAR,@install_date,120),'') + @crlf +
    'Service_account             : ' + ISNULL(@service_account,'') + @crlf +
    'Instant_file_init_enabled   : ' + ISNULL(@instant_file_init_enabled,'') + @crlf +
    'Collation                   : ' + ISNULL(@collation,'') + @crlf +
    'Mixed_mode                  : ' + ISNULL(CONVERT(VARCHAR,@mixed_mode),'') + @crlf +
    'Is_clustered                : ' + ISNULL(CONVERT(VARCHAR,@is_clustered),'') + @crlf +
    'Active_node                 : ' + ISNULL(@active_node,'') + @crlf +
    'Node_name_list              : ' + ISNULL(@node_name_list,'') + @crlf +
    'Is_hadr_enabled             : ' + ISNULL(CONVERT(VARCHAR,@is_hadr_enabled),'') + @crlf +
    'Filestream_configured_level : ' + ISNULL(CONVERT(VARCHAR,@filestream_configured_level),'') + @crlf +
    'Filestream_effective_level  : ' + ISNULL(CONVERT(VARCHAR,@filestream_effective_level),'') + @crlf +
    'Filestream_share_name       : ' + ISNULL(@filestream_share_name,'') + @crlf +
    'Errorlog_path               : ' + ISNULL(@errorlog_path,'') + @crlf +
    'Default_data_path           : ' + ISNULL(@default_data_path,'') + @crlf +
    'Default_log_path            : ' + ISNULL(@default_log_path,'') + @crlf +
    'Default_backup_path         : ' + ISNULL(@default_backup_path,'') + @crlf +
    'Tempdb_data_path            : ' + ISNULL(@tempdb_data_path,'') + @crlf +
    'Tcp_listen_port             : ' + ISNULL(CONVERT(VARCHAR,@tcp_listen_port),'') + @crlf +
    'OS                          : ' + ISNULL(@os,'') + @crlf +
    'Logical_cpu_cnt             : ' + ISNULL(CONVERT(VARCHAR,@logical_cpu_cnt),'') + @crlf +
    'Hyperthread_ratio           : ' + ISNULL(CONVERT(VARCHAR,@hyperthread_ratio),'') + @crlf +
    'Socket_count                : ' + ISNULL(CONVERT(VARCHAR,@socket_count),'') + @crlf +
    'Cores_per_socket            : ' + ISNULL(CONVERT(VARCHAR,@cores_per_socket),'') + @crlf +
    'Physical_memory_mb          : ' + ISNULL(CONVERT(VARCHAR,@physical_memory_mb),'') + @crlf +
    'Min_mem                     : ' + ISNULL(CONVERT(VARCHAR,@min_mem),'') + @crlf +
    'Max_mem                     : ' + ISNULL(CONVERT(VARCHAR,@max_mem),'') + @crlf +
    'Max_dop                     : ' + ISNULL(CONVERT(VARCHAR,@max_dop),'') + @crlf +
    'Cost_threshold              : ' + ISNULL(CONVERT(VARCHAR,@cost_threshold),'') + @crlf +
    'Tempdb_file_count           : ' + ISNULL(CONVERT(VARCHAR,@tempdb_file_count),'') + @crlf +
    'Model_recovery_model        : ' + ISNULL(@model_recovery_model,'') + @crlf +
    'Locked_pages                : ' + ISNULL(CONVERT(VARCHAR,@locked_pages),'') + @crlf +
    'Startup_params              : ' + ISNULL(@startup_params,'') + @crlf;

  -- Dump script
  EXEC [dbo].[prc_save_text_to_file] @body, @dump_file;
END
GO