Копирование базы SQL

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

О чем данная статья

В ней описан тот способ, как с помощью разработанного мною .NET приложения можно распространять план резервного копирования и проводить все необходимые настройки над БД средствами СУБД с уведомлением администратора о выполнении задач.
По максимум постараюсь описать те нюансы, с которыми мне пришлось столкнуться в ходе разработки приложения и настройки БД.
Для описанных ниже задач можно использовать мастер планов обслуживания, но мне больше понравился такой подход. Основное преимущество описанного мною метода, что данный способ можно применять ко всем версиям MS SQL (кроме Express, там немного другой подход). План обслуживания можно переносить, но у вас должна быть соответствующая в версия MS SQL и все равно будет создан Job для запуска плана обслуживания.
Сравнить редакции MS SQL и их функционал вы можете вот .
Осторожно: перфекционисты могут испытать жжение и боль при просмотре кода моего приложения, т.к. оно было написано на скорую руку и заточено под решение конкретной задачи.
Кому подойдет данная статья:

  • Тем, у кого MS SQL Express и нет возможности запускать с помощью Job задачи
  • Тем, кто в ближайшем будущем планирует перейти с MS SQL 2008 на более новую версию и не хочет настраивать зеркалирование БД, а сразу на новой версии настроить AlwaysOn
  • Тем, у кого нет средств для поднятия еще резервных серверов и приходится обходиться тем, что есть.
  • У кого нет сжатых сроков на время восстановления БД. Главное – это результат
  • Кому лень что-то делать
  • Просто любопытным людям.

Оглавление:
Теория о резервном копирование

  1. Журнал транзакций
  2. Разностная копия БД
  3. Системные базы данных
  4. План бекапирования
  5. Общие рекомендации по резервному копированию

Используем приложение

  1. Настройка уведомления администратора
  2. Дополнительные уведомления для администратора
  3. Решение проблем при настройке DatabaseMail
  4. Настраиваем резервное копирование с помощью приложения для SQL Standart
  5. Настраиваем резервное копирование с помощью приложения для SQL Express
  6. Удаление задач из БД
  7. Удаление копий БД

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

  1. Создание и хранение резервных копий баз данных в MS SQL. Практические советы
  2. Построение цепочки восстановлений баз данных MS SQL
  3. Настройка Database Mail в MS SQL Server 2005 и старше
  4. SQL Server 2008: бэкапим с умом. Часть 1: Теория
  5. Всё что вы стеснялись спросить о бэкапах Microsoft SQL Server

Исходники на github для MS SQL Standart и для MS SQL Express
Если появится желающие добавить свои мысли в код, принимаю pull request. Готов выслушать конструктивную критику и доработать приложение, если это действительно кому-то нужно будет.

Теория о резервном копирование

Все что описано в теории, вы можете найти самостоятельно. Конфигурации, которые описаны в данном разделе, автоматически будут выполнены моим приложением при настройке резервного копирования.
MS SQL Server поддерживает 3 модели резервного копирования.

  1. Простую
  2. Модель полного восстановление
  3. Модель полного восстановления с неполным протоколированием

Я выбрал для приложения модель полного восстановления, т.к. мне необходимо было иметь возможность всегда восстановить последнюю версию БД после любой операции и у меня не было одномоментных массовых операций по вставке данных. Если вы только начинаете и не знаете, как правильно выбрать, вам может помочь вот эта статья Microsoft.
Для включения данного режима, необходимо выполнить следующий скрипт
ALTER DATABASE SET RECOVERY FULL;
При переключении модели восстановления на полную у нас появится следующие возможности:

  1. СУБД перестанет автоматически очищать журнал транзакций . Журнал будет расти до тех пор, пока не будет сделана его резервная копия. Это важный момент, администратору БД необходимо продумать вопрос о плане резервного копирования и очистки журнала. UPD: спасибо за помощь Yggaz
  2. Создание разностной резервной копии
  3. Создание полной резервной копии

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

1. Журнал транзакций

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

  1. восстановление отдельных транзакций;
  2. восстановление всех незавершенных транзакций при запуске SQL Server;
  3. накат восстановленной базы данных, файла, файловой группы или страницы до момента сбоя и т.д

Рекомендации

  1. Вынести на быстрый жесткий диск, чтобы при большом потоке операций не было задержек при записи.
  2. Необходимо делать резервные копии журнала транзакций не реже чем каждый час.
  3. После создания полной (разностной) копии базы данных, все старые журналы можно удалять, т.к. они теряют свою актуальность.
  4. Внимательно следите за размером диска на котором хранятся журналы транзакций, если оно закончится, то записать новые данные в БД будет невозможно, пока не произойдет уменьшение размеров журнала транзакций или не добавиться новый дополнительный файл транзакций.
  5. Журнал транзакций необходимо регулярно усекать, чтобы избежать его переполнения. UPD: Как сказал kolu4iy данная операция по усечению слегка сомнительна в плане производительности, т.к. при бэкапирование журнал транзакции очищается внутри и СУБД начинает писать в нем по новой. Однако у вас может возникнуть ситуация, которую описал я в своем комментарии и тогда это вам может пригодиться.
  6. Возможна ситуация, когда невозможно сразу сделать усечение журнала. Они описаны в данной статье
  7. Для получения информации о состоянии базы данных можно с помощью следующего запроса:
    select name,log_reuse_wait, log_reuse_wait_desc from sys.databases
  8. При необходимости можно получить информацию о последних открытых транзакциях DBCC OPENTRAN (Имя базы данных) WITH TABLERESULTS

Пример SQL скрипта для выполнения резервного копирования журнала транзакции с последующим усечением файла.
BACKUP LOG TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL\MSSQL\Backup\.bak’ WITH NOFORMAT, NOINIT, NAME = N’Журнал транзакций Резервное копирование’, SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO USE GO DBCC SHRINKFILE (N’Имя файла лога БД’ , 25) GO
Эти же операции можно проделать с помощью SSMS

2.Разностная копия БД

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

  1. Используйте разностные копии БД, если создание полной копии БД занимает большой промежуток времени
  2. Периодически делайте полную копию БД, чтобы уменьшить объемы создаваемых разностных копий.
  3. После создания полной копии БД, все предыдущие разностные копии теряют свою актуальность.

Более подробно о рекомендациях по частоте созданию разностных резервных копий, можно прочитать .
Приведу небольшой пример из практики, почему мы стали использовать разностную копию. Со временем у нашего клиента разрослась база данных до таких размеров, что создание полной резервной копии занимало 8 часов, еще несколько месяцев и возможно к началу рабочего дня не успевало бы завершиться данная операция. После перевода на разностное резервное копирование, мы сократили время с 8 часов до 2-4 минут (в зависимости от дня недели). Раз в неделю мы делали полную копию БД.
Пример SQL для создания резервной разностной копии БД с проверкой копии по завершению (отличается от полного копирования флагом DIFFERENTIAL вместо него нужно использовать NOFORMAT).
declare @pathBackup as varchar(55) set @pathBackup = N’C:\Backup\_’ + REPLACE(convert(varchar,GETDATE(), 104),’.’,’_’) + ‘.bak’ BACKUP DATABASE TO DISK = @pathBackup WITH DIFFERENTIAL, NOFORMAT, INIT, NAME = N’Полная База данных Резервное копирование’, SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM GO declare @backupSetId as int declare @pathBackup as varchar(55) set @pathBackup = N’C:\Backup\_’ + REPLACE(convert(varchar,GETDATE(), 104),’.’,’_’) + ‘.bak’ select @backupSetId = position from msdb..backupset where database_name=N» and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N») if @backupSetId is null begin raiserror(N’Ошибка верификации. Сведения о резервном копировании для базы данных «» не найдены.’, 16, 1) end RESTORE VERIFYONLY FROM DISK = @pathBackup WITH FILE = @backupSetId, NOUNLOAD, NOREWIND GO

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

Помимо основной базы и связанных с ней файлов, я настоятельно рекомендую делать копии и системных баз данных. Начнем с того, что рассмотрим какие базы существуют в MS SQL. Их всего 5:

Название Описание
База данных master В этой базе данных хранятся все данные системного уровня для экземпляра SQL Server.
База данных msdb Используется агентом SQL Server для планирования предупреждений и задач.
База данных model Используется в качестве шаблона для всех баз данных, создаваемых в экземпляре SQL Server. Изменение размера, параметров сортировки, модели восстановления и других параметров базы данных model приводит к изменению соответствующих параметров всех баз данных, создаваемых после изменения.
База данных resource База данных только для чтения. Содержит системные объекты, которые входят в состав SQL Server. Системные объекты физически хранятся в базе данных Resource, но логически отображаются в схеме sys любой базы данных.
База данных tempdb Рабочее пространство для временных объектов или взаимодействия результирующих наборов.

Более подробно можете прочитать о них и еще вот .
Я выбрал резервировать только 2 системные БД:

  1. msdb – потому что, там хранятся настроенные задачи и другие
  2. master – хранятся все произведенные настройки SQL Server.

Данная информация все равно не сильно критична и ее можно восстановить руками, но зачем тратить лишнее время, когда можно просто взять из резервной копии.

4. План бекапирования

На основе выше описанного составим наш план резервного копирования данных. Он может отличаться от того, что потребуется вам, все зависит от требований к восстановлению БД. Когда я подготавливал план, мне пришлось учесть, что необходимо восстановить данные максимально и потеря данных составляла не больше одного часа.
Мы будем делать следующие резервные копии:

  • Полная копия основной БД, чаще чем раз в неделю нет необходимости
  • Разностная копия основной БД, каждый день
  • Копии журнала транзакций основной БД, каждый час
  • Копия системной БД master, раз в неделю
  • Копия системной БД msdb, раз в неделю

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

День недели Время Действия Частота Описание
Понедельник — Пятница С 8-00 до 21-00 Резервные копии
Журнала транзакций
Каждый час После выполнения резервной копии БД идет сжатие и усечение журнала транзакций
Суббота — Воскресенье С 8-00 до 18-00
Понедельник – Воскресенье 22-00 Разностная копия основной БД 1 раз в день После успешного выполнения разностной копии удаляются все старые копии журнала транзакций
Суббота 12-00 Проверка БД 1 раз в день Проверка БД Дело на целостность.
Суббота 18-00 Создание полной копии БД 1 раз в день По завершению данной операции идет уведомление на почту.
Если создание резервной копии прошло удачно, удаляется

  • старая полная резервная копия
  • все старые разностные копии
  • все старые журналы транзакций

Понедельник – Воскресенье 23-30 Создание копии системной базы master 1 раз в день Хранится всегда только последний экземпляр БД
Воскресенье 12-30 Создание копии системной базы msdb 1 раз в месяц Хранится всегда только последний экземпляр БД

5. Общие рекомендации по резервному копированию

  1. Используйте опцию BACKUP WITH CHECKSUM
    чтобы убедиться, что все прошло хорошо. Недостатком такого решения является то, что для больших баз данных проверка контрольной суммы может серьезно загрузить систему.
  2. Не выполняйте резервное копирование файлов на тот же физический диск, на котором хранится база данных или протокол транзакций.
  3. Если вы используете MS SQL 2008 или выше, рекомендую вам использовать сжатие резервных копий средствами SQL. Следующий код включит сжатие по умолчанию: USE master; GO EXEC sp_configure ‘backup compression default’, ‘1’; RECONFIGURE WITH OVERRIDE;
  4. держите резервные копии по нескольку дней на случай, если одна из них будет повреждена – старая резервная копия лучше, чем никакой.
  5. Используйте DBCC CHECKDB для проверки каждой базы данных перед копированием, это своевременно предупредит вас о надвигающихся проблемах. DBCC CHECKDB (‘Имя базы данных’) WITH NO_INFOMSGS, ALL_ERRORMSGS; Примечание: на практики мы использовали данную проверку, только перед выполнением полной резервной копии.
  6. Выполняйте периодически обновление статистики и реорганизации индексов БД

Используем приложение

Несколько нюансов по приложению:

  • Все тексты и запросы в коде вынесены в ресурсы, мне так было проще
  • При вводе параметров соединения и других настроек, они сохраняются в файл. Для Express и Standart используются разные файлы (dbStandart, udExpress) в них хранится класс UserData
  • Для выполнения некоторых операций могут потребоваться права администратора
  • На данный момент не работает соединение с БД под доменной учетной записью
  • Программа не обладает суперкрасивым интерфейсом

1. Настройка уведомления администратора

Мне было лень каждый раз заходить на сервер и проверять, сработала ли задача или произошла какая-то ошибка. Да и хотелось иметь возможность получать другие уведомления, не только о выполнения задач.
Для данной цели используется DatabaseMail MS SQL (для версии Standart и выше)
В своем приложение я сделал специальный раздел для автоматизации данной задачи

При нажатии появится форма для заполнения информации необходимой для создания профиля рассылки писем:

Приложение автоматически настроено на стандартный 25 SMTP порт для адреса с которого отправляются письма. При необходимости его можно изменить в процедуре sysmail_add_account_sp
Пользователь и пароль требуются на случай, если у почтового сервиса настроена аутентификация.
Имя оператора в системе указывается для того, чтобы у нас нормально создался профиль в DatabaseMail. Пишите любое название, которое будет для вас понятным. Ниже приведен пример заполненной формы.

Дальше, с данного ящика от указанного оператора, к нам будут приходить уведомления об успешном выполнении операций.
Выполняемые действия на данном этапе:

  1. Меняются системные параметры MS SQL.
  2. Создается DatabaseMail Profile
  3. Активируется в SQL Agente профиль
  4. Создается DatabaseMail Account
  5. Добавляется DatabaseMail Account к Database Mail Profile
  6. Создается DatabaseMail Operator

Более подробно описано в следующей статье и, частично, я брал отсюда. Естественно, данные действия можно выполнить с помощью SSMS.

2.Дополнительные уведомления для администратора

В программе предусмотрены 2 задачи, применяемые к БД:

  1. проверка целостности БД. Для проверки базы данных использовалась стандартная процедура DBCC CHECKDB.
  2. информирование о свободном месте в файловых группах.
  3. Вторая задача была реализована с помощью запроса к системной таблице dbo.sysfiles
  4. Вот пример данного запроса, который выполнялся к базе:

Select NAME = left(a.NAME,15), a.FILEID, = convert(decimal(12,2),round(a.size/128.000,2)), = convert(decimal(12,2),round(fileproperty(a.name,’SpaceUsed’)/128.000,2)), = convert(decimal(12,2),round((a.size-fileproperty(a.name,’SpaceUsed’))/128.000,2)) , FILENAME = a.FILENAME From dbo.sysfiles a
Ответ с сервера приходит на почту администратора в виде html разметки. Данный синтаксис возможен благодаря следующей стандартной функции MS SQL FOR XML.
Так же пока я искал, как преобразовать в возвращаемый результат выполнения запросов в html текст, наткнулся на следующую страницу, где человек создал целую процедуру для этих целей
Настроить эти операции можно с помощью соответствующего пункта в меню программы:

Появиться окно для указания почтового ящика, на который необходимо высылать html текст отчета:

3.Решение проблем при настройке DatabaseMail

В MS SQL 2008 я столкнулся с проблемой при настройке SQL Server Agent. Симптомы следующие, после настройки невозможно запустить SQL Agent. В основном это решается с помощью установки update на SQL сервер.
Убедитесь, что установлен sp1, а потом можно уже ставить обновление.
Если данные обновления не помогают, необходимо скачать fix. Его можно найти на данном сайте конечную ссылку не могу указать сейчас, для того что бы дойти до пакета фикса, нужно будет ответить на ряд вопросов.
Если есть проблемы с модулем DatabaseMail. После настройки данного модуля с помощью приложения, необходимо зайти в SQL Agent и просмотреть журнал событий. Если там будут ошибки «невозможно подключиться к почтовому ящику». Значит есть проблема, даже если через проверку отправляется письмо.
Исправляется это следующими манипуляциями:

  1. Management Studio — SQL Server Agent — Properties.
  2. Alert System
  3. Уберите галочку с Enable mail profile
  4. Нажмите OК
  5. Зайдите снова и поставьте галочку
  6. Перезагрузите SQL Server Agent.

Проверьте учетную запись для SQL Agent service. Если это доменная учетная запись измените ее на системную или наоборот. Все должно заработать.

4.Настраиваем резервное копирование с помощью приложения для SQL Standart:

Выбираем версию Standart. Настраиваем уведомления. (см. раздел, настройки уведомления):

Соединяемся с БД, заполняя данные для соединения и указываем БД, для которой будет применяться Job:

Выбираем настройку резервного копирования:

Указываем пути для сохранения копий БД. Если указанные папки не существует, то программа попытается их создать (нужны соответствующие права).
Нажимаем сохранить и базе настраиваются соответствующие задачи. Желательно настроить для каждого бэкапа разные папки, т.к. при удалении будут удаляться все файлы с расширением bak. (см. раздел удаление копий БД)

5.Настраиваем резервное копирование с помощью приложения для SQL Express:

Так как в SQL Express отсутствует SQL Agent, задачу по автоматизации резервного копирования пришлось решить другим путем. В указанной пользователем папке создается bat файле в котором описан SQL запрос, отвечающий за создание резервной копии. В случае необходимости можно редактировать его напрямую. По мимо этого должен работать стандартный планировщик Windows, в нем создается задача, которая будет запускать раз в сутки в указанное время.
Для этого запускаем приложение. Выбираем пункт MS SQL Express:
Появляется форма для заполнения параметров:
Указываем где будет сохраняться наша копия, а также где будет лежать bat файл для создания копии базы (имя файла указывать не надо, оно будет задано автоматически). Далее указываем настройки соединение и время, когда необходимо запускать задачу.
Единственный минус данного подхода в том, что приходится храниться в открытом виде пароль для соединения с БД.

6.Удаление задач из БД.

Если необходимо удалить все задачи из БД (например, захотели изменить пути сохранения БД). Для этого используем соответствующий пункт в меню программы. Из SQL Agent будут удалены все задачи с определенным начальным префиксом (в моем случае King):

7.Удаление копий БД

В некоторых задачах, настроено удаление старых копий БД. Для этого я использую процедуру master.dbo.xp_delete_file. Пример использования: Удалит все файлы с расширением bak из указанной папки, дата создания которых превышает 14 дней.
EXECUTE master.dbo.xp_delete_file 0,»E:\backups»,N’bak’,dateadd(d,-14,getdate()),0;
И вот еще один более подробный пример и информация о том, какие параметры принимает данная функция.

Как восстанавливать резервные копии

Из-за нехватки времени модуль восстановления еще не реализован, возможно в будущем я его добавлю, а пока просто кратко опишу как можно будет восстановить базу.
С помощью SQL скрипта. Для восстановления базы данных используется команда RESTORE.
Если необходимо восстановить просто базу из полной копии, то достаточно выполнить следующий скрипт:
RESTORE DATABASE FROM DISK = ‘Z:\SQLServerBackups\back.bak’ WITH REPLACE
В случае, если необходимо восстановить последовательно сначала полную копию, разностные копии и журналы транзакций, тогда необходимо написать следующий SQL скрипт.
RESTORE DATABASE TEST_DB –восстанавливаем полную копию FROM test_db_full WITH NORECOVERY; GO RESTORE DATABASE TEST_DB –восстанавливаем разностную копию FROM test_db_diff WITH FILE = 1, NORECOVERY; GO RESTORE LOG TEST_DB –восстанавливаем журнал транзакций №1 FROM test_db_tran_1 WITH FILE = 1, WITH NORECOVERY; GO RESTORE LOG TEST_DB –восстанавливаем журнал транзакций №2 FROM test_db_tran_2 WITH FILE = 1, WITH NORECOVERY; GO RESTORE DATABASE TEST_DB WITH RECOVERY; GO
Для восстановления БД можно использовать так же и SSMS.

Использование клиент-серверного варианта работы ставит вопрос о выборе модели восстановления информационной базы. В данной статье мы рассмотрим какие существуют модели восстановления в СУБД MS SQL Server, какую из них выбрать и в каком случае.

СУБД MS SQL Server поддерживает 3 варианта варианта: simple (простая), full (полная), bulk logged (с неполным протоколированием). Последняя их них не имеет особого интереса для системы 1С:Предприятие.

Выбор модели восстановления

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

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

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

Рекомендации при переходе от одной модели восстановления к другой

После установки параметра «Модель восстановления» в свойствах базы данных, необходимо:

С полной модели на простую:

  1. Перед переходом: создать резервную копию журнала транзакций (это даст возможность восстановления базы данных на момент времени внутри данного журнала, в случае необходимости)
  2. После перехода: отключить задания резервного копирования журнала транзакций
  3. После перехода: убедиться в наличии задания создания резервной копии базы данных.

После переключения с простой модели на полную:

  1. Сразу же после переключения: создать полную копию базы (переключение вступает в силу с данного момента)
  2. Настроить план обслуживания: регулярного резервного копирования журнала транзакций регулярного полного резервного копирования базы данных.

В данной статье будет рассказано как вручную сделать полную резервную копию базы данных в SQL Server 2008 R2 с помощью программы «Среда Microsoft SQL Server Management Studio».

  1. Создание резервной копии
  2. Восстановление базы данных из резервной копии
  3. Восстановление резервной копии в другую базу данных (копирование данных)

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

На самом деле все довольно просто. Запускаем оснастку «Среда Microsoft SQL Server Management Studio» («Пуск» — «Все программы» — «SQL Server 2008 R2» — «Среда Microsoft SQL Server Management Studio» ) и вводим данные для авторизации.

После чего в Обозревателе объектов раскрываем вкладку «Базы данных» и кликнем правой кнопкой мыши по той базе данных, для которой необходимо сделать резервную копию. В появившемся контекстном меню выберем «Задачи» (Tasks) — «Создать резервную копию» (Back Up…) .

Запустится окно «Резервная копия базы данных» (Back Up Data Base) . Убедимся, что тип резервной копии стоит «Полная» (Full), при необходимости зададим имя и описание, а также укажем назначение резервной копии. По умолчанию выбран путь на жестком диске компьютера в папку Backup основного расположения баз SQL-сервера. Для того чтобы изменить место размещения копии, сначала нажмем «Удалить» (Remove), чтобы удалить существующее назначение, а затем «Добавить» (Add…) для добавления нового.

Здесь зададим расположение и имя файла резервной копии и нажмем «ОК» . Таких мест назначений можно задать несколько. В этом случае резервная копия будет разбита на равные части, каждая часть в указанном файле.

Далее перейдем на вкладку «Параметры» (Options), на которой можно можно указать, что резервную копию необходимо проверить после выполнения, а также задать другие параметры надежности.

Когда все настройки установлены, нажимаем «ОК» и дожидаемся завершения задачи. Если все сделано правильно, в указанной директории мы найдем файл резервной копии базы данных SQL.

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

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

Откроется окно «Восстановление базы данных» (Restore Database). Здесь, в качестве источника укажем «С устройства» (From device) и выберем файл резервной копии (созданных в пункте 1).

Установим флаг «Восстановить» (Restore) напротив выбранной резервной копии. При необходимости, на вкладке «Параметры» (Options), можно указать дополнительные параметры восстановления, о значении которых можно прочитать .

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

Восстановление резервной копии в другую базу данных (копирование данных)

Если же необходимо загрузить данные в базу данных, отличную от той из которой была сделана резервная копия, то при загрузке помимо действий, описанных в пункте 2, необходимо на вкладке «Параметры» (Options) задать имена файлов этой базы данных и установить флаг «Перезаписывать существующую базу данных» (WITH REPLACE).

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

  1. Операционная система семейства Windows (в моем примере используется Microsoft Windows Server 2008 R2)
  2. Установленный Microsoft SQL Server 2008 R2 (об установке SQL Server можно прочитать )
  3. Существующая база данный в SQL Server (о создании баз данных в SQL Server читайте)
  4. Настроенная компонента Database Mail, в случае если требуется уведомлять по электронной почте операторов о результатах выполнения плана обслуживания (о том как настроить компоненту Database Mail и создать оператора системы я писал ).

Первое что нам необходимо сделать, это убедиться что Агент SQL Server установлен и работает. Для этого запустим оснастку «Службы» («Пуск» (Start) — «Администрирование» (Administrative Tools) — «Службы» (Services) ) и в списке служб найдем службу «Агент SQL сервер» (SQL Server Agent).

Откроем свойства этой службы (кликнув по ней 2 раза) и убедимся что:

  • Тип запуска стоит «Автоматически» (Startup type: Automatic);
  • Состояние «Работает» (Service status: Started);

В противном случае, необходимо изменить параметры как на скриншоте выше и сохранить настройки нажав «Применить» (Apply) .

Теперь запустим программу «Среда SQL Sever Management Studio» ( «Пуск» (Start) — «Все программы» (All programs) — «Microsoft SQL Server 2008 R2» — «Средства SQL Server 2008 R2») и введем данные для авторизации.

После чего, еще раз убедимся что Агент SQL Server работает (в обозревателе объектов должна быть вкладка «Агент SQL Server» (SQL Server Agent) с зеленой иконкой слева.

Теперь перейдем непосредственно к созданию плана обслуживания. В обозревателе объектов (Object Explorer) раскроем вкладку «Управление» (Management), кликнем правой кнопкой мыши по вкладке «Планы обслуживания» (Maintenance Plans) и в контекстном меню выберем «Мастер планов обслуживания» (Maintenance Plan Wizard) .

В запустившемся мастере планов обслуживания на странице приветствия нажимаем «Далее» (Next) и в следующем окне вводим имя и описание нового плана.

Затем необходимо определиться с расписанием, по которому будет выполняться данный план обслуживания. Для этого установим переключатель на «Единое расписание для всего плана или без расписания» (Single schedule for the entire plan ore no schedule) и нажмем «Изменить…» (Change…) для назначения расписания.

Откроется окно «Свойства расписания задания» . Здесь зададим те параметры, согласно которым должен выполняться план обслуживания и нажмем «ОК» . В моем примере это:

Еще раз убедимся, что расписание задано верно, и нажмем «Далее» (Next) .

Здесь выберем те задачи, которые будет выполнять наш план обслуживания. В моем примере это:

  1. Проверка целостности базы данных (Check Database Integrity);
  2. Резервное копирование базы данных (полное) (The Back Up Database (Full));

Заметьте, что для каждой задачи приводится ее краткое описание в поле снизу. Выбрав необходимые задачи, жмем «Далее» (Next) .

Теперь необходимо задать порядок выполнения задач, используя кнопки «Вверх…» (Move Up) и «Вниз…» (Move Down). Установив порядок, жмем «Далее» (Next) .

Здесь требуется задать параметры для каждой задачи в плане. Первая задача в нашем списке это «Копирование БД (полное)» (Back Up Database (Full)).

Прежде всего необходимо выбрать базы данных для резервного копирования, нажав на кнопку выбора списка «Определенные базы данных» (Select one ore more). Выбрав необходимые для резервного копирования базы данных, нажимаем «ОК» .

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

  1. Если установить переключатель «Создать файл резервной копии для каждой базы данных» (Create a backup file for every database) , то при выполнении задания в выбранной директории будет создаваться несколько файлов резервных копий с именами, соответствующими названиям баз данных. Ну а установка флага «Создавать вложенный каталог для каждой базы данных» (Create a sub-directory for each database) разложит файлы по отдельным папкам. Обратите внимание, что необходимо оставить заполненным расширение файла резервной копии.
  2. Установка флага «Срок действия резервного набора данных истекает» (Backup set will expire) указывает SQL-серверу, когда этот набор может быть перезаписан без явного пропуска проверки на истечение срока.
  3. Для наибольшей надежности, можно установить флаг «Проверять целостность резервной копии» (Verify backup integrity).
  4. Также рекомендую выбрать режим «Сжимать резервные копии» (Compress backup) для экономии дискового пространства, если используемая версия SQL Server поддерживает данную функцию.

Если дисковое пространство ограничено, можно также выбрать один файл для хранения резервной копии, который будет перезаписываться после каждого выполнения плана обслуживания. Для этого установим соответствующий переключатель на «Создать резервную копию баз данных в одном или нескольких файлах» (Back up databases across one ore more files) и указжем соответствующее имя файла (будьте внимательны, файл резервной копии следует задавать с расширением .bak), а также выберем режим «Перезаписать» в случае, если файлы резервной копии существуют (If backup files exist: Overwrite).

Определившись с настройками жмем «Далее» (Next).

Теперь очередь задачи «Проверка целостности базы данных» (Database Check Integrity). Для нее всего лишь необходимо выбрать базу данных. В моем примере это все та же база данных, что и на предыдущем шаге. Определившись с базами, жмем «Далее» (Next).

На следующей странице возможно выбрать директорию, куда будет сохраняться лог выполнения задания, а также указать оператора SQL Server для отправки отчета по электронной почте. Задав параметры, снова жмем «Далее» (Next).

Проверим еще раз все настройки плана обслуживания, и если все верно, нажимаем «Готово» (Finish).

Мастер начнет построение плана обслуживания. Если мастер не обнаружит ошибок, то увидим сообщение об успешном построении плана. В противном случае необходимо устранить ошибки и повторить процедуру снова. Закроем окно, нажав «Закрыть» (Close).

Для запуска выполнения плана обслуживания перейдем в программу «Среда Microsoft SQL Server Management Studio». Здесь, раскрыв вкладку «Планы обслуживания» (Maintenance Plans) увидим наш только что созданный план. Чтобы проверить его работу, кликнем по нему правой кнопкой мыши, и в контекстном меню выберем пункт «Выполнить» (Execute) .

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

А в соответствующих директориях должны появиться файл резервной копии

и файл лога выполнения плана.

Открыв, этот файл, вы должны увидеть примерно следующее:

Если все так, поздравляю! План обслуживания SQL Server создан и работает.

Задача: восстановить базу данных из файла бэкапа.
Шаг за шагом с иллюстрациями рассмотрим как это сделать на примере СУБД 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.

Add a Comment

Ваш адрес email не будет опубликован. Обязательные поля помечены *