Разбираемся с утилитами для бэкапа баз данных. Резервное копирование MS SQL Резервное копирование бд ms sql
В данной статье будет рассказано как вручную сделать полную резервную копию базы данных в с помощью программы «Среда Microsoft SQL Server Management Studio».
1. Создание резервной копии
На самом деле все довольно просто. Запускаем оснастку «» («Пуск » — «Все программы » — «SQL Server 2008 R2 » — «Среда Microsoft SQL Server Management Studio ») и вводим данные для авторизации.
После чего в Обозревателе объектов раскрываем вкладку «Базы данных » и кликнем правой кнопкой мыши по той базе данных, для которой необходимо сделать резервную копию. В появившемся контекстном меню выберем «Задачи » (Tasks ) — «Создать резервную копию » (Back Up… ) .
Запустится окно «Резервная копия базы данных » (Back Up Data Base ) . Убедимся, что стоит «Полная » (Full ), при необходимости зададим имя и описание, а также укажем назначение резервной копии. По умолчанию выбран путь на жестком диске компьютера в папку Backup основного расположения баз SQL-сервера. Для того чтобы изменить место размещения копии, сначала нажмем «Удалить » (Remove ), чтобы удалить существующее назначение, а затем «Добавить » (Add …) для добавления нового.
Здесь зададим расположение и имя файла резервной копии и нажмем «ОК » . Таких мест назначений можно задать несколько. В этом случае резервная копия будет разбита на равные части, каждая часть в указанном файле.
Когда все настройки установлены, нажимаем «ОК » и дожидаемся завершения задачи. Если все сделано правильно, в указанной директории мы найдем файл резервной копии базы данных SQL.
2. Восстановление базы данных из резервной копии
Восстановление происходит по аналогичной схеме. В «Среде Microsoft SQL Server Management Studio » выбираем базу из которой сделана резервная копия , кликаем по ней правой кнопкой мыши, в контекстном меню выбираем «Задачи » (Tasks ) — «Восстановить » (Restore ) — «База данных… » (Database… ).
Откроется окно «Восстановление базы данных » (Restore Database ). Здесь, в качестве источника укажем «С устройства » (From device ) и выберем файл резервной копии (созданных в пункте 1).
Установим флаг «Восстановить » (Restore ) напротив выбранной резервной копии. При необходимости, на вкладке «Параметры » (Options ), можно указать дополнительные параметры восстановления, о значении которых можно прочитать .
После того, как все настройки сделаны, жмем «ОК » и дожидаемся сообщения об успешном восстановлении базы данных.
3. Восстановление резервной копии в другую базу данных (копирование данных)
Если же необходимо загрузить данные в базу данных, отличную от той из которой была сделана резервная копия , то при загрузке помимо действий, описанных в пункте 2, необходимо на вкладке «Параметры » (Options) задать имена файлов этой базы данных и установить флаг «Перезаписывать существующую базу данных » (WITH REPLACE).
Помогла ли Вам данная статья?
«Кто владеет информацией - тот владеет миром» - Майер Амшель Ротшильд
Самое ценной сущностью в любом бизнесе является информация. Потеря информации может привести к непредсказуемым последствиям, в основном финансовым. Поэтому одной из главной задач IT специалистов является резервное копирование всей IT инфраструктуры. Это относится и базам данных MS SQL Server.
Для того, чтобы обеспечить сохранность информации в используемых базах данных, а также снизить время для восстановления работоспособности, необходимо производить периодическое резервное копирование SQL-серверов.
Разберем на простейшем примере: необходимо настроить резервное копирование базы данных на отдельный диск.
Решение:
- Открываем Microsoft SQL Server Management Studio . В навигационном меню справа открываем вкладку "Управление" . Там видим вкладку "Планы обслуживания" . Нажимаем правой кнопкой мыши -> "Создать план обслуживания" и даем имя нашему плану (Рис.1):
Рис.1 Создание нового плана обслуживания.
2. На панели элементов добавляем задачу "Резервное копирование базы данных" (Рис.2):
Рис.2 Добавление задачи "Резервное копирование базы данных".
3. На созданной задаче нажимаем правой кнопкой мыши -> "Изменить" (Рис.3):
4. В окне свойствах задачи выбираем тип резервной копии (в моем случае полный), выбираем нужную базу данных (у меня ka_cons), каталог для бэкапов, возможность проверки резервных копий на целостность и варианты сжатия оных (Рис.4-6):
Рис.4 Тип бэкапа - полный.
Рис.5 Выбор базы данных для бэкапа.
Рис.6 Определение каталога для бэкапов, проверки целостности и степени сжатия.
5. На панели настройки плана обслуживания справа. нажимаем на кнопку "Расписание" (Рис.7):
6. Настраиваем нужное нам расписание и нажимаем "Ок" (Рис.8):
Рис.8 Настройка расписания резервного копирования.
7. Сохраняем наш план обслуживания (Рис.9):
Рис.9 Сохранение плана обслуживания.
Полное резервное копирование базы данных по расписанию настроено.
Рекомендуется настроить регулярное резервное копирование базы данных (на случай аппаратных или программных сбоев), причем лучше всего с сохранением резервных копий за последние несколько дней, например семь (за последнюю неделю).
Для этого можно использовать либо встроенный в SQL Server планировщик заданий - «SQL Server Agent» (в бесплатную версию не входит), либо стандартный «Планировщик Windows» в сочетании с утилитой SQLCMD.EXE, которая позволяет выполнять запросы к SQL Server из командной строки. В планировщике необходимо создать как минимум семь заданий (по одному на каждый день недели), каждое из которых будет (раз в неделю) заменять один из семи файлов, содержащих соответствующую резервную копию базы данных.
Кроме того, файлы резервных копий рекомендуется хранить не только на жестком диске компьютера, где установлен SQL Server, но и дублировать их на ленту или жесткий диск другого компьютера в сети. Для этого можно использовать либо специальное ПО, которое позволяет делать резервные копии всего диска, либо с помощью того же планировщика копировать файлы на ленту или другой компьютер (вторым шагом).
С помощью «Планировщика Windows» (для бесплатной версии)
Чтобы создать задание в «Планировщике Windows» надо:
Запустить программу «Блокнот» (Пуск->Все программы->Стандартные->Блокнот) и ввести следующие две строки, после чего сохранить их в виде командного файла (*.BAT):
SQLCMD -S (local) -E -Q "BACKUP DATABASE AltaSVHDb TO DISK = "D:\BACKUP\ AltaSVHDb_monday.bak" WITH INIT, NOFORMAT, SKIP, NOUNLOAD"
XCOPY D:\BACKUP\ AltaSVHDb_monday.bak \\BACKUP_SERVER\Folder\*.* /Y
где «(local)» - имя сервера (в случае установки именованного экземпляра SQL Server надо указать имя полностью: «ИМЯ_КОМПА\SQLEXPRESS»), «AltaSVHDb» - имя базы данных, «D:\BACKUP\ AltaSVHDb_monday.bak» - имя файла для создания в нем резервной копии (будет различаться по дням недели), «BACKUP_SERVER» - имя компьютера, на который будет выполняться дополнительное копирование, «Folder» - папка на этом компьютере (к ней должен быть предоставлен общий доступ).
Запустить мастер планирования заданий (Панель управления->Назначенные задания->Добавить задание) и нажать кнопку «Далее»:
Нажать кнопку «Обзор» и указать путь к командному файлу (*.BAT), созданному на шаге a):
Указать имя для задания, выбрать вариант запуска «еженедельно» и нажать кнопку «Далее»:
Поставить галочку возле нужного дня недели, а в поле «Время начала» указать время, когда должен запускаться процесс резервного копирования (обычно это делается ночью), затем нажать кнопку «Далее»:
Ввести имя пользователя и пароль (дважды) учетной записи ОС, от имени которой будет выполняться задание, и нажать кнопку «Далее»:
Внимание! Чтобы задание успешно выполнялось необходимо предоставить указанной здесь учетной записи (домена или локального компьютера) права записи в вышеупомянутую папку «\\BACKUP_SERVER\Folder» , а также настроить доступ к самому SQL Server.
Нажать кнопку «Готово»
Примечание . Чтобы проверить работоспособность созданного задания, необходимо в списке заданий (Панель управления->Назначенные задания) нажать правой кнопкой мыши на интересующем задании и в контекстном меню выбрать пункт «Выполнить», затем убедиться, что файл резервной копии БД успешно создался по тем путям, которые были указаны на шаге a).
С помощью «SQL Server Agent» (в бесплатную версию не входит)
Чтобы создать задание в «SQL Server Agent» надо:
Запустить утилиту SQL Server Management Studio и подключиться к серверу под учетной записью администратора.
В левой части окна нажать правой кнопкой мыши на разделе «Объекты сервера/Устройства резервного копирования» и в контекстном меню выбрать пункт «Создать устройство резервного копирования»:
В поле «Имя устройства» ввести имя, которое будет ассоциироваться с файлом резервной копии БД, при необходимости изменить путь в поле «Файл» и нажать «ОК»:
В левой части окна нажать правой кнопкой мыши на разделе «Агент SQL Server/Задания» и в контекстном меню выбрать пункт «Создать задание»:
В поле «Имя» ввести имя задания:
На странице «Шаги» нажать кнопку «Создать»:
В появившемся окне ввести имя в поле «Имя шага», проверить, что в поле «Тип» выбрано «Сценарий Transact-SQL (T-SQL)», а в поле «Команда» ввести строку:
BACKUP DATABASE AltaSVHDb TO AltaSVHDb_monday WITH INIT, NOFORMAT, SKIP, NOUNLOAD
где «AltaSVHDb» - имя базы данных, «AltaSVHDb_monday» - имя устройства резервного копирования, созданного на шаге c) (будет различаться по дням недели):
В предыдущем окне нажать кнопку «ОК», в результате на странице «Шаги» должна появиться строка:
Чтобы файл резервной копии БД сразу копировался на другой компьютер в сети необходимо повторить пункты f) - h), в окне «Создание шага задания» выбрав в поле «Тип» значение «Операционная система (CmdExec)», а в поле «Команда» указав строку:
XCOPY D:\MSSQL\BACKUP\AltaSVHDb_monday.bak \\BACKUP_SERVER\Folder\*.* /Y
где «D:\MSSQL\BACKUP\AltaSVHDb_monday.bak» - путь, указанный на шаге c) (будет различаться по дням недели), «BACKUP_SERVER» - имя компьютера, на который будет выполняться копирование, «Folder» - папка на этом компьютере (к ней должен быть предоставлен общий доступ):
Примечание . Чтобы копирование файла успешно выполнялось необходимо запускать «SQL Server Agent» под учетной записью домена Windows, для которой предоставлены права записи в вышеупомянутую папку (см. также «SQL2005_installation.doc» или «SQL2008_installation.doc»), а также настроен доступ к самому SQL Server (см. раздел «Настройка прав доступа к БД», включить эту учетную запись надо в роль «sysadmin» на странице «Серверные роли», а на страницах «Сопоставление пользователей» и «Защищаемые объекты» ничего не делать).
На странице «Расписания» нажать кнопку «Создать»:
Ввести имя в поле «Имя», проверить, что в поле «Тип расписания» выбрано значение «Повторяющееся задание», а в поле «Выполняется» - «Еженедельно». Поставить галочку возле нужного дня недели (остальные снять), а в поле «Однократное задание» указать время, когда должен запускаться процесс резервного копирования (обычно это делается ночью):
В предыдущем окне нажать кнопку «ОК», в результате на странице «Расписания» должна появиться строка:
Нажать кнопку «ОК».
Примечание . Чтобы проверить работоспособность созданного задания, необходимо в разделе «Агент SQL Server/Задания» нажать правой кнопкой мыши на интересующем задании и в контекстном меню выбрать пункт «Запустить задание на шаге», в появившемся окне выбрать первый шаг данного задания и нажать «ОК». Далее появится окно отображающее ход выполнения задания. Если выполнение задания закончится с ошибкой, то подробное описание ошибки можно увидеть вызвав пункт «Просмотр журнала» того же контекстного меню.
Восстановим базу «Test _Recovery » в состояние «t 4 ».
Приступим к восстановление базы данных из полной резервной копии «Full2_Test_Recovery.bak» используя « SQL Server Management Studio ». Щелкаем правой кнопкой мыши по базе « Test _ Recovery », в появившемся меню выбираем « Tasks », далее « Restore », потом « Database ».
В появившемся окне « Restore Database » в разделе « Sourse » выбираем « Device ». Далее « Add », прописываем путь «\\ vniz - tst - bkp 01. test . local \ Backup _ SQL \ Full 2_ Test _ Recovery . bak », нажимаем « Ok ». В разделе «Destination» выбираем Database «Test Recovery»
Нажимаем «Ok»
База успешно восстановится.
Рассмотрим восстановление базы данных используя Transact-SQL.
Щелкаем правой кнопкой мыши по базе «Test_Recovery», в появившемся меню выбираем «New Query»:
В появившемся окне вводим:
USE master
RESTORE DATABASE Test_Recovery
FROM DISK = "\\vniz-tst-bkp01.test.local\Backup_SQL\Full2_Test_Recovery.bak"
WITH REPLACE
База успешно восстановится.
В этом примере мы использовали параметр «REPLACE»:
Восстановление обычно не допускает случайной перезаписи базы данных другой базой данных. Если указанная в инструкции RESTORE база данных уже существует на текущем сервере, а идентификатор GUID семейства для заданной базы данных отличается от идентификатора GUID семейства для базы данных, записанного в резервном наборе данных, то ее восстановление не будет выполнено.
Параметр REPLACE отменяет несколько важных проверок, обычно выполняемых операцией восстановления. Отменяются следующие проверки.
- Проверка на восстановление поверх существующей базы данных резервной копии, созданной для другой базы данных. При использовании параметра REPLACE при восстановлении можно записать данные поверх существующей базы данных независимо от того, какие базы данных содержатся в резервном наборе данных, даже если указанное имя данных отличается от записанного в резервном наборе. Это может привести к случайной перезаписи поверх базы данных другой базы данных.
- Проверка на восстановление базы данных, использующей модель полного восстановления или модель восстановления с неполным протоколированием, для которой не была создана резервная копия заключительного фрагмента журнала, и не был применен параметр STOPAT. При использовании параметра REPLACE возможна потеря зафиксированных данных, поскольку последние записанные в журнал данные еще не были скопированы в резервную копию.
- Перезапись существующих файлов.
Давайте рассмотрим нежелательную ситуацию. А именно: по какой-то причине вышла из строя БД. Что есть у нас? Полная копия, диференциальная копия на вчера, но на сегодня тоже есть данные, неужели нужно было делать диф.копирование каждый час? - Нет! Есть Журнал транзакций
.
Журнал транзакций - журнал, в который записываются все транзакции и все изменения базы данных, выполняемые каждой транзакцией. Т.е. любое действие с БД пошагово запысается в журнал. Каждая запись отмечается СУБД на предмет завершённости транзакции, выполнена или нет. С его помощью, можно восстановить состояние БД не только после сбоя, а и при непредвиденных действиях с данными. Откатить до определённого времени. Как и с БД, с журналом транзакций нужно проводить резервное копирование, полное, дифференциальное, инкрементное. Для восстановления части журнала транзакций после сбоя в промежутке между созданием резервных копий, нужно выполнить резервирование заключительного фрагмента журнала, который, по-сути, является точкой финализации резервного копирования. Выполняется после сбоя, как точка обратного отсчёта.
Итак, для восстановления БД после сбоя нам нужны - актуальная полная копия БД, дифференциальная копия БД и копия журнала транзакций.
Для самой базы данных существует 3 модели восстановления - простая, полная и модель с неполным протоколированием. Рассмотрим:
- Простая модель (Simple) - используется только полное резервирование. Нет диф. резервирования, как и резервирования журнала транзакций. Полные копии нужно создавать как можно чаще. Актуально для БД, используемых "только для чтения".
- Модель полного восстановления (Full) - наиболее применяемая модель, при которой доступны все функции резервного копирования данных и их восстановление. Поддерживает восстановление отдельных страниц данных. Происходит полное протоколирование транзакций сохраниние журнала транзакций.
- Модель с неполным протоколированием (Bulk-Logged) - предназначена, как дополнение к полной модели полного восстановления. Не поддерживает протоколирование большинство массовых операций, соответственно - не поддерживает восстановление БД до определённого момента времени.
Рассмотрим наиболее актуальную цепочку создания резервных копий: Полное резервирование - раз в неделю, Дифференциальное резервирование - раз в день, Резервирование журнала транзакций - раз в час.
Есть несколько вариантов создания резервных копий:
- С помощью встроенного планировщика задач MS SQL
- С помощью языка Transact-SQL
- С помощью sqlcmd и Планировщика задач ОС
- Вручную (что нас не устраивает, ибо тру админ должен постоянно бездельничать)
Рассмотрим первый вариант, как наиболее юзабельный. Для этого используется Windows Server 2008 R2 Enterprise и MS SQL Server 2008 Eng.
Итак, допустим, что у нас есть БД TECH:
Переходим к инструменту создания Джобы:
Трём правую клавишу мышака и вызываем Мастера Джобу:
Выбираем галочку "Отдельное выполнение каждого задания", мы ведь выполняем только одно действие
Мастер без тюрбана, но ведь не в размере тюрбане главное)) Выбираем тип желания, в нашем случае - полное резервирование:
Мастер Джоба, как оказалось, чуточку немного еврей, поэтому переспрашивает ещё раз:
"Параметры дополнительные выбрать стоит, о юнный паддаван!":
здесь выбираем БД, срок хранения резервной копии, адрес (лента или диск), путь сохранения и главное - планировщик заданий!
"Не стоит о базе данных забывать при выборе своём. Сконцентрируй силу и выбери БД":
"Слишком быстро ты спешишь задание создать, нажать на кнопку стоит, что внизу с названием Shedule - Define".
Собсно, планировщик заданий, где выбираем тип (повторение, единожды и т.д.), день, время, тип старта:
Вот и всё, создали. Мастер Джоба крут и зелен. Смотрим в Maintance Plans состояние:
Для параноиков, не бойтесь в этом признаться зеркалу, стоит заглянуть в душу SQL Server Agent - Job Activity Monitor, Мастер Джоба подробно покажет всё:
Теперь, при удовлетворении заданных условий, должен создаться полный бэкап БД. По такому же принципу, создаётся диф.резервирование и резервирование журнала транзакций (эти подпункты разположены ниже "Полное резервирование" в списке выбора заданий).
Крутите уши MSSQL-ю, как Вам удобно, не отвертите
В следующей статье - создание с помощью Transact-SQL и пара примеров.
