В ходе проведения презентаций о бэкапах и восстановлении баз данных SQL Server, обычно задаются два типа вопросов. Первые задаются прямо по ходу презентации из зала, вторые задаются уже после, в приватной беседе. Эти, «приватные» вопросы, зачастую более интересны и я попробую дать ответы на наиболее сложные и интересные из них, вместо того чтобы писать ещё одну статью о том как вы должны делать бэкапы, или почему вы должны делать бэкапы, или даже почему вы должны проверять свои бэкапы (но вы и вправду должны проверять свои бэкапы).
Могу ли я развернуть бэкап на версии SQL Server, отличной от той, на которой был сделан бэкап? Какие проблемы могут возникнуть?
Вы можете восстановить бэкап на другой версии SQL Server, но только в том случае, если версия SQL Server, на которой вы разворачиваете бэкап, более новая чем та, на которой вы его сделали. Другими словами, вы можете развернуть бэкап, сделанный SQL Server 2000 на SQL Server 2005, SQL Server 2005 на SQL Server 2008 R2 или с SQL Server 2008 на SQL Server 2012, но никогда не сможете сделать этого в обратном направлении. Каждая версия SQL Server вносит свои изменения в базу данных и файлы, хранящие её. Компания Microsoft не будет «возвращаться в прошлое» и переписывать предыдущие версии SQL Server для поддержки этих изменений. Если же вам действительно нужно перейти на более старую версию SQL Server, вам нужно будет заскриптовать схему и сами данные (например, вот статья, посвящённая подобному переходу)
Для того, чтобы определить на какой версии SQL Server был создан бэкап, нужно посмотреть заголовок файла бэкапа:
RESTORE HEADERONLY FROM DISK = 'd:bumm.bak';
В результате вы увидите Major, Minor и Build-версии того экземпляра SQL Server, на котором был сделан бэкап (как показано на скриншоте снизу). Это позволит вам определить подходящую версию SQL Server для восстановления этого бэкапа.
При восстановлении БД на более новую версию SQL Server, может оказаться, что в ней присутствует что-то несовместимое с этой версией SQL Server. Наиболее безопасным подходом к переходу на новую версию SQL Server будет запуск Microsoft Upgrade Advisor (бесплатная утилита доступная для каждой версии SQL Server) на базе, которую требуется переносить, убедиться, что она готова, а затем сделать бэкап и восстановить её на новом экземпляре (но только в этом порядке, а не сначала попытаться перенести бэкап, а затем запустить помощника).
После восстановления, БД будет находиться в режиме совместимости с той версией SQL Server’а, с которой осуществлялся переход. Это означает, что ей будет доступен только тот функционал, который поддерживался версией SQL Server, на которой создавался бэкап. Для того, чтобы получить все преимущества новой версии SQL Server, нужно изменить уровень совместимости базы данных. Это можно сделать с помощью GUI, а можно скриптом:
ALTER DATABASE MyDB SET COMPATIBILITY_LEVEL = 110;
Различные числа обозначают различные версии SQL Server: 90 для SQL Server 2005, 100 для SQL Server 2008 и 2008 R2 и 110 для SQL Server 2012 (более подробно о версиях SQL Server можно прочитать здесь — прим переводчика).
Стоит добавить, что не все «переходы» возможны. SQL Server позволят «прыгнуть вперёд» только на две версии. Например, вы не можете развернуть бэкап, сделанный SQL Server 2000, на SQL Server 20012. Сначала вам нужно будет развернуть его на SQL Server 2008, установить соответствующий уровень совместимости, создать новый бэкап, а его, затем, развернуть на SQL Server 2012.
Могу ли я использовать операцию восстановления для создания копии базы даных? Что может пойти не так?
Да, вы можете это сделать. Если вы разворачиваете бэкап на другом сервере, то нужно убедиться в том, что на новом сервере у вас присутствуют те же самые логические диски, что и на «старом» сервере, либо вручную прописать правильные пути для файлов базы данных, используя опцию WITH MOVE команды RESTORE DATABASE:
RESTORE DATABASE NewDBName
FROM DISK = 'c:bumm.bak'
WITH MOVE 'OldDB' TO 'c:datanew_mm.mdf',
MOVE 'OldDB_Log' TO 'c:datanew_mm_log.ldf';
У файлов баз данных есть как логические имена, так и физические имена файлов. Вам нужно всего лишь прописать все логические имена файлов и определить для каждого из них новое физическое размещение.
Основными проблемами, с которыми вы можете столкнуться, являются ошибки связанные с нехваткой свободного места на дисках, на которые вы восстанавливаете базу данных, либо вы можете забыть указать новое имя для базы данных и SQL Server будет пытаться восстановить базу данных поверх существующей БД.
Когда вы восстанавливаете БД на новом сервере, вы можете столкнуться с проблемой «Orphaned Users» (пользователей, утративших связь с учётной записью, согласно переводу на msdn – прим. переводчика), если пользователь базы данных связан с учётной записью, не представленной на новом сервере. Вам нужно будет исправить эту ошибку.
Можно ли присоединять как базу данных файл MDF, если у меня нет файла журнала транзакций?
Единственный вариант, когда это допустимо – если журнал транзакций был утерян уже после того как работа базы данных была корректно завершена. В любом случае – это не очень хорошая идея. При присоединении БД, файл журнала транзакций, так же как и файл данных, нужен для проведения процесса восстановления БД (здесь под восстановлением БД понимается не операция RESTORE DATABASE, а recovery – процесс, происходящий при каждом запуске SQL Server, при котором SQL Server «проходит» по журналу транзакций и приводит файлы данных в согласованное состояние – прим. переводчика). Тем не менее, в некоторых случаях возможно присоединение файла данных без файла журнала транзакций, но эта возможность предназначена только для тех случаев, когда файл журнала транзакций был повреждён или потерян в результате проблем с оборудованием и при отсутствии резервных копий. Конечно, база данных не может существовать без журнала транзакций, и при присоединении БД без файла журнала транзакций, SQL Server просто пересоздаст его.
Присоединение файла данных без файла журнала транзакций разрушает цепочку журналов и, в добавок, может оказаться, что в БД нарушена транзакционная или структурная целостность (в зависимости от состояния БД на момент «потери» журнала транзакций). Операция присоединения такой БД может завершаться ошибкой вне зависимости от того, какие бы действия не предпринимались.
Копирование файлов данных и файлов журнала транзакций допустимо только после выполнения операции отсоединения (detach), либо после того как процесс SQL Server был корректно завершён – это обеспечит корректное завершение всех транзакций. Копирование/перемещение файлов баз данных на другой сервер является более быстрым способом переноса БД на другой сервер, чем создание/разворачивание резервной копии, но не так безопасно (в том случае, если вы перемещаете непосредственно файлы БД, не имея копий). Так же, нужно помнить о том, что вы можете выполнить присоединение БД только на такой же или более новой версии SQL Server.
Моя БД лежит на SAN. Я слышал, что бэкапов SAN достаточно. Это правда?
Это может быть правдой. Главное чтобы ваша SAN (СХД, Система Хранения Данных – прим. переводчика) поддерживала транзакции SQL Server. Если это так, тогда она будет знать о том, что в БД существуют транзакции и наличие этих транзакций может означать, что данные в файлах данных, могут быть не полными, поскольку процесс записи данных, изменённых в этих транзакциях, на жёсткий диск, может быть не завершён на момент создания резервной копии. Те бэкапы, которые делает сам SQL Server, естественно, учитывают эти моменты.
EMC Data Domain, например – это комбинация ПО и SAN, обеспечивающая поддержку транзакций, как и продукция других вендоров, но вам всё равно нужно проверить документацию вашего SAN. Обратите внимание на наличие фраз вроде «transaction consistency», или «transaction aware», или чего-то подобного. Если вы их не нашли, то я бы посоветовал вам проверить восстановление БД прежде чем вы решите, что бэкапов SAN вам достаточно для выполнения всех ваших требований к резервным копиям. Впрочем, даже после того, как вы убедились, что бэкапы SAN выполняются корректно, это вовсе не означает, что «родные» бэкапы SQL Server вам больше не нужны. Если вам нужна возможность восстановления вашей БД на момент времени, например, вам всё равно придётся делать бэкапы журнала транзакций средствами SQL Server.
Обычно, при создании бэкапа, SAN с поддержкой SQL Server, использует VDI-интерфейс SQL Server и «замораживает» БД на время создания резервной копии. Если вы запустите механизм создания такого бэкапа и посмотрите в журнал ошибок SQL Server, там вы увидите сообщения о том, что операции IO были заморожены.
Если вы полагаетесь на резервные копии создаваемые SAN, вам всё равно нужно проводить проверки целостности БД либо на «живых» БД, либо на копиях, восстановленных с бэкапа SAN. В противном случае, вы можете долгое время создавать бэкапы повреждённой БД и даже не знать об этом.
Почему я не могу использовать в качестве бэкапов копии файлов данных, созданных Windows? Мне не нужна возможность восстановления на произвольный момент времени.
SQL Server не является обычным десктопным приложением. Он управляет своими файлами таким образом, чтобы обеспечить выполнение всех свойств ACID (Atomic, Consistency, Isolated, Durable – чуть более подробно — прим. переводчика). Вкратце, чтобы обеспечить успешное завершение транзакций, SQL Server старается никому не давать доступ к своим файлам и сам модифицирует их так, как ему нужно.
Если вы просто скопируете файл данных, игнорируя блокировки и транзакции, которые могут выполняться в данный момент, это означает, что когда вы попробуете присоединить этот файл позже, он будет в несогласованном состоянии, что приведёт к ошибкам.
Только в том случае, когда база данных совершенно не изменяется, вы сможете скопировать файл и присоединить его позже. Если же существует хотя бы минимальная вероятность того, что в момент копирования файла была открыта хотя бы одна транзакция, скорее всего, вы получите неудачную резервную копию. Единственный безопасный способ копирования файлов данных и журналов транзакций для использования их в качестве бэкапов – это перевод БД в режим offline перед копированием.
Намного безопаснее и проще использовать встроенный механизм SQL Server
для создания бэкапов. Такой бэкап будет являться полной копией вашей БД, и все свойства ACID будут выполнены.
У меня очень маленькая БД. Почему я не могу просто «выгрузить» каждую таблицу на диск для создания резервной копии?
Вы можете использовать что-нибудь вроде SQLCMD и выгрузить таблицы в простой текстовый файл, но потом, вместо того, чтобы просто одной командой восстановить БД, вам придётся выполнить целый ряд команд. Во-первых, вам нужно будет создать пустую БД. Затем, вам нужно будет создать и загрузить из файла каждую таблицу. Если какая-нибудь таблица содержит столбец IDENTITY, вам нужно будет выполнять SET IDENTITY_INSERT на каждой из этих таблиц. Так же, вам придётся тщательно определять порядок, в котором вы будете загружать данные в таблицы, чтобы обеспечивать целостность.
Плюс, учитывайте, что все ваши таблицы выгружены на диск в разное время, так что если данные как-то изменялись во время выгрузки, после восстановления вы не получите БД в целостном состоянии и вам придётся вручную искать ошибки и исправлять их.
Конечно, вы вправе поступать таким образом. С другой стороны, вы можете просто выполнить команду BACKUP DATABASE, а потом, когда понадобится, восстановить этот бэкап.
Зачем платить деньги за утилиты, делающие бэкапы, если SQL Server сам умеет это делать?
Существует три основные причины для использования сторонних программ, создающих бэкапы: руководство, автоматизация и функциональность. Если вы начинающий администратор баз данных или вообще не администратор баз данных, но вынуждены обслуживать СУБД как дополнение к своей основной работе, вы можете и не знать о том как, где и почему нужно настраивать бэкапы в SQL Server. Хорошая утилита (вроде SQL Backup Pro) может предоставить вам как раз такой тип руководства, который вам нужен для того, чтобы обеспечить сохранность ваших БД с помощью резервных копий.
Бэкапы, создаваемые самим SQL Server, работают отлично, но вам нужно проделать немало работы для того, чтобы их настроить и ещё больше для того, чтобы их автоматизировать. Хорошая сторонняя утилита сделает процесс автоматизации очень простым. Более того, с её помощью вы сможете автоматизировать другие процессы связанные с бэкапами, такие как зеркалирование/доставка журналов и проверка целостности бэкапа.
Наконец, хотя бэкапы SQL Server и делают то что вам надо, они, возможно, делают это не самым лучшим образом. Например, некоторые утилиты более эффективно сжимают резервные копии, сохраняя таким образом больше дискового пространства и сокращая время создания резервной копии. Так же, они добавляют функциональность – такую как шифрование файла резервной копии (что-то подобное возможно встроенными средствами SQL Server только в том случае, если сама БД зашифрована).
Если бэкап лежит на сетевой шаре, может ли кто-то прочитать его?
До тех пор пока вы не зашифровали непосредственно сам файл резервной копии – да – это самый обычный файл. Если кто-то получит доступ к этой шаре, то он сможет его прочитать любым текстовым редактором, либо просто скопировать и запустить восстановление из него на другом экземпляре SQL Server.
Более того, из бэкапа можно достать схему БД или данные, даже не восстанавливая его. Если у вас есть утилита SQL Data Compare, то она, запущенная с ключом /Export сможет вытащить все данные из бэкапа в CSV-формате, сравнивая этот бэкап с пустой БД и не спрашивая никакого пароля. Так же, та же самая SQL Data Compare сможет создать для вас скрипт создающий схему БД.
Для того чтобы предотвратить несанкционированный доступ к бэкапу, вам придётся сделать несколько вещей. Во-первых, убедиться, что шара, на которой хранятся бэкапы, доступна ограниченному кругу лиц. Во-вторых, вы должны хранить только те бэкапы, которые вам действительно нужны. Наконец, если вы используете сторонние утилиты для создания резервных копий (типа SQL Backup Pro), вы можете зашифровать бэкап, так что если кто-то и получит доступ непосредственно к файлу, то прочитать оттуда ничего не сможет.
Без сторонних утилит, вы сможете этого добиться, используя Transparent Data Encryption (TDE).
Для обеспечения наилучшего уровня безопасности, вам нужно выполнять все вышеперечисленные действия.
А кто-нибудь может изменить содержимое резервной копии?
Возможности изменять содержимое файла резервной копии не предусмотрено. Поскольку бэкап это постраничная копия базы данных (в том виде в котором она существовала на момент создания бэкапа), восстановленная копия этой БД будет находиться в абсолютно том же состоянии, в котором оригинал был на момент создания бэкапа.
Когда SQL Server считывает каждую страницу, в ходе восстановления БД, он высчитывает её контрольную сумму, зависящую от её содержания, и сравнивает с тем значением, которое было прочитано с оригинальной страницы в момент создания бэкапа (подразумевается, что вы использовали параметр WITH CHECKSUM при создании резервной копии). Если кто-либо производил изменения в файле резервной копии, эти значения не совпадут и SQL Server отметит такую страницу как повреждённую.
Существует ли какой-либо флаг, установив который при создании бэкапа, я могу быть уверен, что всегда смогу из него восстановиться?
Если под таким флагом вы подразумеваете, что ваш процесс создания резервной копии включает в себя выполнение операции RESTORE VERIFYONLY после создания бэкапа, то нет, вы не можете быть уверены в том, что сможете восстановить БД из этого бэкапа. RESTORE VERIFYONLY может выполнять набор из двух проверок.
Во-первых, она проверяет заголовок бэкапа, чтобы убедиться, что в нём нет ошибок. Если заголовок повреждён, то вы не сможете восстановить БД из этого бэкапа.
RESTORE VERIFYONLY
FROM DISK= '<Backup_location>'
Вторая проверка возможно только в том случае, если вы запускали процедуру создания резервной копии с параметром WITH CHECKSUM. Это означает, что в ходе создания резервной копии, SQL Server пересчитывает и сверяет контрольные суммы для всех прочитанных страниц. Если он наткнётся на страницу, для которой эти суммы не сойдутся, операция создания резервной копии завершится с ошибкой. Если проверка завершается успешно, BACKUP WITH CHECKSUM вычислит и запишет контрольную сумму созданной копии.
Соответственно, RESTORE VERIFYONLY может использоваться для пересчёта контрольной суммы и проверки того, что за время хранения резервная копия не была повреждена
RESTORE VERIFYONLY
FROM DISK= '<Backup_location>'
WITH CHECKSUM
Проблемы могут возникнуть в двух местах. Во первых, проверка заголовка в ходе выполнения VERIFYONLY не проверяет всё что может повлиять на процесс восстановления. Это означает, что RESTORE VERIFYONLY может завершиться без ошибок, но БД всё равно не сможет быть восстановлена из «проверенной» копии.
Во-вторых, CHECKSUM не может обнаружить повреждения, произошедшие в памяти. Если страница данных была обновлена, находясь в памяти и затем произошло её повреждение прежде чем она была записана на диск (и, соответственно, в бэкап), тогда вычисление контрольной суммы не покажет никакой ошибки, а просто подтвердит, что в бэкап была записана та же страница, что и содержалась в БД в момент создания бэкапа. Т.е. если страница уже была повреждена в момент создания бэкапа, ошибка не может быть найдена с помощью контрольной суммы и восстановление из этого бэкапа может завершиться ошибкой.
Единственный способ узнать наверняка, что из бэкапа можно восстановиться и полученная БД не повреждена – это восстановить его и, желательно, запустить проверку целостности БД на восстановленной копии.
Не содержит ли бэкап что-нибудь кроме данных? Может ли кто-нибудь прочесть пароли из него?
Бэкап содержит не только данные. Он содержит всю структуру базы данных. Она включается в себя все данные, процедуры, представления, функции и весь остальной код. Также, он содержит все настройки БД. Наконец, он содержит всю информацию о пользователях БД. Для обычной БД, каждый пользователь БД связан с учётной записью SQL Server. Пароли таких пользователей хранятся вместе с учётной записью, так что этих паролей в бэкапе не будет.
Однако, в автономных базах данных (contained databases — прим. переводчика) существует понятие USER WITH PASSWORD, поскольку сама идея автономных баз данных предполагает минимальную связь такой базы с сервером. В этом случае, пароль будет находиться в бэкапе, что может привести к попыткам достать его оттуда. Пароли хранятся не открытым текстом, они хэшируются, точно так же как пароли учётных записей (которые хранятся в системной базе данных master и, естественно, попадают в её бэкап).
Microsoft предлагает несколько best practices по безопасности автономных баз данных.
Зачем в бэкапе индексы, статистика и остальные штуки, которые легко пересоздать? Это же просто потеря времени?
А по-моему, потеря времени – это попытки разделить вещи таким образом и делать резервную копию только одной части. Во-первых, как это сделать? Например, как забэкапить данные, не делая, при этом, бэкапа кластерных индексов? Это невозможно, поскольку листовой уровень кластерного индекса – это страницы данных. Т.е., можно сказать, что кластерные индексы – это сами таблицы, поэтому кластерные индексы должны быть включены в бэкап. Конечно, возможно выделить некластерные индексы в отдельную файловую группу и не делать её бэкап, но потом, после восстановления того бэкапа, что у нас есть, нам всё равно нужно будет возвращать эту файловую группу к жизни и перестраивать все индексы. Так чего мы добьёмся?
Со статистикой так же возникнут проблемы. SQL Server бэкапит статистику вместе с базой данных (и она занимает очень мало места, поскольку, гистограмма, называющаяся статистикой, строится всего лишь по 200 строкам) и восстанавливает её вместе с БД. Однако, если после восстановления мы начнём пересоздавать индексы, поскольку не делали их резервной копии, нам придётся пересоздавать и статистику. Это так же потребует дополнительного времени, а база данных, тем временем, будет оставаться недоступной.
В конце концов, я бы поспорил с формулировкой «легко пересоздать», поскольку в экстренном случае, весь этот процесс может оказаться очень запутанным, что неизбежно приведёт к тому, что люди, работающие с этой базой данных, не смогут получить к ней доступ намного большее время, чем в случае простого восстановления из бэкапа.
Сама идея создания резервной копии заключается в том, чтобы можно было восстановить базу данных как можно более быстро и эффективно. Чем сложнее процесс восстановления, тем менее эффективна резервная копия. Да, для хранения индексов, пользователей, хранимых процедур и всего прочего, требуется дополнительное пространство, но увеличение скорости восстановления за счёт того, что всё лежит в одном месте, стоит этого дополнительного пространства.
ОМГ! Я только что удалил таблицу! Я знаю, что это есть в журнале транзакций. Как мне её вернуть?
После того как транзакция была зафиксирована, SQL Server не сможет её откатить. Операции DELETE И TRUNCATE удаляют данные совершенно разными способами. Операция DELETE удаляет данные с помощью транзакций, удаляющих каждую строку. Операция TRUNCATE просто отмечает странницы данных, на которых лежали удаляемые данные, как не использующиеся. Но последствия ни одной из этих операций не могут быть устранены вручную при просмотре журнала транзакций. Вместо этого, вам нужно выполнить процесс, называющийся восстановлением на момент времени. Вы должны немедленно сделать бэкап журнала транзакций вашей БД для того, чтобы сохранить все изменения сделанные до того момента, как вы случайно удалили нужные данные из таблицы. Затем, вам нужно выполнить шаги, описанные в главе 6 этой книги для восстановления на момент времени (в MSDN тоже всё есть – прим. переводчика).
Другой вариант – использование сторонних утилит, типа SQL Backup Pro, которые могут выполнять восстановление отдельных объектов БД в режиме online из имеющихся резервных копий.
А если я просто хочу создать с помощью бэкапа скрипт для построения БД, без восстановления непосредственно бэкапа…?
Стандартных средств для создания такого скрипта в SQL Server не предусмотрено. Однако, утилиты, типа SQL Compare, могут сформировать его. Он легко создаётся с помощью GUI, но так же это возможно с использованием PowerShell:
& 'C:Program Files (x86)Red GateSQL Compare 8SQLCompare.exe' /Backup1:C:MyBackupsMyBackupFile.bak /MakeScripts:"C:MyScriptsMyBackupScript"
Так же, вы можете обратить внимание на SQL Virtual Restore. Эта утилита позволяет вам примонтировать бэкап к вашему SQL Server так, как будто бы вы запускали процесс восстановления из этого бэкапа, но не требует использования всего того места, которое было бы необходимым при восстановлени. Примонтированный таким образом бэкап выглядит как самая обычная база данных и вы можете заскриптовать её любым удобным вам образом.
От переводчика:
Грант довольно активно пиарил утилиты своей компании на протяжении всей статьи, я постарался это немного сгладить. Названия остались, а если кому-то будут нужны ссылки – их достаточно легко найти в интернете (ну или спросить в личке у меня).
Как обычно, любые пожелания и исправления по переводу и стилистике приветствуются.
Автор: unfilled
