Главное, что требуется от администратора, — уметь восстанавливать базы данных с минимальными потерями информации. А при решении таких задач необходимо опираться на две предпосылки, каждая из которых имеет жизненно важное значение. Во-первых, это хорошо организованная стратегия резервного копирования. А, во-вторых, вам не обойтись без тщательно отработанного процесса восстановления

В этой статье речь пойдет о том, как определить, насколько успешно выполняется ваша стратегия резервного копирования, а также о том, как вы удерживаете в допустимых пределах возможные объемы потерь информации в базах данных. Важнейший критерий в этом деле — список выполненных вами последних операций резервного копирования. Обсуждение таких вопросов, как хранение резервных копий в другом офисе для страховки на случай отказов серверов и сайта, а также тестирование резервных копий, мы отложим до следующего раза. Я хочу, чтобы вы сосредоточились на следующих вопросах:

  1. Как давно были созданы мои последние резервные копии?
  2. Имеются ли резервные копии всех моих баз данных (требующих восстановления)?
  3. Соответствует ли частота снятия резервных копий моим требованиям и стандартам?
  4. Придерживаюсь ли я корректной процедуры резервного копирования в контексте режима восстановления своей базы данных SQL?

На все эти вопросы я могу ответить с помощью одного запроса.

Запрос для определения времени выполнения резервных копий

Метаданные, касающиеся резервных копий, хранятся в системной базе данных SQL Server msdb. Всего существует четыре основных системных представления; для данного запроса нам понадобится два из них.

  • msdb.dbo.backupset. Это представление содержит основные метаданные по предыстории снятия резервных копий, включая сведения о времени формирования соответствующей резервной копии, имени выполнявшего эту операцию сотрудника, о типе резервной копии, ее размере и о том, подвергались ли данные сжатию; регистрационный номер транзакции в журнале, позволяющий воссоздавать цепочку резервирования для обеспечения порядка восстановления и многие другие данные.
  • msdb.dbo.backupmediafamily. Данное представление содержит сведения о месте расположения резервных файлов, созданных в процессе резервного копирования.

Кроме того, я использую хранящееся в главной базе данных системное представление sys.databases, которое позволяет идентифицировать режим восстановления базы данных (обеспечивает ли этот режим регистрацию транзакций, дающую возможность восстановления базы на определенный момент?). Наряду с этим мне нужно использовать соединение LEFT JOIN между представлением sys.databases и представлениями предыстории резервного копирования, чтобы идентифицировать базы данных, существующие в соответствующем экземпляре SQL Server, но не снабженные списками последних мероприятий по резервному копированиию, то есть базы данных с риском потери содержащихся в них сведений.

В приведенном листинге представлен запрос, составленный мною с целью получения ответов на четыре сформулированных выше вопроса.

Во главе запроса размещается обобщенное табличное выражение. Оно, в сущности, формирует виртуализованное результирующее множество, которое может быть повторно использовано на протяжении оставшейся части сценария. Это выражение включает в себя всю информацию, касающуюся имени базы данных и режима восстановления из представления sys.databases, и объединяет эти результаты с данными dbo.backupset, чтобы представить тип резервной копии и дату ее формирования с помощью имеющихся метаданных по резервной копии, хранящихся в msdb. Для упорядочения результатов по датам формирования резервных копий от последних до самых первых и переустановления этого значения для каждого сочетания имени базы данных и типа резервной копии я использую оконную функцию ROW_NUMBER. Кроме того, я хочу возвратить значение backup_set_id, поскольку оно понадобится мне для присоединения к msdb.dbo.backup_media_family на более поздних этапах выполнения сценария.

Отмечу, что в факультативных предложениях WHERE данного обобщенного табличного выражения я закомментировал два значения шаблонных параметров. Если интерес для вас представляют всего несколько баз данных или только определенные типы резервных копий, вы можете удалить символы комментариев и использовать так или свести их в одно предложение WHERE, чтобы осуществить фильтрацию результатов. Если вы с достаточным вниманием относитесь к обслуживанию данных msdb, эти меры обеспечат для вас значительную экономию ресурсов при выполнении заданий. Кроме того, должен сказать, что я убежденный сторонник возвращения лишь той информации, которая понадобится мне в дальнейшем; это касается не только конечных результатов, но и промежуточных этапов выполнения запросов.

Теперь, располагая репозиторием информации о резервных копиях (и, возможно, об отсутствии таковых), упорядоченной по принципу «более поздние — более ранние копии» для каждой базы данных, а также по типу резервных копий, мы можем задействовать оставшуюся часть нашего запроса для того, чтобы с помощью фильтров определить последнюю по времени резервную копию для каждой базы данных и для каждого типа резервной копии (данные, разностная копия, журнал регистрации транзакций) и использовать некоторые функции для расчета «возраста» резервных копий, выраженного в часах и минутах.

Полученные результаты я экспортировал в Excel. В этой программе их можно отформатировать и подсветить объекты, за которыми, возможно, нужно будет понаблюдать (см. экран).

 

Результаты запроса в Excel
Экран. Результаты запроса в Excel 

Конечный результат является набором записей, представляющим каждую базу данных и ее последнюю резервную копию — копию данных, разностную копию и копию журнала регистрации из экземпляра преды­стории, содержащегося в данный момент внутри базы данных msdb. Помните четыре вопроса, которые я сформулировал в начале статьи?

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

Ответы на второй и третий вопросы зависят от стандартов, принятых в вашей компании. Если у вас нет таких стандартов или вы не знаете, в чем они состоят, я рекомендую установить их после прочтения данной статьи и определить, соответствует ли этим стандартам ваша стратегия резервного копирования.

Я не использовал фильтры для отделения содержимого tempdb от прочих результатов, ибо хотел показать, что данный запрос возвращает результаты для всех баз данных, не имеющих преды­стории резервного копирования. Если у вас есть база данных, не имеющая ни резервных копий (конкретно отвечающих на второй вопрос), ни предыстории резервного копирования, сведения по этой базе данных будут тем не менее отражены в наборе результатов по данному запросу. Отметим, что резервировать содержимое базы tempdb необходимости нет, так как оно воссоздается всякий раз при запуске и перезапуске служб SQL Server.

Наконец обратимся к четвертому вопросу: придерживаетесь ли вы корректной процедуры резервного копирования — корректной с точки зрения обеспечения режима восстановления соответствующей базы данных? В таблице я выделил желтым цветом пример, который показывает, в чем именно процедура некорректна. База данных Registration переведена в режим полного восстановления (Full recovery), что означает следующее. Все транзакции, предусматривающие удаление, обновление или вставку данных, присоединяются к концу журнала регистрации транзакций. Если вы установите настройку автоматического приращения журнала, этот процесс конкатенации будет продолжаться даже в ситуации переполнения журнала регистрации транзакций. Объем файла будет продолжать увеличиваться до тех пор, пока не будет занято все пространство накопителя, где он размещается, или пока настройка автоматического приращения не преодолеет порог наличного пространства в этом накопителе. Речь идет о ситуациях, когда вы не формируете резервные копии журналов регистрации транзакций для всех баз данных, переведенных в режим полного восстановления. Резервная копия журнала регистрации транзакций — единственный процесс, обеспечивающий контроль данного журнала: в ходе этого процесса отдельные фрагменты журнала выделяются для повторного использования после того, как с журнала снята копия. Как только процесс регистрации доходит до конца физического журнального файла, начинается повторное использование последнего: точка ввода данных переходит обратно к началу того фрагмента журнала регистрации, который выделен для повторного использования. Как вы можете убедиться в ходе анализа представленных выше результатов, мы формируем резервные копии данных и разностные резервные копии содержимого баз данных, но не резервные копии журнала регистрации транзакций.

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

Листинг. Запрос для определения параметров резервных копий
WITH full_backups AS
(
SELECT
        ROW_NUMBER()
                OVER
                        (
                                PARTITION BY BS.database_name,
                                        BS.type
                                ORDER BY BS.database_name ASC,
                                        BS.backup_finish_date DESC
                        ) AS [Row Number],
        D.name AS [database_name],
        BS.backup_set_id,
        BS.type AS backup_type,
        BS.backup_finish_date,
        D.recovery_model_desc
FROM master.sys.databases AS D
        LEFT JOIN msdb.dbo.[backupset] AS BS
                ON D.name = BS.database_name
/* FILTERING OPTIONS*/
--WHERE BS.[type] = ''
--WHERE BS.[name] = ''


)SELECT
        FB.database_name,
        CASE FB.backup_type
                WHEN 'D' THEN 'Data'
                WHEN 'I' THEN 'Differential'
                WHEN 'L' THEN 'Transaction Log'
        END AS backup_type_desc,
        FB.recovery_model_desc,
        FB.backup_finish_date,
        BMF.physical_device_name,
        DATEDIFF(hour,FB.backup_finish_date, GETDATE()) AS backup_hours,
        DATEDIFF(minute,FB.backup_finish_date, GETDATE()) AS backup_minutes
FROM full_backups FB
LEFT JOIN msdb.dbo.[backupset] BS ON FB.backup_set_id = BS.backup_set_id
LEFT JOIN msdb.dbo.backupmediafamily BMF ON BS.media_set_id = BMF.media_set_id
WHERE FB.[Row Number] = 1
ORDER BY FB.database_name, FB.[Row Number], FB.backup_type