SQL 옵션 값 (Registry)

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