ГлавнаяРазноеНастройка резервного копирования ms sql 2018

Часть 1. Настройка резервного копирования ms sql. Ошибки и заблуждения. Настройка резервного копирования ms sql 2018


Автоматизация настройки резервного копирования MS SQL с помощью .NET приложения

Я долго созревал, чтобы написать данную статью и выложить свое приложение. Надеюсь вам будет интересно.
О чем данная статья
В ней описан тот способ, как с помощью разработанного мною .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, [FILE_SIZE_MB] = convert(decimal(12,2),round(a.size/128.000,2)), [SPACE_USED_MB] = convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)), [FREE_SPACE_MB] = 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.

habr.com

Техподдержка: Настройка регулярного резервного копирования БД MS SQL Server

Рекомендуется настроить регулярное резервное копирование базы данных (на случай аппаратных или программных сбоев), причем лучше всего с сохранением резервных копий за последние несколько дней, например семь (за последнюю неделю).

Для этого можно использовать либо встроенный в 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/Задания» нажать правой кнопкой мыши на интересующем задании и в контекстном меню выбрать пункт «Запустить задание на шаге», в появившемся окне выбрать первый шаг данного задания и нажать «ОК». Далее появится окно отображающее ход выполнения задания. Если выполнение задания закончится с ошибкой, то подробное описание ошибки можно увидеть вызвав пункт «Просмотр журнала» того же контекстного меню.

www.alta.ru

Часть 1. Настройка резервного копирования ms sql. Ошибки и заблуждения

Настройка резервного копирования ms sql, это одна из первых задач которая приходит в голову, когда задумываешься о последствиях утраты рабочих баз данных. Зачастую у многих администраторов именно с решения этой задачи начинается первое знакомство с SQL Server Management Studio (SSMS) и языком Transact SQL (T-SQL). Этой публикацией мы решили начать цикл статей, посвященных типовым ошибкам и заблуждениям. Мы не будем описывать как по шагам выполнить настройку резервного копирования, а остановимся на тех важных моментах, которым обычно не уделяется должного внимания, что в итоге сводит на нет всю настройку резервного копирования ms sql.

Заблуждение: Мне достаточно лишь настроить бэкапы …

Так обычно думают те, кто впервые сталкивается с этим вопросом. Однако, все далеко не так просто, как кажется на первый взгляд. Приведу минимальный перечень вопросов, с которыми придётся разобраться:

  1. Какова ваша стратегия резервного копирования?
  2. Что такое модель восстановления баз данных и какую модель выбрать?
  3. Куда лучше складывать файлы бэкапов и как их проверять?
  4. За какой промежуток времени хранить резервные копии?
  5. Как настроить регулярную проверку целостности БД на SQL Server?
  6. Как настроить email-уведомления об ошибках?

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

Ошибка: Выбирается неверная стратегия резервного копирования и восстановления

Стратегия резервного копирования и восстановления определяется Моделью восстановления базы данных, частотой создания резервных копий (полной, разностной и журнала транзакций) и глубиной хранения резервных копий. Стратегия фактически определяет за какой максимальный период времени вы потеряете данные в случае сбоя и как долго будет выполняться восстановление данных. Другими словами, планируя стратегию, необходимо руководствоваться важностью информации и серьезностью последствий при её утере в случае сбоя, а также приемлемой скоростью восстановления после сбоя. Очень часто, чтобы не заморачиваться, выбирается самая простая стратегия, в то время как потеря данных, даже за небольшой промежуток времени, чревата серьезными последствиями для бизнеса.

Необходимо заметить, что SQL Server позволяет восстанавливать базы данных не только на тот момент, когда была создана её полная резервная копия (бэкап), но и на любой другой момент времени. Для этого в SQL Server есть механизм журнала транзакций. Суть механизма проста – все запросы на изменение данных, которые получает SQL Server, регистрируются в журнале транзакций. При этом журнал должен периодически бэкапиться. Делается это быстро и незаметно для пользователей. В случае сбоя восстанавливается вся цепочка по очереди: вначале полная резервная копия базы данных, а затем резервные копии журнала. В итоге база данных будет восстановлена на момент бэкапа последнего журнала транзакций.

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

Если вы настраиваете политику обслуживания с помощью QMB и не знаете какую политику выбрать, то советуем выбрать политику с Полной моделью восстановления.

Ошибка: Полная модель восстановления без резервной копии журнала транзакций

Часто приходится сталкиваться с ошибкой, когда для базы данных установлена Полная модель восстановления (значение по умолчанию), в то время как бэкап журнала транзакций не создается вовсе. В таком случае теряется всё преимущество Полной модели восстановления и в случае сбоя не удастся восстановить данные на актуальный момент времени т.к. отсутствует бэкап журнала транзакций, а сам файл журнал транзакций (с расширением ldf) в такой ситуации ничем не поможет. Кроме этого, если не бэкапить журнал транзакций, то информация в нём будет постоянно накапливаться и файл журнала будет постоянно расти. Чтобы место в файле освобождалось для последующих транзакций и файл не рос, необходимо регулярно выполнять резервное копирование журнала транзакций. Частота операции определяется стратегией резервного копирования. Фактически это и есть тот период времени за который позволительно потерять данные.

Чтобы минимизировать ошибки при создании политики обслуживания из шаблона QMB автоматически установит нужную модель баз данных.

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

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

Ошибка: Не настроены уведомления администратора

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

Нужно отметить один неприятный момент: уведомления, которые отправляет штатный компонент DataBase Mail не содержат информации об ошибке. Поэтому после его получения, для того чтобы понять серьезность ошибки, администратору приходится каждый раз смотреть логи SQL Server. К сожалению, со временем многие администраторы перестают обращать внимания на ошибки и своевременно реагировать на них. По закону жанра именно в такой момент база летит в тартарары.

QMB не требует настройки компонента DataBase Mail на SQL Server и умеет отправлять уведомления через встроенный или ваш SMTP аккаунт. Кроме этого, уведомления QMB содержат текст ошибки так как будто инструкция была выполнена в SSMS. Соответственно администратор не тратит время на поиск текста ошибки.

Продолжение следует…

qmbsql.ru

Резервное копирование MS SQL Server

Резервное копирование MS SQL с программой Handy Backup позволяет создавать резервные копии баз данных и настроек MS SQL Server. Инструменты программы позволяют эффективно, безопасно и быстро копировать данные СУБД MS SQL.

Handy Backup предоставляет специализированный плагин для создания резервных копий MSSQL и автоматического выбора всех необходимых данных Microsoft SQL Server. Ниже описаны основные возможности и функции.

Точность и надёжность

Плагин MSSQL непосредственно обращается к серверу СУБД, не прибегая к помощи ODBC-драйвера. Это позволяет для MS SQL резервное копирование баз данных со всеми их структурами и элементами в исходном формате, без упрощений или изменений.

Резервное копирование SQL баз в "горячем" режиме

Программа может выполнять горячее резервое копирование баз SQL, т.е., копировать открытые базы и таблицы, без остановки любых служб и приложений СУБД MS SQL, а также без отключения пользователей от сервера, что экономит время.

Поддержка всех версий и конфигураций

Вы можете создавать резервную копию SQL 2016, 2014, 2012 (R2), 2008 (R2), 2005, 2000, в 32- и 64-разрядной архитектуре. Для настройки резервного копирования баз данных SQL вам достаточно выбрать плагин MSSQL, и программа сама определит используемую версию.

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

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

Рекомендуемое решения для автоматического резервного копирования данных MS SQL

Версия 7.17.0 от 14 сентября 2018. 164 MBПрограмма резервного копирования Handy Backup. 7400 RUB за лицензию

Копируйте данные MS SQL Server и любые другие серверные данные на одном компьютере, используя решение Handy Backup Office Expert.

В решении Handy Backup Office Expert возможность резервного копирования MS SQL доступна "из коробки". Если вы хотите копировать базы данных MSSQL в версиях Standard или Professional, функцию можно приобрести в качестве дополнительного плагина.Для работы в сетевой архитектуре баз данных мы предлагаем решение Handy Backup Server Network.

Чтобы создать резервную копию данных Microsoft SQL Server, вам необходимо выбрать "MSSQL" в источниках данных. С его помощью вы автоматически выберете все нужные данные с сервера SQL и настроите задачу резервного копирования.

Внимание: для автоматического создания резервной копии вам нужно разрешить режим идентификации через ОС (Windows Authentication Mode) для сервера SQL, а затем запустить программу как пользователь с правами администратора MS SQL.

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

Плагин MSSQL для Handy Backup эффективно работает с MS SQL Server 2016, предоставляя автоматический бэкап MSSQL 2016 без ограничений по формату данных или изменений в функциональности программы.

Смотрите также:

Cnet Editor’s Rating: Outstanding

www.handybackup.ru

Настройка расписания и плана резервного копирования

Рекомендуем использовать Visual Studio 2017

Эта документация перемещена в архив и не поддерживается.

Visual Studio 2013

Если вы занимаетесь администрированием систем, вы уже знаете, почему наличие надежного набора резервных копий принципиально важно. Никто не хотел бы оказаться на месте администратора вышедшего из строя сервера, если нет полного набора резервных копий. К счастью, резервное копирование данных TFS можно выполнить с помощью средства «Плановые резервные копии» на консоли администрирования. Если регулярно создавать резервные копии этих баз данных, можно снизить риск потери данных или производительности из-за сбоя оборудования или других непредвиденных событий. В отличие от предыдущих методов, это средство также создает резервные копии баз данных SharePoint, используемых TFS, если TFS настроен для использования SharePoint.

Важно

Если используется выпуск SQL Server Enterprise или Datacenter и требуется восстановить базы данных конфигурации и коллекции до выпуска SQL Server Standard, перед выполнением резервного копирования необходимо отключить сжатие SQL Server, выполнив действия, описанные в следующей статье базы знаний Майкрософт.

  1. Если вы не являетесь администратором TFS, членом группы «Системный администратор SQL Server» и (если развертывание использует продукты SharePoint), членом группы «Администраторы фермы», получите соответствующие разрешения.

    Кроме того, разрешение «Резервное копирование и создание планов обслуживания SQL Server» для учетной записи службы TFS (TFSService) должно иметь значение «Разрешить» на каждом экземпляре сервера SQL Server, где размещены копируемые базы данных, а также эта учетная запись должна иметь полный доступ к сетевой папке, папке или запоминающему устройству, где будут храниться резервные копии.

  2. Откройте консоль администрирования TFS и на странице Плановые резервные копии запустите мастер для создания графика резервного копирования.

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

  4. Если на сервере настроена поддержка SMTP, можно получать оповещения об определенных событиях по электронной почте. В противном случае все варианты затемняются.

  5. Можно выбрать один из двух графиков по умолчанию или создать собственный пользовательский график.

  6. Выполните указания мастера. Если в развертывании используются отчеты, будет предложено ввести пароль, чтобы создать ключ шифрования для отчетов.

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

    Примечание

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

Ответ. Да. Можно вручную создавать планы резервного копирования. Узнать об этом подробнее можно здесь.

Ответ. Это делается с помощью средства «Восстановить», однако конкретные действия зависят от того, куда вам нужно восстановить данные: на новый сервер или на старый сервер.

technet.microsoft.com

Резервное копирование базы данных MS SQL 2008 R2

«Кто владеет информацией - тот владеет миром» - Майер Амшель Ротшильд

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

Для того, чтобы обеспечить сохранность информации в используемых базах данных, а также снизить время для восстановления работоспособности, необходимо производить периодическое резервное копирование SQL-серверов.

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

Решение:

  1. Открываем Microsoft SQL Server Management Studio. В навигационном меню справа открываем вкладку "Управление". Там видим вкладку "Планы обслуживания". Нажимаем правой кнопкой мыши -> "Создать план обслуживания" и даем имя нашему плану (Рис.1):

    Рис.1 Создание нового плана обслуживания.

    2.  На панели элементов добавляем задачу "Резервное копирование базы данных" (Рис.2):

    Рис.2 Добавление задачи "Резервное копирование базы данных".

    3.  На созданной задаче нажимаем правой кнопкой мыши -> "Изменить" (Рис.3):

    Рис.3

    4.  В окне свойствах задачи выбираем тип резервной копии (в моем случае полный), выбираем нужную базу данных (у меня ka_cons), каталог для бэкапов, возможность проверки резервных копий на целостность и варианты сжатия оных (Рис.4-6):

        Рис.4 Тип бэкапа - полный.       Рис.5 Выбор базы данных для бэкапа.       Рис.6 Определение каталога для бэкапов, проверки целостности и степени сжатия.

    5.  На панели настройки плана обслуживания справа. нажимаем на кнопку "Расписание" (Рис.7):

    Рис.7

    6.  Настраиваем нужное нам расписание и нажимаем "Ок" (Рис.8):

    Рис.8 Настройка расписания резервного копирования.

    7.  Сохраняем наш план обслуживания (Рис.9):

       Рис.9 Сохранение плана обслуживания.

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

Успехов!

sergeykovarsky.blogspot.com

Создание резервной копии базы данных в MS SQL Server 2012

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

 

 

 

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

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

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

Запускаем утилиту «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) раскрываем вкладку «Базы данных» (Databases), кликаем правой кнопкой мыши по базе, из которой необходимо выгрузить данные и в контекстном меню выбираем «Задачи» (Tasks) — «Создать резервную копию…» (Back up…).

Откроется окно настройки свойств резервного копирования базы данных. Здесь можно выбрать:

  • Базу данных (Database) для которой создается резервная копия — выбрана база данных с которой мы начинали действия.
  • Тип резервной копии (Backup type) — по умолчанию полная (Full). Подробно о различных типах резервных копий читайте здесь.
  • Установить флаг «Только резервная копия» (Copy-only Backup) — признак того, что создаваемая резервная копия будет изолирована от обычной последовательности резервных копий SQL Server.
  • Компоненты резервного копирования (Backup component) — всю базу данных (Database) или только выбранные файлы (Files and filegroups).
  • Срок действия резервного набора данных (Backup set will expire) — период, после которого эта резервная копия может быть перезаписана без явного пропуска проверки на истечение срока. Если выбрано через 0 дн. (After: 0 days), файлы резервной копии не будут перезаписываться.
  • Назначение (Destination) — путь к файлу резервной копии на выбранном диске (Disk).

Для того, чтобы изменить или добавить место расположения и имя файла резервной копии или устройства резервного копирования, нажмем «Добавить» (Add…), в окне выбора места расположения резервной копии выберем каталог и имя файла, и закроем все окна нажав «ОК». Для удаления назначения резервного копирования, выделим его в списке и воспользуемся кнопкой «Удалить» (Remove).

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

Здесь установим флаг «Проверить резервную копию после завершения» (Verify backup when finished) для обеспечения больше надежности и установим параметр «Сжимать резервные копии» (Compress backup) для экономии дискового пространства, после чего нажмем «ОК» для запуска процесса создания файла резервной копии.

Если резервное копирование выбранной базы данных завершилось успешно, увидим соответствующее сообщение.

Ну а в указанном ранее каталоге найдем непосредственно сам файл резервной копии выбранной базы данных.

О восстановлении базы данных из резервной копии, можно прочитать в статье «Восстановление базы данных из резервной копии в MS SQL Server 2012»

 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)

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

Смотрите также:

  • Добавление базы данных в Microsoft SQL Server 2012

    Ниже приведена пошаговая инструкция, показывающая как добавить новую базу данных в Microsoft SQLServer 2012 (в более старых редакциях, например в Microsoft SQL Server 2008 R2, набор действий аналогичен).         Запускаем…

  • Отсоединение и Присоединение баз данных в MS SQL Server 2012

    В ситуации когда необходимо перенести базу данных SQL с одного экземпляра MS SQL Server на другой, или изменить каталог хранения файлов базы данных, помогут операции отсоединения (Detach) и присоединения (Attach) баз данных MS SQL Server.…

tavalik.ru