Ms sql restore database

Ms sql restore database

Приветствую, в данной заметке будет рассмотрено создание резервных копий и восстановление в MS SQL Server

Создание резервных копий

1 Резервное копирование системных баз данных.

2 Полное резервное копирование базы данных.

3 Разностное резервное копирование базы данных.

4 Резервное копирование журнала транзакций базы данных.

5 Резервное копирование файловых групп базы данных.

Восстановление из резервных копий

6 Восстановление из полной резервной копии.

7 Восстановление из разностной резервной копии.

8 Восстановление журнала транзакций.

9 Восстановление файловых групп.

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

Создадим нашу тестовую базу данных “sbase”, модель восстановления — полная:

Создание резервных копий

1Резервное копирование системных баз данных.

Список системных баз: master, model, msdb, tempdb.

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

Model: Используется в качестве шаблона для создаваемых баз данных. Резервное копирование необходимо при изменении настройки самой базы model.

Msdb: Содержит сведения о заданиях и для агента сервера MS SQL Server. копирование необходимо делать каждый раз при добавлении задания для агента сервера MS SQL Server.

Tempdb: Хранит временные данные например для транзакций. Уничтожается и создается при перезапуске экземпляра MS SQL Server. Резервное копирование делать нет смысла.

Создадим новый запрос:

Выполним следующий запрос:

BACKUP DATABASE master

TO DISK = ‘C:sqlmaster.bak’

BACKUP DATABASE model

TO DISK = ‘C:sqlmodel.bak’

BACKUP DATABASE msdb

TO DISK = ‘C:sqlmsdb.bak’

Как видим, на диск ‘C’ было произведено успешное резервное копирование системных баз данных.

2Полное резервное копирование базы данных.

Включает в себя файлы данных и журнал транзакций. По сути является базой данных на момент создания резервной копии базы данный MS SQL Server.

Включает в себя:

Резервное копирование данных в базе.

Резервное копирование изменений, возникающих во время резервного копирования

Резервное копирование транзакций, не зафиксированных в журнале транзакций.

Способ 1(Графический интерфейс):

Выберем «создать резервную копию»

Указываем куда копировать и модель – полная.

Способ 2(Запрос SQL):

BACKUP DATABASE sbase

TO DISK = ‘C:sqlsbase.bak’

3 Разностное резервное копирование базы данных.

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

Нельзя восстановить без полной резервной копии. После каждого запуска разностного копирования, размер резервной копии возрастает из-за количества транзакций с момента полного резервного копирования.

При создании разностного резервного копирования выполняются следующие действия:

Создание резервных копий баз данных, которые изменились с момента полного резервного копирования.

Создание резервных копий всех операций, выполняющихся во время разностного резервного копирования и всех транзакций не зафиксированных в журнале транзакций.

—Создадим таблицу test

CREATE TABLE test(

INSERT INTO test (id,name)

Далее по аналогии с полным запустим задачу резервного копирования, но модель выберем – разностную:

Проведем полный бэкап, добавим еще данных, проведем разностный бэкап:

—Делаем полный бэкап

BACKUP DATABASE sbase

TO DISK = ‘C:sqlsbase_razh2’

—Добавим еще данные

INSERT INTO test (id,name)

BACKUP DATABASE sbase

TO DISK = ‘C:sqlsbase_razh3’

А вот и результат:

Вывод, не надо доводить разностное копирование до больших объемов, иначе оно теряет свой смысл быстрого восстановления данных.

4 Резервное копирование журнала транзакций базы данных.

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

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

В процессе выполняются следующие действия:

Создается копия журнала транзакций от последнего резервного копирования лога до конца текущего.

Очищаются части журнала транзакций до начала активной части и отбрасываются сведения в неактивной части.

По аналогии с полным и разностным копированием запускаем задачу, но тип выбираем – лог транзакций:

Или с помощью запроса:

BACKUP LOG sbase

TO DISK = ‘C:sqlsbase_tran.bak’

5 Резервное копирование файловых групп базы данных.

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

Читайте также:  Total commander последняя версия

Файлы журналов не входят в состав файловых групп.

Управление пространством журнала отделено от управления пространством данных, возможно только полное и разностное резервное копирование файлов и файловых групп.

Пример полного копирования:

По аналогии с другими видами копирования запускаем мастер:

Тоже, только запросом:

BACKUP DATABASE sbase

TO DISK = ‘C:sqlprimary.bak’

Про восстановление можно почитать на русском языке :

Стадия копирования данных: копирование всех страниц данных, журнала и индекса с резервного носителя в файлы базы данных.

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

Стадия отката: производит откат незафиксированных транзакций и делает базу данных доступной для пользователей. После завершения стадии отката восстановление последующих резервных копий становится невозможным. Затем в процессе восстановления база данных переводится в активный режим.

Режим WITH RECOVERY включает и стадию повтора, и стадию отката и восстанавливает базу данных. Более поздние резервные копии восстановить невозможно. Это значение по умолчанию.Если набор данных наката не был восстановлен в достаточной степени, чтобы обеспечить согласованность с базой данных, стадия отката выполнена быть не может. Компонент Database Engine выдает ошибку и прекращает восстановление. Если весь набор данных наката согласован с базой данных, то выполняется восстановление, после чего базу данных можно перевести в режим в сети.

Предложение WITH NORECOVERY позволяет пропустить стадию отката, чтобы сохранить незафиксированные транзакции. Пропуск стадии отката позволяет восстановить другие резервные копии, чтобы выполнить накат базы данных на более поздний момент времени. Иногда инструкция RESTORE WITH NORECOVERY выполняет накат данных до момента, пока они не будут согласованы с базой данных. В таких случаях компонент Database Engine выдает информационное сообщение, указывающее, что набор данных наката теперь можно восстановить при помощи параметра RECOVERY. Другими словами, параметр NORECOVERY нужно использовать, когда для восстановления базы используются несколько восстанавливаемых резервных копий, за исключением последней восстанавливаемой резервной копии. После применения параметра NORECOVERY, база данных переходит в состояние восстановления.

6 Восстановление из полной резервной копии.

Или с помощью запроса:

RESTORE DATABASE sbase

7 Восстановление из разностной резервной копии.

В начале восстанавливается полная копия(например в прошлом шаге мы это уже сделали), а далее восстановим разностную копию.

Графический интерфейс аналогичен с предыдущим примером за исключением типа выбираемой копии, а запрос будет таков на примере наших разностных копий:

RESTORE DATABASE sbase

FROM DISK = ‘C:sqlsbase_razh2’

WITH FILE = 1, NORECOVERY, REPLACE

RESTORE DATABASE sbase

FROM DISK = ‘C:sqlsbase_razh3’

WITH FILE = 1, RECOVERY

8 Восстановление журнала транзакций.

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

Графический вариант интуитивно понятен, будет продемонстрирован только SQL запрос:

Для того, чтоб все отработало корректно, вернемся разностному бэкапу 2, и после него накатим журнал транзакций:

RESTORE DATABASE sbase

FROM DISK = ‘C:sqlsbase’

WITH FILE = 1, NORECOVERY, REPLACE

RESTORE DATABASE sbase

FROM DISK = ‘C:sqlsbase_razh2’

WITH FILE = 1, NORECOVERY, REPLACE

RESTORE LOG sbase

FROM DISK = ‘C:sql ran.bak’

WITH FILE = 1, NORECOVERY

9 Восстановление файловых групп.

Графический вариант показан не будет, он довольно интуитивен, запрос SQL:

RESTORE DATABASE sbase FILEGROUP = ‘PRIMARY’

FROM DISK = ‘C:sqlprimary.bak’

WITH PARTIAL, RECOVERY, REPLACE

Так как мы восстанавливали только часть базы – файловую группу, то мы использовали параметр «PARTIAL».

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

Если экземпляр SQL сервера доступен, то системные базы восстанавливаются согласно приведенной таблице:

Системная база данных

Запускаем экземпляр сервера в однопользовательском режиме. Восстановление базы осуществляется так же, как полное восстановление пользовательской базы данных. После восстановления следует перезапустить экземпляр SQL сервера.

Восстановление базы осуществляется так же, как полное восстановление пользовательской базы данных.

Восстановление базы осуществляется так же, как полное восстановление пользовательской базы данных.

Читайте также:  Email адрес что это

Запускаем экземпляр сервера в однопользовательском режиме: выключим и включим экземпляр сервера с параметром запуска /m, введя в командной строке Windows (CMD):

net stop MSSQLSERVER

net start MSSQLSERVER /m

Подключимся к серверу и запустим процесс восстановления базы.

sqlcmd

RESTORE DATABASE master FROM DISK = ‘C:sqlsbase.bak’ WITH REPLACE;

GO

Вернем экземпляр SQL в состояние «в сети».

Стартуем сервер в многопользовательском режиме:

net start MSSQLSERVER

На этом – все, желаю удач.

Разное

Если Вы хотите обменяться ссылками со мной между сайтами — пишите в контактах

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

Для того чтобы разработать эффективный план резервного копирования в Microsoft SQL Server2000, Вам необходимо научиться восстанавливать данные из резервной копии. Восстановление может понадобиться в случае сбоя в работе оборудования или при необходимости создания на другом сервере абсолютно идентичной копии существующей базы данных.

Запомните, если Вы восстанавливаете базу, используя Simple Recovery Model, Вам нужно будет восстановить только последнюю полную копию. Если же Вы используете Full или Bulk Recovery Model , Вы должны восстановить полную копию, затем последнюю дифференциальную копию и все копии журналов транзакций. Изучим подробнее процессы восстановления.

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

Независимо от модели восстановления, первым шагом всегда является восстановление последней полной резервной копии. Для восстановления БД в Enterprise Manager, следует выделить базу данных, дважды щелкнуть по ней правой кнопкой мыши и выбрать в контекстном меню “All Tasks > Restore Database”, после этого откроется диалоговое окно, показанное на Рисунке A.

Диалоговое окно Restore Database позволяет просматривать все последние резервные копии в хронологическом порядке. Там же Вы можете выбрать базу данных, которую нужно восстановить. На вкладке Options показанной на Рисунке B, Вы можете выбрать сделующие опции:

  • Eject tapes after restoring each backup (выгружать ленту после каждого восстановления)
  • Prompt befor restoring each backup (выдавать дополнительное предупреждение перед началом восстановления каждой копии)
  • Force restore over existing database (осуществлять восстановление поверх существующей базы данных), эта опция эквивалентна Move в T-SQL.

В нижней части окна находятся три переключателя, которые позволяют определить состояние базы после восстановления копии:

  • Leave Database Operational. No Additional Transaction Logs Can Be Restored.
  • Если Вы выбрали это значение, то после загрузки резервной копии будет инициирован процесс восстановления, что приведет к откату всех незавершенных транзакций. Станет невозможной загрузка дополнительных копий журнала транзакций. Пользователи получат возможность нормально работать с базой данных.
  • Leave Database Nonoperational But Able To Restore Additional Transaction Logs.
    • По окончании загрузки копии база данных будет оставаться временно недоступной. Будет необходимо загрузить дополнительные копии, после чего инициировать процесс восстановления.
    • Leave Database Read-Only And Able To Restore Additional Transaction Logs.
      • База данных становится доступной только для чтения. Вы можете загрузить дополнительные резервные копии журнала транзакций. Эта опция используется для создания резервного сервера (Standby Server)
      • Для восстановления базы данных и журналов транзакций осталось просто нажать кнопку OK.

        Восстановление базы данных с помощью T-SQL.

        Восстановление базы данных можно выполнить и с помощью T-SQL, который предлагает больше опций чем Enterprise Manager. Синтакс использования T-SQL команды следующий:

        Для детального изучения каждой опции следует прочитать описание в SQL Server 2000 Books Online.

        На Рисунке C показано восстановление базы данных Pubs из полной копии с устройства резервного копирования.

        Восстановление базы данных из дифференциальной копии.

        Если Вы используете Full или Bulk Recovery Model, Вы должны выполнить сначала восстановление полной резервной копии, затем последней дифференциальной копии и всех журналов транзакций. Для выполнения восстановления базы данных, используя дифференциальную копию, в Enterprise Manager необходимо выделить базу данных, дважды щелкнуть по ней правой кнопкой мыши и выбрать в контекстном меню “All Tasks > Restore Database”, выбрать восстановления полной и дифференциальной копии базы данных, а затем нажать OK. (исунок D)

        Синтаксис команды Restore для выполнения восстановления с использованием дифференциальных копий, показан на Рисунке Е.

        Читайте также:  Device harddisk0 как исправить

        Восстановление журнала транзакций.

        Перед началом восстановления журнала транзакций, Вы должны восстановить полную и последнюю дифференциальную копию базы. Затем Вы можете восстанавливать журналы транзакций в соответсвующем порядке. Если Вы используете Enterprise Manager, нужно выделить базу данных, дважды щелкнуть по ней правой кнопкой мыши и выбрать в контекстном меню “All Tasks > Restore Database”, выбрать все нужные копии и, если есть необходимость, опцию Point in Time Restore (восстановление на определенный момент времени) (Рисунок F).

        Синтаксис команды Restore для восстановления журнала транзакций, показан на РисункеG.

        Подведем итоги. Резервное копирование и восстановление базы данных – это одна из основных , наиболее важных задач администратора баз данных. В любой момент времени Вы должны быть уверены в своей способности восстановить базу данных SQL Server 2000 согласно Вашему плану аварийного восстановления. Если же у Вас нет плана аварийного восстановления, я рекомендую начать работать над ним. В случае, если что-то произойдет и данные будут потеряны, следующей потерей для Вас может стать потеря места работы.

        Очень часто приходится отвечать на вопрос "как восстановить базу данных в MS SQL 7.0" c уточнением, что при попытке выполнения комманды RESTORE DATABASE сервер ругается так:

        Server: Msg 3156, Level 16, State 1
        The file ‘g:MSSQL7datamydb_data.mdf’ cannot be used by RESTORE. Consider using the WITH MOVE option to identify a valid location for the file.

        Server: Msg 3158, Level 16, State 1
        Could not create one or more files. Consider using the WITH MOVE option to identify valid locations.

        Чтобы сэкономить время на написание писем, приведу решение.

        Собственно на решение прозрачно намекают в обоих сообщениях об ошибке. Возможная причина возникновения ошибки — backup (dump) достался по наследству, и имя_файла_в_операционной_системе сохранённое в заголовке бэкапа не может быть использовано. Например, если на сервере, на котором производился бэкап, файлы данных и логов были размещены на диске G:, а на сервере, на котором производится восстановление БД такого диска нет и в помине.

        В приведённом ниже примере проводится восстановление БД myDB, файлы которой до бэкапа размещались на диске G:, а после восстановления будут перемещены на диск D:.

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

        в полученном резалтсете нас особенно интересует первая колонка:

        впрочем, если администратор БД, доставшейся по наследству, не использовал говорящих логических и физических имён для файлов данных и логов, нам пригодится третья колонка. D — файлы данных, L — файлы логов.

        Теперь используем эти имена для восстановления БД с использованием опции MOVE :

        Как видим, если знаешь что делать, ничего сложного нет 🙂

        Восстановление логинов пользователей базы данных.

        При выполнении команды RESTORE базы данных процесс восстановления данных и логов не может считаться заершённым, если не были восстановлены логины пользователей БД. Если операции backup/restore проводились на разных серверах, то связи пользователей БД с их логинами будут разорваны, и их нужно исправить. Для тех кто не хочет это делать при помощи Enterprise Manager, приведу простенький скрипт.

        Сначала определим пользователей ущемлённых в их правах:

        и в этом резалтсете нас будет интересовать первая колонка:

        Если резалтсет пустой, то администратор может считать процесс восстановления завершённым, если нет, то для каждой строки резалтсета (или для каждого ущемлённого пользователя, что одно и тоже) необходимо выполнить скрипт аналогичный следующему:

        И это ещё не всё. Если хранимая процедура sp_change_users_login не нашла соответствия старому логину, она создаст новый с пустым паролем (что в общем-то логично), поэтому

        Проверяем логин для пользователя dummy

        в резалтсете смотрим в третью колонку

        если логин новый, то меняем для него пароль:

        Повторяем операцию для каждого нового логина, и после этого идём спокойно пить пиво.

        Ссылка на основную публикацию
        Adblock detector