Как ускорить восстановление больших баз данных в SQL Server
Все администраторы знают, что восстановление базы данных из резервной копии — процесс не быстрый. Особенно, если база большая.
А что если я скажу, что время восстановления БД можно сократить более чем вдвое при помощи одной простейшей опции?
Давайте проведём эксперимент. Вот база данных размером 100 ГБ, под завязку заполненная данными.
Все данные этой базы лежат в единственном файле.
Я сделаю резервную копию этой базы и затем восстановлю базу из резервной копии. И резервное копирование и восстановление я провожу самым обыкновенным способом без каких-либо хитроумных настроек.
А теперь посмотрите внимательно на выдачу инструкции RESTORE DATABASE:
Сама команда восстановления сообщает, что она выполнялась 3331 секунду — это примерно 55 минут. Но с точки зрения внешнего наблюдателя восстановление базы заняло больше двух часов! Получается, что 55 минут сервер занимался непосредственно восстановлением БД, а ещё час двадцать занимался неизвестно чем.
На самом деле это время сервер потратил на выделение места под восстанавливаемую базу. Сложно поверить, но это заняло в полтора раза больше времени, чем полезный процесс. Вас устраивает, что КПД дисковой подсистемы при восстановлении всего 40%?
Если нет, то давайте зайдём в локальную политику безопасности и предоставим учётной записи, под которой работает SQL Server разрешение «выполнение задач по обслуживанию томов» (в английской версии — «perform volume maintenance tasks»). Чтобы это подействовало необходимо перезапустить службу SQL Server. Теперь сервер сможет гораздо быстрее выделять место на диске для резервных копий, восстановления баз, а также при расширении файлов баз и журналов.
Повторим эксперимент с восстановлением стогигабайтной базы:
Теперь база действительно восстанавливается за 55 минут — это почти в два с половиной раза быстрее, чем при настройках по умолчанию!
А как вы думаете, почему Микрософт не включил эту настройку сразу при установке сервера? Ведь преимущества очевидны! Может быть, тут притаились какие-то побочные эффекты? Ответ на это вопрос вы узнаете на наших курсах. А также вы узнаете о других интересных настройках, которые могут заметно поднять производительность сервера баз данных. Приходите учиться в Специалист — будет интересно! 😉
Подробнее об этом Вы сможете узнать на курсах SQL Server
Заказ добавлен в Корзину.
Для завершения оформления, пожалуйста, перейдите в Корзину!
Microsoft SQL Server: работа с резервными копиями баз данных
Для работы с резервными копиями баз данных Microsoft SQL Server можно воспользоваться web-интерфейсом.
В адресной строке браузера (рекомендуется использовать Internet Explorer версии 7.0 или 8.0; работа в Google Chrome не поддерживается) наберите адрес https://mssqltools.masterhost.ru, и нажмите клавишу «Ввод».
После этого вам будет предложено авторизоваться.
Введите логин к базе данных Microsoft SQL Server, высланный вам письмом при регистрации площадки, и соответствующий пароль к нему. Нажмите «ОК», нажмите кнопку «Продолжить», после чего вы попадёте в интерфейс управления бэкапами.
По умолчанию открывается страница «Restore A Database» (восстановить базу данных).
Восстановление базы данных из резервной копии
Для того, чтобы восстановить резервную копию вашей локальной базы данных Microsoft SQL Server в хостинговую базу данных, нужно сделать следующее:
- Выбрать базу данных, которую хотите восстановить:
- Подтвердить выбор, кликнув кнопку «OK»:
- Выбрать файл с бэкапом базы данных (для того, чтобы выбрать файл с бэкапом, находящийся на вашем локальном компьютере, кликните кнопку «Browse»:
Выберите требуемый файл и кликните кнопку «OK», после чего файл будет загружен на сервер и появится в списке доступных бэкапов. Кликните «OK», чтобы продолжить: 
- Выбрать требуемый резервный набор данных (backup set):
- Восстановить базу данных из бэкапа, кликнув кнопку «Restore»:
И далее «OK»: 
В случае успеха вы получите сообщение «Restore successfully performed»: 
При этом сообщения в секции «Check database options» в большинстве случаев можно игнорировать.
Известные ограничения:
Восстановить хостинговую базу данных из вашей резервной копии можно в том случае, если резервная копия сделана в Microsoft SQL Server 2005, 2008 и 2012 версий.
Восстановление из резервных копий, сделанных в Microsoft SQL Server 7.0 и ниже не поддерживается. Также не поддерживается восстановление из резервных копий, содержащих каталоги полнотекстового поиска Microsoft SQL Server 2000 и 2005 версий.
Резервное копирование базы данных
Для того, чтобы получить резервную копию хостинговой базы данных Microsoft SQL Server, нужно сделать следующее:
- В левой части меню в разделе «Tools» выбрать пункт «Backup Databases», далее выбрать требуемую базу данных:
- Проверить информацию о выбранной базе данных и кликнуть кнопку «OK»:
- Ввести имя и описание резервного набора данных:
- Кликнуть кнопку «Backup» для начала резервного копирования базы данных:
- После резервную копию можно будет скачать на локальный компьютер кликнув соответствующую ссылку:
Либо выбрав в левой части меню в разделе «Tools» пункт «Manage Backup Files»
Обращаем ваше внимание на то, что техническое резервное копирование хостинговых баз данных производится ежесуточно в автоматическом режиме, а данный интерфейс предназначен для того, чтобы в случае необходимости вы могли сделать и скачать актуальную резервную копию самостоятельно.
Известные ограничения:
На наших хостинговых серверах используется Microsoft SQL Server 2012, поэтому сделанные с их помощью резервные копии баз данных вы сможете восстановить только в Microsoft SQL Server 2012. Восстановление в предыдущих версиях Microsoft SQL Server не поддерживается.
В случае необходимости для переноса данных между базами данных Microsoft SQL Server 2008 и MSSQL Server 2005 можно использовать стандартную процедуру экспорта данных в Microsoft SQL Server Management Studio.
Управление файлами резервных копий
Управлять файлами резервных копий можно с помощью пункта «Manage Backup Files», который находится в левой части меню, в разделе «Tools». Здесь вы можете удалить, скачать, или переместить файл с резервной копией из каталога «Backup» в «Restore» и наоборот:
Прочие вопросы по работе с резервными копиями направляйте авторизованной заявкой из личного кабинета, выбрав отдел «Техподдержка Windows-хостинга, Windows VPS и Windows DS».
Если у вас остались вопросы — посетите раздел FAQ.
Резервное копирование и восстановление сервера базы данных
Все сведения о программе ESET PROTECT и соответствующие настройки хранятся в базе данных. Рекомендуем регулярно создавать резервную копию базы данных во избежание потери данных. Резервную копию можно использовать позже при перемещении программы ESET PROTECT на новый сервер. См. соответствующий раздел из приведённых ниже в отношении своей базы данных:
Имена баз данных и файлов журналов не изменяются при изменении наименования продукта с ESET Remote Administrator на ESET Security Management Center или ESET PROTECT.
Примеры резервного копирования MS SQL
Чтобы создать резервную копию базы данных MS SQL в файле, следуйте примерам, показанным ниже:
Эти примеры предназначены для использования с настройками по умолчанию (например, с именем базы данных и параметрами подключения базы данных по умолчанию). Любые изменения настроек, установленных по умолчанию, требуют соответствующей корректировки сценария резервного копирования.
Для выполнения приведенных ниже команд требуется наличие достаточных прав. Если вы не используете учетную запись пользователя локального администратора, вам нужно изменить путь для резервного копирования, например на ‘C:\USERS\PUBLIC\BACKUPFILE’ .
Одноразовое резервное копирование базы данных
Выполните следующую команду в командной строке Windows, чтобы создать резервную копию в файле с именем BACKUPFILE :
SQLCMD -S HOST\ERASQL -Q «BACKUP DATABASE ERA_DB TO DISK = N’C:\USERS\ADMINISTRATOR\DESKTOP\BACKUPFILE'»
В этом примере статус HOST означает IP-адрес или имя хоста, а ERASQL — имя экземпляра MS SQL Server. Вы можете установить сервер ESET PROTECT на экземпляр SQL с пользовательским именем (при использовании базы данных MS SQL). Соответственно измените скрипты резервного копирования в этом сценарии.
Регулярное резервное копирование базы данных с помощью SQL-сценария
Выберите один из следующих SQL-сценариев:
a) создание регулярных резервных копий и их хранение на основе даты создания:
2. SQLCMD.EXE -S HOST\ERASQL -d ERA_DB -E -Q «BACKUP DATABASE ERA_DB TO DISK = N’C:\USERS\ADMINISTRATOR\DESKTOP\BACKUPFILE’
WITH NOFORMAT,INIT, NAME = N’ERA_DB’, SKIP, NOREWIND, NOUNLOAD, STOP_ON_ERROR, CHECKSUM, STATS=10″
3. REN BACKUPFILE BACKUPFILE-[%DATE:~10,4%%DATE:~4,2%%DATE:~7,2%_T%TIME:~0,2%%TIME:~3,2%].bac
b) присоединение резервной копии к одному файлу:
2. SQLCMD.EXE -S HOST\ERASQL -d ERA_DB -E -Q «BACKUP DATABASE ERA_DB TO DISK = N’C:\USERS\ADMINISTRATOR\DESKTOP\BACKUPFILE’
WITH NOFORMAT, NOINIT, NAME = N’ERA_DB’, SKIP, NOREWIND, NOUNLOAD, STOP_ON_ERROR, CHECKSUM, STATS=10″
Восстановление MS SQL
Чтобы восстановить базу данных MS SQL из файла, следуйте примерам, показанным ниже:
SQLCMD.EXE -S HOST\ERASQL -d ERA_DB -E -Q «RESTORE DATABASE ERA_DB FROM DISK = N’C:\USERS\ADMINISTRATOR\DESKTOP\BACKUPFILE'»
Резервное копирование MySQL
Чтобы создать резервную копию базы данных MySQL в файле, следуйте примерам, показанным ниже.
mysqldump —host HOST —disable-keys —extended-insert —routines -u ROOTLOGIN -p ERADBNAME > BACKUPFILE
В этом примере статус HOST означает IP-адрес или имя хоста MySQL Server, ROOTLOGIN — учетную запись root сервера MySQL Server, а статус ERADBNAME — имя базы данных ESET PROTECT.
Восстановление MySQL
Чтобы восстановить базу данных MySQL из файла, следуйте примеру, показанному ниже:
mysql —host HOST -u ROOTLOGIN -p ERADBNAME < BACKUPFILE
Дополнительные сведения о резервном копировании Microsoft SQL Server см. на веб-сайте Microsoft TechNet. Дополнительные сведения о резервном копировании MySQL Server см. на веб-сайте документации MySQL.
Инструкция по созданию/восстановлению резервных копий баз данных
Резервные копии рабочих баз данных, т.н. backup (бэкап), рекомендуется делать регулярно (на случай аппаратных и/или программных сбоев на сервере) с использованием планировщиков заданий, процесс настройки которых подробно описан в инструкциях по установке сетевых версий программ Альта-Софт (включая «Альта-ГТД»). Кроме того, в самой программе «Альта-ГТД» имеется функция, позволяющая создать резервную копию рабочей базы – см. меню Сервис/Бэкап БД SQL.
В процессе создания резервной копии «живая» база данных выгружается в файл на диск компьютера (на котором установлен SQL Server). В результате получается целостный файл, из которого в любой момент можно гарантированно восстановить базу данных до состояния, в котором она находилась на момент создания резервной копии. Причем по заверениям корпорации Microsoft резервную копию можно создавать даже во время активной работы пользователей с базой, однако «при прочих равных» мы рекомендуем делать копии, когда с базой никто не работает (хотя бы чтобы понимать в каком состоянии она находилась на момент копирования).
Перенос базы данных с одного SQL-сервера на другой также необходимо делать с помощью операций:
- Создание резервной копии базы данных (backup)
- Восстановление базы данных из резервной копии (restore)
Создание резервной копии базы данных (backup)
- Запустить утилиту SQL Server Management Studio (из состава MS SQL Server).
- Подключиться к серверу под учетной записью администратора или владельца БД (можно использовать встроенную учетную запись «sa», пароль для которой задавался при установке SQL Server, либо выбрать вариант «Проверка подлинности Windows» в случае если текущий пользователь сеанса Windows обладает правами администратора в SQL Server, а также использовать любую другую учетную запись SQL Server или Windows, которая включена в роль «db_owner» копируемой базы):
- Нажать правой кнопкой мыши на имени копируемой БД (в разделе «Базы данных») и выбрать меню «Задачи/Создать резервную копию»:
- В разделе «Назначение» указать путь и имя файла (путь всегда задается для компьютера, где установлен сам SQL Server!), в который будет выгружена база данных, для чего использовать кнопки «Удалить» и «Добавить» до тех пор, пока в поле «Создать резервную копию на» не будет отображен ровно один желаемый путь:
- На странице «Параметры» установить переключатель «Создать резервную копию в новом наборе носителей…» и галочку «Проверить резервную копию после завершения»:
- Нажать кнопку «ОК».
Примечание. Резервные копии баз данных SQL Server обычно хорошо сжимаются архиваторами (WinRAR, WinZIP и т.п.), поэтому размер полученного файла (*.bak) можно сильно уменьшить с их помощью.
Восстановление базы данных из резервной копии (restore)
- Запустить утилиту SQL Server Management Studio (из состава MS SQL Server).
- Подключиться к серверу под учетной записью администратора или владельца БД (можно использовать встроенную учетную запись «sa», пароль для которой задавался при установке SQL Server, либо выбрать вариант «Проверка подлинности Windows» в случае если текущий пользователь сеанса Windows обладает правами администратора в SQL Server, а также использовать любую другую учетную запись SQL Server или Windows, которая включена в роль «db_owner» восстанавливаемой базы при наличии таковой или серверную роль «dbcreator»):
- Нажать правой кнопкой мыши на разделе «Базы данных» и выбрать меню «Восстановить базу данных»:
- На странице «Общие» выполнить следующие действия:
- В поле «В базу данных» ввести имя для восстанавливаемой базы (если будет указано имя существующей базы, то это эквивалентно тому, что сначала полностью удалить существующую базу и затем восстановить из резервной копии новую базу, т.е. все данные существующей базы будут утеряны!);
- Установить переключатель «С устройства» и указать путь к файлу резервной копии, нажав кнопку «…»;
- Установить галочку «Восстановить» в нужной строке (которых может быть несколько, если один файл *.bak содержит несколько резервных копий базы):
- На странице «Параметры» установить галочку «Перезаписать существующую базу данных» и проверить пути в списке «Восстановить файлы базы данных как» (должны указывать на существующую папку на SQL-сервере, к которой предоставлены права на запись – пути по умолчанию обычно должны заканчиваться папкой DATA, а не просто MSSQL):
- Нажать кнопку «ОК».
Примечание. После восстановления базы данных на другой версии SQL Server (например, при переносе с SQL2005 на SQL2008) рекомендуется в свойствах базы данных переключить параметр «Уровень совместимости» на последнюю версию (см. страницу «Параметры» в свойствах БД).
Кроме того, после переноса базы на любой новый SQL-сервер придется заново настроить авторизацию и регулярное резервное копирование в соответствии с инструкцией по установке сетевой версии соответствующей программы.
Для переноса большого количества «имен входа» (логинов) с их паролями на новый сервер можно воспользоваться способом, описанным в статье базы знаний Microsoft. Однако, следует иметь в виду, что данный способ не переносит серверные права логинов (принадлежность «серверным ролям», право на «просмотр состояния сервера» и т.п.), а также не подходит для переноса с SQL 2012 на более ранние версии SQL Server.