MS SQL Server дело тонкое...: Использование fn_dblog, fn_dump_dblog и восстановление с STOPBEFOREMARK до LSN

Автор: Paul Randal, Using fn_dblog, fn_dump_dblog, and restoring with STOPBEFOREMARK to an LSN

Я уже много писал в блоге о недокументированной функции fn_dblog, в написании которой я помогал (и мне ещё многое предстоит :-)), но вот одна функция, которую я ранее не упоминал в блоге: fn_dump_dblog (хотя я рассказывал о ней на конференциях).

Рассмотрим сценарий: кто-то удалил таблицу, и вы хотите выяснить, когда это произошло и, возможно, кто это сделал. Трассировка по умолчанию также переполнилась, поэтому вы больше не можете получить оттуда информацию об операции DDL.

Если записи журнала транзакций об операции DROP ещё не были очищены из активной части журнала, то вы сможете использовать fn_dblog для поиска нужной информации. Вы даже можете просмотреть неактивную часть журнала, используя трассировочный флаг 2536, который указывает средству чтения журнала игнорировать точку усечения журнала и выводить все возможные записи журнала.

Но что делать, если соответствующие записи журнала больше не существуют в текущем журнале? Они остались только в ваших резервных копиях журнала. Вы могли бы кропотливо восстанавливать резервные копии журнала по несколько секунд, пока не найдёте момент, когда произошло удаление, а затем восстановить данные до этого момента.

Или вы можете сэкономить уйму времени и использовать fn_dump_dblog, которая позволяет извлекать и искать записи журнала непосредственно из файла резервной копии журнала, не восстанавливая базу данных!

Редакция от 15.08.13: Осторожно — Джонатан недавно обнаружил на системе заказчика, которая активно использует эту функцию, что каждый вызов fn_dump_dblog создаёт новый скрытый планировщик SQLOS и до трёх потоков, которые не исчезают (и не используются повторно) до перезапуска сервера. Это ошибка, которую команда разработчиков SQL собирается исправить, теперь, когда мы их предупредили. Используйте с осторожностью.

Редакция от 15.05.15: Ошибка исправлена в SQL Server 2012 SP2 и выше, а также в SQL Server 2014. Исправление не будет перенесено в более ранние версии.

Поиск операции DROP в журнале

Вот пример — я создам таблицу, заполню её, создам резервную копию, а затем удалю её.

USE [master];
GO

CREATE DATABASE [FNDBLogTest];
GO
USE [FNDBLogTest];
GO
SET NOCOUNT ON;
GO

-- Создадим таблицы для работы
CREATE TABLE [ProdTable] (
    [c1] INT IDENTITY,
    [c2] DATETIME DEFAULT GETDATE (),
    [c3] CHAR (25) DEFAULT 'a');

CREATE TABLE [ProdTable2] (
    [c1] INT IDENTITY,
    [c2] DATETIME DEFAULT GETDATE (),
    [c3] CHAR (25) DEFAULT 'a');
GO

INSERT INTO [ProdTable] DEFAULT VALUES;
GO 1000

-- Создадим начальные резервные копии
BACKUP DATABASE [FNDBLogTest] TO DISK = N'D:\SQLskills\FNDBLogTest_Full.bak' WITH INIT;
GO
BACKUP LOG [FNDBLogTest] TO DISK = N'D:\SQLskills\FNDBLogTest_Log1.bak' WITH INIT;
GO

INSERT INTO [ProdTable2] DEFAULT VALUES;
GO 1000

Теперь я удалю таблицу и добавлю ещё несколько записей в журнал:

DROP TABLE [ProdTable];
GO

INSERT INTO [ProdTable2] DEFAULT VALUES;
GO 1000

Теперь как я могу найти момент, когда таблица была удалена?

SELECT
    [Current LSN],
    [Operation],
    [Context],
    [Transaction ID],
    [Description]
FROM
    fn_dblog (NULL, NULL),
    (SELECT
        [Transaction ID] AS [tid]
    FROM
        fn_dblog (NULL, NULL)
    WHERE
        [Transaction Name] LIKE '%DROPOBJ%') [fd]
WHERE
    [Transaction ID] = [fd].[tid];
GO

Current LSN            Operation       Context           Transaction ID Description
---------------------- --------------- ----------------- -------------  --------------------------------
0000009d:0000021e:0001 LOP_BEGIN_XACT  LCX_NULL          0000:00001ff7  DROPOBJ; <сокращено>
0000009d:0000021e:0002 LOP_LOCK_XACT   LCX_NULL          0000:00001ff7
0000009d:0000021e:0003 LOP_LOCK_XACT   LCX_NULL          0000:00001ff7
0000009d:0000021e:0008 LOP_MODIFY_ROW  LCX_IAM           0000:00001ff7
0000009d:0000021e:0009 LOP_MODIFY_ROW  LCX_PFS           0000:00001ff7  Deallocated 0001:0000009b
0000009d:0000021e:000a LOP_MODIFY_ROW  LCX_IAM           0000:00001ff7
0000009d:0000021e:000b LOP_MODIFY_ROW  LCX_PFS           0000:00001ff7  Deallocated 0001:0000009c
0000009d:0000021e:000c LOP_MODIFY_ROW  LCX_IAM           0000:00001ff7
0000009d:0000021e:000d LOP_MODIFY_ROW  LCX_PFS           0000:00001ff7  Deallocated 0001:0000009d
0000009d:0000021e:000e LOP_MODIFY_ROW  LCX_IAM           0000:00001ff7
0000009d:0000021e:000f LOP_MODIFY_ROW  LCX_PFS           0000:00001ff7  Deallocated 0001:0000009e
0000009d:0000021e:0010 LOP_MODIFY_ROW  LCX_IAM           0000:00001ff7
0000009d:0000021e:0011 LOP_MODIFY_ROW  LCX_PFS           0000:00001ff7  Deallocated 0001:0000009f
0000009d:0000021e:0012 LOP_MODIFY_ROW  LCX_PFS           0000:00001ff7  Deallocated 0001:0000009a
0000009d:0000021e:0013 LOP_HOBT_DDL    LCX_NULL          0000:00001ff7  Action 3 on HoBt 0xd:100 <сокращено>
0000009d:0000021e:0014 LOP_DELETE_ROWS LCX_MARK_AS_GHOST 0000:00001ff7
0000009d:0000021e:0032 LOP_LOCK_XACT   LCX_NULL          0000:00001ff7
...сокращено...

Здорово, правда?

Теперь я создам ещё одну резервную копию журнала, которая очистит журнал и будет содержать только что просмотренные записи.

BACKUP LOG [FNDBLogTest] TO DISK = N'D:\SQLskills\FNDBLogTest_Log2.bak' WITH INIT;
GO

Кто выполнил DROP?

Если вы хотите выяснить, кто выполнил команду DROP, найдите поле Transaction SID для записи журнала LOP_BEGIN_XACT транзакции удаления и затем передайте это значение функции SUSER_SNAME(). Всё просто!

Восстановление с использованием STOPBEFOREMARK

LSN для записи журнала LOP_BEGIN_XACT — это точка, до которой мне нужно восстановиться.

Для этого можно просто подставить LSN в параметр STOPBEFOREMARK для команды RESTORE. Параметр задокументирован, но его формат — нет! Как полезно!!

Перед LSN необходимо указать «0x», а формат должен точно соответствовать тому, который возвращает fn_dblog.

Таким образом, последовательность восстановления до момента непосредственно перед удалением будет следующей:

RESTORE DATABASE [FNDBLogTest2]
    FROM DISK = N'D:\SQLskills\FNDBLogTest_Full.bak'
WITH
    MOVE N'FNDBLogTest' TO N'C:\SQLskills\FNDBLogTest2.mdf',
    MOVE N'FNDBLogTest_log' TO N'C:\SQLskills\FNDBLogTest2_log.ldf',
    REPLACE, NORECOVERY;
GO

RESTORE LOG [FNDBLogTest2]
    FROM DISK = N'D:\SQLskills\FNDBLogTest_Log1.bak'
WITH
    NORECOVERY;
GO

RESTORE LOG [FNDBLogTest2]
FROM
    DISK = N'D:\SQLskills\FNDBLogTest_Log2.bak'
WITH
    STOPBEFOREMARK = 'lsn:0x0000009d:0000021e:0001',
    NORECOVERY;
GO

RESTORE DATABASE [FNDBLogTest2] WITH RECOVERY;
GO

И таблица снова существует, прямо перед тем моментом, когда она была удалена. Вы видите, где я использовал сформированную строку LSN в последнем восстановлении журнала.

Использование fn_dump_dblog

Что делать, если записи журнала больше не находятся в текущем журнале? Я могу использовать функцию fn_dump_dblog.

Например, вот как я могу использовать её для просмотра резервной копии FNDBLogTest_Log2.bak:

SELECT
    COUNT (*)
FROM
    fn_dump_dblog (
        NULL, NULL, N'DISK', 1, N'D:\SQLskills\FNDBLogTest_Log2.bak',
        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);
GO

Мне необходимо указать все параметры DEFAULT (их 63!), иначе функция не сработает. Остальные параметры:

Начальный LSN (обычно просто NULL)
Конечный LSN (опять же, обычно NULL)
Тип файла (DISK или TAPE)
Номер резервной копии в файле (для наборов носителей с несколькими резервными копиями)
Имя файла

Таким образом, я могу выполнить тот же запрос, что и выше:

SELECT
    [Current LSN],
    [Operation],
    [Context],
    [Transaction ID],
    [Description]
FROM
    fn_dump_dblog (
        NULL, NULL, N'DISK', 1, N'D:\SQLskills\FNDBLogTest_Log2.bak',
        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),
    (SELECT
        [Transaction ID] AS [tid]
    FROM
        fn_dump_dblog (
            NULL, NULL, N'DISK', 1, N'D:\SQLskills\FNDBLogTest_Log2.bak',
            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 '%DROPOBJ%') [fd]
WHERE [Transaction ID] = [fd].[tid];
GO

Что работает отлично, но выполняется гораздо дольше.

Вам, наверное, интересно, для чего нужны все остальные параметры fn_dump_dblog? Они предназначены для указания семейств носителей набора носителей, который имеет более одного семейства носителей.

Вот пример использования резервной копии журнала, распределённой по двум файлам:

BACKUP LOG [FNDBLogTest] TO
    DISK = N'D:\SQLskills\FNDBLogTest_Log3_1.bak',
    DISK = N'D:\SQLskills\FNDBLogTest_Log3_2.bak'
WITH INIT;
GO

Если я попытаюсь использовать fn_dump_dblog и укажу только один файл, я получу ошибку:

SELECT
    COUNT (*)
FROM
    fn_dump_dblog (
        NULL, NULL, N'DISK', 1, N'D:\SQLskills\FNDBLogTest_Log3_1.bak',
        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);
GO

Msg 3132, Level 16, State 1, Line 1
The media set has 2 media families but only 1 are provided. All members must be provided.

Поэтому я должен указать оба семейства носителей:

SELECT
    COUNT (*)
FROM
    fn_dump_dblog (
        NULL, NULL, N'DISK', 1, N'D:\SQLskills\FNDBLogTest_Log3_1.bak',
        N'D:\SQLskills\FNDBLogTest_Log3_2.bak', 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);
GO

Итог

Вот и всё — несколько мощных инструментов, которые можно добавить в ваш арсенал восстановления после сбоев.

MS SQL Server дело тонкое...

1.12.25

Использование fn_dblog, fn_dump_dblog и восстановление с STOPBEFOREMARK до LSN

Поиск операции DROP в журнале

Кто выполнил DROP?

Восстановление с использованием STOPBEFOREMARK

Использование fn_dump_dblog

Итог

Комментариев нет:

Отправить комментарий

Архив блога

Ярлыки

www.sql-ex.ru