Как отследить удаление таблиц и изменения в базе данных 1С на уровне MS SQL Server?

← На главную

В процессе администрирования баз данных 1С иногда возникают инциденты, когда нарушается целостность структуры базы на уровне СУБД: например, метаданные 1С содержат табличную часть документа или справочника, а физическая таблица в Microsoft SQL Server оказывается удалена. В таких ситуациях платформа 1С при попытке обращения к данным генерирует критическую ошибку. Разберем, как встроенными средствами MS SQL Server установить точное время операции, виновную учетную запись, имя хоста и клиентского приложения, а также как настроить превентивный контроль для предотвращения подобных инцидентов в будущем.

Способ 1. Расследование через стандартную трассировку (Default Trace)

По умолчанию в MS SQL Server включена фоновая трассировка (Default Trace), которая фиксирует события создания, изменения и удаления объектов схемы базы данных. Это первый и самый быстрый источник информации, если инцидент произошел недавно и файлы трассировки еще не перезаписались.

Проанализируем состояние системных трассировок с помощью функции sys.fn_trace_getinfo. Выполним следующий запрос в SQL Server Management Studio:


SELECT * 
FROM sys.fn_trace_getinfo(NULL);

В выводе этого запроса найдем строку со свойством property = 2 для активной трассировки (где property = 5 и значение равно 1). Это значение содержит физический путь к текущему файлу трассировки с расширением .trc.

Чтобы извлечь информацию обо всех операциях удаления и изменения объектов базы данных, воспользуемся функцией sys.fn_trace_gettable, отфильтровав события по классам EventClass IN (46, 47, 164) (события Object:Created, Object:Deleted, Object:Altered):


DECLARE @TracePath NVARCHAR(260);

-- Получаем путь к текущему файлу Default Trace
SELECT @TracePath = CAST(value AS NVARCHAR(260))
FROM sys.fn_trace_getinfo(NULL)
WHERE property = 2 AND traceid = 1;

-- Читаем события удаления и изменения структуры
SELECT 
    StartTime,
    EventClass,
    DatabaseName,
    TargetObjectName,
    LoginName,
    NTUserName,
    HostName,
    ApplicationName,
    SPID
FROM sys.fn_trace_gettable(@TracePath, DEFAULT)
WHERE EventClass IN (46, 47, 164)
  AND DatabaseName = 'ИмяВашейБазы1С'
ORDER BY StartTime DESC;

В полученном результате мы увидим точное время выполнения команды, имя удаленной таблицы в колонке TargetObjectName, учетную запись (LoginName / NTUserName), имя компьютера (HostName) и программу, из которой был отправлен запрос (ApplicationName).

Способ 2. Чтение журнала транзакций (LDF и бэкапов TRN)

Если файлы стандартной трассировки уже перезаписались, обратимся к журналу транзакций базы данных. В MS SQL Server даже при простой модели восстановления (Simple) операции DDL (такие как DROP TABLE) фиксируются в журнале транзакций до тех пор, пока занимаемое ими пространство не будет усечено и перезаписано.

Для анализа активного журнала транзакций используем функцию sys.fn_dblog. Ищем операции удаления строк из системных таблиц метаданных:


USE ИмяВашейБазы1С;
GO

SELECT 
    [Current LSN],
    [Operation],
    [Context],
    [Transaction ID],
    [Transaction Name],
    [Begin Time],
    [Transaction SID]
FROM sys.fn_dblog(NULL, NULL)
WHERE [Operation] = 'LOP_DELETE_ROWS'
  AND [Context] = 'LCX_SCHEMA';

Если в базе данных используется полная модель восстановления (Full Recovery Model) и настроено регулярное создание резервных копий журнала транзакций, проанализируем созданные файлы резервных копий (.trn) с помощью функции sys.fn_dump_dblog:


SELECT 
    [Current LSN],
    [Operation],
    [Transaction Name],
    [Begin Time],
    [SPID]
FROM sys.fn_dump_dblog(
    NULL, NULL, N'DISK', 1, 
    N'C:\Backups\Log_Backup.trn',
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT
)
WHERE [Transaction Name] LIKE '%DROP%';

С помощью полученного идентификатора транзакции (Transaction ID) можно найти операцию LOP_BEGIN_XACT, которая покажет точное время начала удаления и SID учетной записи инициатора.

Способ 3. Проверка отложенных задач и регламентных заданий

Если прямого интерактивного подключения зафиксировано не было, проверим источники автоматического выполнения команд:

Задания SQL Server Agent: проанализируем историю выполнения заданий через системные таблицы msdb.dbo.sysjobs и msdb.dbo.sysjobhistory на предмет выполнения сторонних T-SQL скриптов или шагов операционной системы.
Планировщик задач Windows: проверим журнал Task Scheduler на сервере СУБД и сервере 1С на запуск утилит sqlcmd, PowerShell-скриптов или batch-файлов.

Превентивная защита: DDL-триггеры и расширенные события

Чтобы исключить потерю информации в будущем и мгновенно фиксировать любые изменения структуры базы данных 1С, настроим систему аудита.

1. Настройка DDL-триггера аудита на уровне базы данных:

Создадим служебную таблицу для логов и триггер, перехватывающий удаление и модификацию объектов:


USE ИмяВашейБазы1С;
GO

CREATE TABLE dbo.Audit_DDL_Log (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    EventDate DATETIME DEFAULT GETDATE(),
    EventType NVARCHAR(100),
    ObjectName NVARCHAR(255),
    ObjectType NVARCHAR(100),
    LoginName NVARCHAR(255),
    HostName NVARCHAR(255),
    ProgramName NVARCHAR(255),
    CommandText NVARCHAR(MAX)
);
GO

CREATE TRIGGER trg_Audit_DDL_Events
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @EventData XML = EVENTDATA();

    INSERT INTO dbo.Audit_DDL_Log (
        EventType,
        ObjectName,
        ObjectType,
        LoginName,
        HostName,
        ProgramName,
        CommandText
    )
    VALUES (
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)'),
        @EventData.value('(/EVENT_INSTANCE/ObjectType)[1]', 'NVARCHAR(100)'),
        @EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(255)'),
        @EventData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(255)'),
        @EventData.value('(/EVENT_INSTANCE/ApplicationName)[1]', 'NVARCHAR(255)'),
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)')
    );
END;
GO

Важное ограничение для баз данных 1С: никогда не используйте команду ROLLBACK внутри DDL-триггеров баз 1С для безусловного запрета DROP_TABLE. При штатном обновлении конфигурации платформа 1С выполняет реструктуризацию таблиц (создает временные таблицы с суффиксами _T, переносит данные и удаляет старые структуры). Попытка заблокировать удаление через откат транзакции приведет к невозможности выполнять штатные обновления базы 1С.

2. Настройка сессии Extended Events (Расширенные события):

Для непрерывного мониторинга без влияния на производительность рекомендуется создать сессию Extended Events на событие object_deleted:


CREATE EVENT SESSION [Audit_Table_Drops] ON SERVER 
ADD EVENT sqlserver.object_deleted(
    ACTION(
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.session_nt_username,
        sqlserver.server_principal_name,
        sqlserver.sql_text
    )
    WHERE ([sqlserver].[equal_i_sql_unicode_string]([database_name], N'ИмяВашейБазы1С'))
)
ADD TARGET package0.event_file(SET filename=N'C:\SQLAudit\TableDrops.xel')
WITH (STARTUP_STATE=ON);
GO

ALTER EVENT SESSION [Audit_Table_Drops] ON SERVER STATE = START;
GO

Комплексное применение встроенных трассировок, регулярных резервных копий журнала транзакций и механизмов Extended Events позволяет в кратчайшие сроки восстановить хронологию событий, выявить источник несанкционированных изменений и защитить инфраструктуру 1С от подобных инцидентов.