declare @SmoAuditLevel int exec master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'AuditLevel', @SmoAuditLevel OUTPUT
-- None = 0, Successful Logins Only = 1, Failed Logins Only = 2, Both Failed and Successful Logins = 3
declare @NumErrorLogs int exec master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'NumErrorLogs', @NumErrorLogs OUTPUT
declare @SmoLoginMode int exec master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'LoginMode', @SmoLoginMode OUTPUT
declare @SmoDefaultFile nvarchar(512) exec master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultData', @SmoDefaultFile OUTPUT
declare @SmoDefaultLog nvarchar(512) exec master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultLog', @SmoDefaultLog OUTPUT
declare @SmoMailProfile nvarchar(512) exec master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'MailAccountName', @SmoMailProfile OUTPUT
declare @BackupDirectory nvarchar(512) exec master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'BackupDirectory', @BackupDirectory OUTPUT
declare @SmoPerfMonMode int exec master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'Performance', @SmoPerfMonMode OUTPUT
if @SmoPerfMonMode is null begin set @SmoPerfMonMode = 1000 end
SELECT
@SmoAuditLevel AS [AuditLevel],
ISNULL(@NumErrorLogs, -1) AS [NumberOfLogFiles],
(case when @SmoLoginMode < 3 then @SmoLoginMode else 9 end) AS [LoginMode],
ISNULL(@SmoDefaultFile,N'') AS [DefaultFile],
ISNULL(@SmoDefaultLog,N'') AS [DefaultLog], -1 AS [TapeLoadWaitTime],
ISNULL(@SmoMailProfile,N'') AS [MailProfile],
@BackupDirectory AS [BackupDirectory],
@SmoPerfMonMode AS [PerfMonMode]
# Update
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 2