SQL — Восстановление в базу с другим именем — Для сисадминов и не только

Восстановление базы данных из резервной копии в MS SQL Server 2012

Раннее я уже писал о создании резервных копий в MS SQL Server 2012. В данной статье подробно рассмотрим процессе восстановления базы данных из имеющейся резервной копии (резервных копий) в MS SQL Server 2012 (в более ранних версиях, например в MS SQL Server 2008 набор действий аналогичен).

0. Оглавление

1. Восстановление базы данных

Подключаемся к MS SQL Server c помощью программы  «SQL Server Management Studio
». В Microsoft Windows Server 2012 R2 ее можно найти в списке всех программ.

В Microsoft Windows Server 2008 R2 в меню «Пуск
» (Start) — «Microsoft SQL Server 2012
» — «Среда SQL Server Management Studio
».

Вводим адрес сервера или его псевдоним, данные для авторизации и нажимаем «Соединить
» (Connect).

Слева, в обозревателе объектов (Object Explorer), раскрываем вкладку «Базы данных
» (Server Oblects), находим в списке базу данных из которой (или в которую) необходимо восстановить данные, кликаем по ней правой кнопкой мыши, затем в появившемся контекстном меню выбираем «Задачи
» (Tasks) — «Восстановить
» (Restore) — «База данных…
» (Database…)

Запустится мастер восстановления базы данных (Restore Database). Выбираем базу источник (Source for restore), при этом мастер попробует автоматически подобрать последовательность файлов резервных копий для восстановления базы на текущий момент времени.

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

Затем необходимо выбрать базу данных назначения (Destination for restore), т. е. ту информационную базу в которую будут загружаться данные. Эта может быть как база с которой делалась резервная копия, так и любая другая база данных, зарегистрированная на текущем экземпляре SQL Server.

Нажав кнопку «Временная шкала…
» (Timeline) можно указать время на которое необходимо восстановить данные. При имеющейся копии журнала транзакций время восстановления можно выбрать с точностью до секунды (или имеющегося checkpoint’а в журнале транзакций).

Очень важно (!)
также помнить о том, что если восстановление данных осуществляется в информационную базу отличную от той с которой производилось резервное копирование (т. е. необходимо скопировать базу данных) то на вкладке «Файлы
» (Files) необходимо указать путь к файлам этой информационной базы.

На вкладке «Параметры
» (Options) можно указать дополнительные параметры резервного копирования. В частности:

  • Флаг «Перезаписать существующую базу данных (WITH REPLACE)
    » (Overwrite the existing database) указывает, что операция восстановления перезапишет файлы любой базы данных, в настоящее время использующей имя, указанное в качестве базы данных назначения.
  • Флаг «Сохранить параметры репликации (WITH KEEP_REPLICATION)
    » (Preserve the replication settings) сохраняет настройки репликации при восстановлении опубликованной базы данных на сервере, отличном от сервера, на котором была создана база данных. Этот параметр имеет значение, только если во время создания резервной копии проводилась репликация базы данных.
  • Флаг «Ограничение доступа к восстановленной базе данных (WITH RESTRICTED_USER)
    » (Restrict access to the restored database) ограничит доступ к базе данных, за исключением пользователей с правами db_owner
    , dbcreator
    или sysadmin
    . Данный параметр имеет смысл использовать, например, если необходимо последовательно восстановить базу из нескольких файлов резервных копий, и доступ пользователей необходимо ограничить до завершения всех операций по восстановлению данных.
  • Если оставить флаг «Создание резервной копии заключительного фрагмента журнала перед восстановлением
    » (Take tail-log backup before restore) то будет создана резервная копия заключительного фрагмента журнала транзакций. Если для точки во времени, выбранной в окне «Временная шкала резервного копирования
    » (Backup Timeline) требуется резервная копия заключительного фрагмента журнала, этот флажок будет установлен и снять его будет нельзя.
  • Флаг «Закрыть существующие соединения
    » (Close existing connections option) переводит базу данных в однопользовательский режим перед началом выполнения процедуры восстановления, а затем возвращает в многопользовательский режим после ее завершения.
  • Ну и наконец, флаг «Выдавать приглашение перед восстановлением каждой резервной копии
    » (Prompt before restoring each backup
    ) указывает, что после восстановления каждой резервной копии будет выводиться диалоговое окно с вопросом, нужно ли продолжать последовательность восстановления. Этот параметр позволяет приостанавливать последовательность восстановления после восстановления каждой резервной копии. Он будет полезен, например, когда нужно поменять ленты в устройстве, если на сервере имеется только одно ленточное устройство.

Когда все необходимые параметры установлены нажимаем «ОК
» для запуска процесса восстановления базы данных. После того, как все операции по восстановлению будут завершены увидим соответствующее уведомление.

 2. Просмотр информации о событиях резервного копирования и восстановления для базы данных

Для того чтобы узнать, когда производилось создание резервных копий конкретной базы данных, а также восстановление базы данных из резервной копии, можно воспользоваться стандартным отчетом «События резервного копирования и восстановления
» (Backup and Restore Events). Для формирования данного отчета необходимо в Обозревателе объектов (Server Oblects) кликнуть правой кнопкой мыши по соответствующей базе данных, в контекстном меню выбрать «Отчеты
» (Reports) — «Стандартный отчет
» (Standart Reports) — «События резервного копирования и восстановления
» (Backup and Restore Events).

Сформировавшийся отчет содержит в себе следующие данные:

  • Среднее время, затрачиваемое на операции резервного копирования (Average Time Taken For Backup Operations)
  • Успешные операции резервного копирования (Saccessful Backup Operations)
  • Ошибки операции резервного копирования (Backup Operation Errors)
  • Успешные операции восстановления (Saccessful Restore Operations)

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

Помогла ли Вам данная статья?

PROИТ: SQL Server Как восстановить базу данных из bak файла бэкапа (restore DB from backup)

Задача
: восстановить
базу данных из файла бэкапа
. Шаг за шагом с иллюстрациями рассмотрим как это сделать на примере СУБД Microsoft SQL Server 2014. Кроме восстановления посмотрим также как сделать линковку
(связь) пользователя базы данных и логина СУБД (будет использовать SQL скрипт sp_change_users_login). Итак, залогиньтесь в СУБД SQL Server и в дереве в контекстном меню пункта Databases
выберите пункт «Restore Database…
«:

В параметрах выберем «Device
» и перейдем по кнопке «Обзор
» (кнопка с многоточием):

В открывшемся окне нажимаем «Add
» и находим файл бэкапа базы данных:

Нажимаем ОК:

Перед началом рекомендуется проверить корректность файла, нажав кнопку «Verify Backup Media
«:

Далее перейдем в левом меню на раздел «Files
» и проверим пути для сохранения восстановленной базы данных. При необходимости меняем их (не обязательно):

Также можно перейти в раздел «Options
» и сделать настройки, если требуется (не обязательно):

Нажимаем ОК и дожидаемся сообщения об успешном восстановлении базы:

Если база данных содержала информацию о пользователях, то необходимо проверить их связь с логинами. Для этого в дереве находим вновь воссозданную БД, раскрываем пункт «Security
» — «Users
» и выбираем какого-либо существующего пользователя и в контекстном меню переходим на пункт «Properties
«:

В открывшемся окне свойств пользователя мы видим, что пользователь существует только в контексте это базы (т.е. не связан ни с каким логином «SQL user without login»):

Это не позволит Вам подключиться к базе данных под данным пользователем

. Поэтому создадим логин для этого пользователя (если логин уже существует, пропустите этот шаг). В дереве объектов СУБД перейдем на уровень выше (выше, чем текущая БД) в пункт «Security
» и в контекстном меню пункта «Logins
» выберем «New Login
…»:

В открывшемся окне заполняем: — login name
— имя логина (я всегда делаю его одноименным с пользователем базы) — выбираем параметр «SQL Server authentication
» и задаем пароль — снимаем «галку» «Enforce password policy
» — default database
— прописываем нужную базу данных:

Далее можно перейти в левом меню в раздел «User Mapping
«, где увидим, что можно закрепить за созданным логином некого пользователя в базе. Если у Вас база данных не содержит никаких пользователей, то задайте эти параметры, как показано на рисунке (в противном случае пропустите этот шаг) и будет автоматически создан пользователь БД связанный с данным логином:

Если же Вы задали вышеуказанный параметр, а пользователь уже существует, то Вы получите следующее сообщение об ошибке:Microsoft SQL Server Management Studio
Create failed for User ‘ShortURLDBUser’.  (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
User, group, or role ‘ShortURLDBUser’ already exists in the current database. (Microsoft SQL Server, Error: 15023)

При этом логин будет создан, но не связан с пользователем. Чтобы связать логин СУБД с пользователем конкретной базы данных
выполним следующий SQL-скрипт:

use ShortUrlDB go
EXEC
 sp_change_users_login 'Update_One'
, 'ShortUrlDBUser'
, 'ShortUrlDBUser'
go

Теперь, если снова перейти в свойства пользователя БД, то мы видим, что пользователь связан с только что созданным логином.

(с) Ella S.

Восстановление отдельных страниц в базе данных / Habr

Предисловие

Статья Gail Shaw «Help, my database is corrupt. Now what?», перевод которой я запостил на прошлой неделе, вызвала, вроде бы, определенный интерес, но она, увы, не содержала «практики». Да, там написано как можно спасти данные, но нет никаких примеров. Изначально я хотел сделать еще один перевод все того же автора, но, подумав, решил написать пост «от себя», как бы «по мотивам». Причины, побудившие меня поступить так, я опишу в конце поста, в примечаниях.

Восстановление баз данных в SQL Server

Как уже было сказано в предыдущей статье, в том случае, если повреждены страницы кластерного индекса или кучи, то данные, содержащиеся на этих страницах, потеряны и единственным вариантом для их восстановления является непосредственно восстановление базы данных. SQL Server предоставляет множество возможностей для восстановления баз данных. Во-первых, это восстановление базы данных целиком — оно может занимать довольно много времени (зависит от размера БД и скорости жестких дисков). Во-вторых, восстановление отдельных файловых групп, либо файлов, если ваша БД состоит из нескольких файловых групп (или, соответствено, файлов). В этом случае, есть возможность восстановления только поврежденных частей БД, не затрагивая остальных. Эти два вида восстановления БД используются довольно часто и затрагиваться в дальнейшем не будут. В-третьих, в SQL Server 2005 появилась возможность восстановления отдельных страниц БД — в этом случае из бэкапа будут восстановлены только указанные страницы. Такое восстановление будет особенно актуально, если DBCC CHECKDB найдет несколько поврежденных страниц в какой-нибудь огромной таблице, «лежащей» в здоровенном файле. За счет того, что восстанавливаться будет не весь файл, и даже не вся таблица, а только несколько страниц — время простоя может быть значительно сокращено.

Требования и ограничения

Модель восстановления и доступность резервных копий журнала транзакций

Самое главное, что нужно помнить — для восстановления отдельных страниц, база данных должна использовать полную (full) модель восстановления, либо модель восстановления с неполным протоколированием (bulk-logged). Если ваши базы находятся в простой (simple) модели восстановления — дальше вы можете уже и не читать. Второе требование — ваши полные бэкапы и бэкапы журнала транзакций должны образовывать неразрывную цепочку журналов (log chain). Если вы никогда не выполняете команду BACKUP LOG WITH TRUNCATE_ONLY (NO_LOG) и не переключаетесь в простую модель восстановления для того, чтобы уменьшить журнал транзакций, и у вас есть ВСЕ резервные копии журнала транзакций с момента последней полной резервной копии не содержащей поврежденных страниц (включая эту самую полную резервную копию) — за цепочку журналов можно не волноваться. В модели восстановления с неполным протоколированием, теоретически, восстановление отдельных страниц должно работать нормально в том случае, если соблюдаются условия описанные выше, и восстанавливаемые страницы не изменялись операциями, выполняемыми с минимальным протоколированием.

Редакции SQL Server

Восстановление страниц возможно в любой редакции SQL Server, но для редакций Enterprise Edition и Developer Edition возможно восстановление поврежденных страниц on-line, т.е. к базе данных, во время восстановления, можно обращаться (и более того, обращаться можно даже к той таблице, к которой относятся восстанавливаемые в данный момент страницы, если сами эти страницы не будут «затрагиваться» — в противном случае, запрос завершится ошибкой). Для редакций «ниже» Enterprise Edition, восстановление страниц проходит в режиме off-line и база данных, на время восстановления, становится недоступной.

Тип поврежденной страницы

В том случае если повреждены страницы индекса, либо данных, их восстановление возможно в режиме online в редакции Enterprise Edition. Страницы, приндалежащие критически важным системным таблицам могут быть восстановлены, но база данных, при восстановлении, будет недоступна в любой редакции SQL Server. «Карты размещения» не могут быть восстановлены «отдельно». Если повреждены GAM, SGAM, PFS, ML, DIFF-страницы, необходимо восстанавливать базу данных целиком. Единственным исключением являются IAM-страницы. Хотя они и относятся к «картам размещения», но они описывают только одну таблицу, а не всю базу данных, и их восстановление возможно. Загрузочная страница базы данных (9-я страница в 1-м файле БД) и страница заголовка файла (0-я страница в каждом файле) не могут быть восстановлены «отдельно», при их повреждении придется восстанавливать БД целиком.

Собственно, восстановление

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

Портим БД

Для экспериментов я буду использовать базу данных AdventureWorks, которая поставляется вместе с SQL Server. Если вы не устанавливали ее, при желании, можно скачать здесь. Перевожу ее в модель восстановления full:

ALTER DATABASE AdventureWorks SET RECOVERY FULL

убеждаюсь, что ошибок в ней еще нет:

DBCC CHECKDB('AdventureWorks') WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY

и создаю полный бэкап:

BACKUP DATABASE AdventureWorks   TO DISK = 'D:tmpaw_backupsaw_full_ok1.bak'

В этой базе данных я создаю таблицу crash.

CREATE TABLE crash (txt varchar(1000))

Поле типа varchar мы и будем портить, для того, чтобы проверить что произойдет, если вдруг SQL Server обнаружит в нем не те данные, которые он сам туда записал. Прежде чем что-то испортить, надо это чем-то заполнить. Я забиваю в созданную таблицу левые данные.

SET NOCOUNT ON  DECLARE @i INT  SET @i = 1  WHILE @i BEGIN  INSERT INTO crash  SELECT REPLICATE('a', 1000)  SET @i = @i + 1  END  SET NOCOUNT OFF  

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

BACKUP LOG AdventureWorks   TO DISK = 'D:tmpaw_backupsaw_log_ok1.trn'

Теперь немного изменим данные: Итак, все готово. Отсоединяем БД и открываем mdf-файл FAR’ом (или чем вам удобнее), ищем в нем строку «zzzzzzz» и заменяем несколько ‘z’ на произвольные символы: Теперь, когда БД испорчена, подсоединяем ее. И, да, я помню, что в предыдущей статье было четко сказано, что отсоединять/присоединять БД не стоит. Но в нашем случае это абсолютно «безопасно» — база данных в «suspect» не упадет.

Ищем ошибки

Итак, испорченная БД успешно вернулась в строй. Снова запустим проверку целостности:

DBCC CHECKDB('AdventureWorks') WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY

В результате то, чего мы ждали (обязательно запоминайте номера поврежденных страниц!
):Msg 8928, Level 16, State 1, Line 1
Object ID 1883153754, index ID 0, partition ID 72057594054246400, alloc unit ID 72057594061651968 (type In-row data): Page (1:20455) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 1883153754, index ID 0, partition ID 72057594054246400, alloc unit ID 72057594061651968 (type In-row data), page (1:20455). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29493257 and -4.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'crash' (object ID 1883153754).
CHECKDB found 0 allocation errors and 2 consistency errors in database 'AdventureWorks'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (AdventureWorks).

В данном случае повреждены сами данные, находящиеся в куче (index id = 0), поэтому SQL Server эти данные восстановить не сможет. Сейчас у нас есть три варианта:

  1. Смириться с потерей данных и выполнить DBCC CHECKDB(‘AdventureWorks’, REPAIR_ALLOW_DATA_LOSS)
  2. Сделать бэкап активной части журнала транзакций и восстановить БД целиком — в результате потери данных не будет, но это займет продолжительное время
  3. Сделать бэкап активной части журнала транзакций и восстановить только одну(!), поврежденную, страницу

Со вторым вариантом все должно быть понятно, а вот что произойдет если запустить DBCC CHECKDB или как восстанавливаются отдельные страницы — я покажу дальше.

Восстанавливаем поврежденную страницу

В первую очередь нам надо сделать бэкап заключительного фрагмента журнала транзакций (tail backup). При этом, если у вас Enterprise Edition, вы можете не добавлять параметр NORECOVERY, который переведет БД в состояние «restoring», поскольку эта редакция поддерживает on-line восстановление страниц. Более того, если у вас резервные копии журнала транзакций выполняются на регулярной основе, чтобы не нарушать цепочку журналов, в редакции Enterprise Edition, вы можете сделать COPY_ONLY бэкап. Я же иду по пути off-line восстановления и выполняю:

BACKUP LOG AdventureWorks   TO DISK = 'D:tmpaw_backupsaw_log_fail3.trn'  WITH NORECOVERY

Теперь, можно восстанавливать поврежденную страницу. В первую очередь, используем полный бэкап (aw_full_ok1.bak):

RESTORE DATABASE AdventureWorks  PAGE = '1:20455'  FROM DISK = 'D:tmpaw_backupsaw_full_ok1.bak'  WITH NORECOVERY

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

RESTORE LOG AdventureWorks  FROM DISK = 'D:tmpaw_backupsaw_log_ok1.trn'  WITH NORECOVERY

и

RESTORE LOG AdventureWorks  FROM DISK = 'D:tmpaw_backupsaw_log_fail3.trn'  WITH RECOVERY

Вроде бы все прошло успешно, запускаем DBCC CHECKDB и… Восстановление прошло успешно. Обратите внимание, что время простоя сокращается за счет того, что из полного бэкапа мы восстанавливаем не всю БД, а только поврежденные страницы (если бы я восстанавливал бэкап целиком — бэкап восстановился бы за 8,5 секунд, но, чем больше размер БД — тем больше будет разница во времени). Счастливчики с SQL Server Enterprise Edition, использующие on-line восстановление, так же сэкономят время на восстановлении из бэкапов лога, а при off-line восстановлении, увы, журналы будут обрабатываться целиком. Стоит так же добавить, что в SQL Server 2005, 2008, 2008 R2 восстановление отдельной страницы возможно только с помощью T-SQL, в Denali появилась возможность делать это через GUI.

А если все-таки DBCC CHECKDB?

На всякий случай я решил проверить что сделает SQL Server, если я запущу DBCC CHECKDB с параметром REPAIR_ALLOW_DATA_LOSS. Все та же ошибка: Сначала переводим БД в режим SINGLE_USER:

ALTER DATABASE AdventureWorks SET SINGLE_USER

А затем, запускаем восстановление:

DBCC CHECKDB('AdventureWorks', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY

В итоге:Repair: The page (1:20455) has been deallocated from object ID 1883153754, index ID 0, partition ID 72057594054246400, alloc unit ID 72057594061651968 (type In-row data).

Ага, SQL Server удалил «испорченную» страницу. Переводим БД в режим MULTI_USER, чтобы она стала доступной для всех и проверяем что пропало: Учитывая, что размер страницы в SQL Server 8КБ, а для пользовательских данных доступно чуть меньше — то все закономерно, таблица «похудела» на 7 записей (в начале их было 99999). Поскольку на этой таблице не было кластерного индекса, данные могли храниться в произвольном порядке, т.е. мы даже не могли узнать какие данные будут потеряны.

Так почему, все-таки, не перевод?

Итак, почему это все-таки не перевод, а пост «по мотивам». Дело в том, что, в открытом доступе статьи «Page Restore» за авторством Gail Shaw нет. Есть такой раздел в книге SQL Server MVP Deep Dives vol.2, которая продается за довольно-таки ощутимые деньги (но, естественно, легко находится в интернетах) и я не уверен, что публиковать перевод — это эм… правильно что ли. В общем, я прочитал статью, взял на заметку основные моменты, а потом уже сам писал текст и, попутно, проводил эксперимент по восстановлению. Надеюсь, кому-нибудь этот опыт был полезен. И, господа, я искренне надеюсь, что если вы решите повторять этот эксперимент, то будете предельно осторожны (например, не будете эксперементировать с основной БД на production-сервере). Помните, что никакой ответственности за ваши действия я не несу.

128

20k


128

Ссылка на основную публикацию
Похожие публикации