Я уже много писал в блоге о недокументированной функции 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
Итог
Вот и всё — несколько мощных инструментов, которые можно добавить в ваш арсенал восстановления после сбоев.
