Как настроить автоматический бэкап базы данных ms sql server

Планирование и автоматизация резервного копирования баз данных SQL Server в SQL Server Express

В этой статье описывается, как использовать скрипт Transact-SQL и планировщик заданий Windows для автоматизации резервного копирования баз данных SQL Server Express по расписанию.

Оригинальная версия продукта: SQL Server
Оригинальный номер базы знаний: 2019698

Аннотация

Выпуски SQL Server Express не предлагают способ планирования заданий или планов обслуживания, так как компонент «Агент SQL Server» не включен в эти выпуски. Поэтому при использовании этих выпусков необходимо использовать другой подход к резервному копированию баз данных.

В настоящий момент SQL Server Express могут создавать резервные копии своих баз данных с помощью одного из следующих методов:

Используйте SQL Server Management Studio или Azure Data Studio. Дополнительные сведения об использовании этих средств для резервного копирования базы данных можно получить по следующим ссылкам:

• Создание полной резервной копии базы данных
• Руководство: резервное копирование и восстановление баз данных с помощью Azure Data Studio
• Используйте скрипт Transact-SQL, использующий семейство команд BACKUP DATABASE. Дополнительные сведения см. в разделе BACKUP (Transact-SQL).

В этой статье описывается, как использовать скрипт Transact-SQL вместе с планировщиком заданий для автоматизации резервного копирования баз данных SQL Server Express по расписанию.

Это относится только к выпускам SQL Server Express, а не к SQL Server Express LocalDB.

Дополнительные сведения

Для резервного копирования баз данных SQL Server с помощью планировщика заданий Windows необходимо выполнить следующие четыре шага:

Шаг А. Создайте хранимую процедуру для резервного копирования баз данных.

Подключитесь к экземпляру SQL Express и создайте хранимую процедуру sp_BackupDatabases в базе данных master с помощью скрипта в следующем расположении:

Шаг Б. Скачивание средства SQLCMD (если применимо).

Служебная sqlcmd программа позволяет вводить инструкции Transact-SQL, системные процедуры и файлы скриптов. В SQL Server 2014 и более ранних версиях служебная программа поставляется в составе продукта. Начиная с SQL Server 2016, sqlcmd служебная программа предлагается в виде отдельного скачивания. Дополнительные сведения см. в разделе Служебная программа sqlcmd .

Шаг В. Создайте пакетный файл с помощью текстового редактора.

В текстовом редакторе создайте пакетный файл с именем Sqlbackup.bat, а затем скопируйте текст из одного из следующих примеров в этот файл в зависимости от сценария:

• Все приведенные ниже сценарии используют D:\SQLBackups в качестве заполнителя. Скрипт необходимо настроить в соответствии с нужным диском и расположением папки резервного копирования в вашей среде.
• Если вы используете проверку подлинности SQL, убедитесь, что доступ к папке ограничен авторизованными пользователями, так как пароли хранятся в виде открытого текста.

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

Пример 1. Полные резервные копии всех баз данных в локальном именованном экземпляре SQLEXPRESS с использованием проверки подлинности Windows.

 // Sqlbackup.bat sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='F'" 

Пример 2. Разностные резервные копии всех баз данных в локальном именованном экземпляре SQLEXPRESS с использованием SQLLogin и пароля.

 // Sqlbackup.bat sqlcmd -U -P -S .\SQLEXPRESS -Q "EXEC sp_BackupDatabases @backupLocation ='D:\SQLBackups', @BackupType='D'" 

SQLLogin должен иметь по крайней мере роль оператора резервного копирования в SQL Server.

Пример 3. Резервное копирование всех баз данных в локальном именованном экземпляре SQLEXPRESS с использованием проверки подлинности Windows

 // Sqlbackup.bat sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\',@backupType='L'" 

Пример 4. Полные резервные копии базы данных USERDB в локальном именованном экземпляре SQLEXPRESS с использованием проверки подлинности Windows

 // Sqlbackup.bat sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @databaseName='USERDB', @backupType='F'" 

Аналогичным образом можно создать разностную резервную копию USERDB, вставив «D» для параметра @backupType и резервную копию журнала USERDB, вставив значение «L» для параметра @backupType .

Шаг Г. Запланируйте задание с помощью планировщика заданий Windows для выполнения пакетного файла, созданного на шаге Б. Для этого выполните следующие действия:

1. На компьютере под управлением SQL Server Express нажмите кнопку Пуск, а затем в текстовом поле введите планировщик задачи.
2. В разделе Лучшее соответствие щелкните Планировщик задач , чтобы запустить его.
3. В планировщике задач щелкните правой кнопкой мыши библиотеку расписания задач и выберите Создать простую задачу. .
4. Введите имя новой задачи (например, SQLBackup) и нажмите кнопку Далее.
5. Выберите Ежедневно для триггера задачи и нажмите кнопку Далее.
6. Задайте для повторения значение один день и нажмите кнопку Далее.
7. Выберите Запустить программу в качестве действия и нажмите кнопку Далее.
8. Нажмите кнопку Обзор, перейдите к созданному на шаге С пакетному файлу и нажмите кнопку Открыть.
9. Установите флажок «Открыть диалоговое окно Свойства» для этой задачи при нажатии кнопки Готово.
10. Откройте вкладку «Общие».
    1. Просмотрите параметры безопасности и убедитесь, что для учетной записи пользователя, выполняя задачу ( в списке При выполнении задачи укажите следующую учетную запись пользователя:) Учетная запись должна иметь по крайней мере разрешения на чтение и выполнение для запуска служебной программы sqlcmd. Дополнительно.
       • При использовании проверка подлинности Windows в пакетном файле убедитесь, что владелец задачи имеет разрешения на резервное копирование SQL.
       • При использовании проверки подлинности SQL в пакетном файле пользователь SQL должен иметь необходимые разрешения для резервного копирования SQL.
    2. Настройте другие параметры в соответствии с вашими требованиями.

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

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

• Служба планировщика задач должна быть запущена во время запланированного запуска задания. Рекомендуется задать для этой службы тип запуска автоматически. Это гарантирует, что служба будет запущена при перезапуске.
• На диске, на который записываются резервные копии, должно быть много места. Рекомендуется регулярно очищать старые файлы в папке Backup, чтобы не иссякнуло свободное место на диске. Скрипт не содержит логику очистки старых файлов.

Резервное копирование и восстановление базы данных в MS SQL Server

26.02.2020

insci

SQL Server

комментариев 16

В этой статье мы рассмотрим, как настроить резервное копирование баз данных в Microsoft SQL Server, покажем, как восстановить базу данных из резервной копии с помощью SQL Server Management Studio и Transact-SQL. Первая часть статьи посвящена теоретическим аспектам резервного копирование в SQL, во второй на примере мы покажем, как настроить регулярное резервное копирование базы данных MS SQL с помощью плана обслуживания и восстановить базу из резервной копии на примере установленного Microsoft SQL Server 2019.

Требования к плану резервного копирования баз данных SQL Server устанавливает бизнес, учитывая несколько критериев:

Допустимый объём потерянных данных (за последний день/час/минуту/секунду);

Следует понимать, что с помощью механизмов резервного копирования невозможно добиться резервирования данных в реальном времени. Для этой цели используются другие технологии высокой доступности SQL Server – группы доступности Always On, зеркалирование баз данных или репликация.

Типы резервного копирования SQL Server

Полное (Full Backup)

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

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

Если модель восстановления базы SQL данных установлена как “Полная”, то при восстановлении бекапа вы можете указать параметр “STOPAT”, где указывается время (до секунды) на котором нужно остановить восстановление данных. Например, сотрудник внёс некорректные данные в 14:46:07, с помощью параметра STOPAT вы можете восстановить данные на момент 14:46:06

Дифференциальное

Дифференциальное или разностное резервное копирование — это копирование только тех данных, которые появились с момента последней полной резервной копии.

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

Обычно при использовании разностного резервного копирования используют план по типу “полное раз в N дней, дифференциальное каждые N часов”. Если ежедневный оборот данных достаточно высокий, то данный тип резервных копий может быть неудобен в применении, так как копии будут весить довольно много.

Например, если полная резервная копия весит 300 GB, а дифференциальная спустя час работы 5 GB, то спустя сутки это будет 120 GB, что делает использование данного типа копий нерациональным.

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

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

Восстанавливая журнал транзакций, вы также можете указать параметр STOPAT, как и в восстановлении полной резервной копии.

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

Tail-Log

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

Tail-Log бекап рекомендуется делать перед восстановлением копий журнала транзакций, чтобы не потерять транзакции между последним бекапом и текущим моментом времени.

Copy-only

Этот вид бекапа не может служить “базой” для дифференциальных резервных копий и для копий журнала транзакций. Copy-only бекап не нарушает текущую цепочку резервных копий (полный-> дифференциальный или полный -> копии журналов транзакций) и используется только в том случае, если вам нужно снять полную резервную копию, не задевая текущую цепочку бекапов.

За исключением этих нюансов – ничем не отличается от обычной полной копии.

Частичная резервная копия

Partial backup этот тип резервной копии используется для того, чтобы снять копии с read-only файловых групп. На практике используется редко.

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

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

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

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

Простая модель восстановления

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

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

При использовании этой модели восстановления, следующий функционал SQL Server недоступен:

• Доставка журналов транзакций
• Always On
• Point-In-Time восстановление
• Резервные копии журнала транзакций

Полная модель восстановления

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

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

Эта модель нуждается в обслуживании журналов транзакций (регулярные резервные копии), иначе журналы займут всё дисковое пространство.

Восстановление с неполным протоколированием (bulk logged)

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

• SELECT INTO
• BULK INSERT и BCP
• INSERT INTO SELECT
• Операции с индексами (CREATE INDEX, ALTER INDEX REBUILD, DROP INDEX)

В остальном эта модель работает аналогично полной модели восстановления.

Настройка резервного копирования SQL Server с помощью плана обслуживания

Планы обслуживания SQL Server это самый распространенный способ настройки регулярного резервного копирования.

Рассмотрим настройку резервного базы данных на SQL Server копирования по плану:

• Полная резервная копия каждые 24 часа
• Копия журнала транзакций – каждые 30 минут

В SSMS (SQL Server Management Studio) перейдите в раздел Management -> Maintenance Planes и запустите -> мастер создания плана обслуживания (Maintenance Plan Wizard).

Укажите имя плана и выберите режим “Separate schedules for each task”.

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

• Back Up Database (Full)
• Back Up Database (Transaction Log)

Используйте следующую последовательность операций:

Выберите базу данных SQL Server, которую нужно бэкапить и выберите расписание.

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

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

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

Опционально можно указать файл для ведения лога плана обслуживания.

Завершение настройки плана обслуживания SQL Server.

Выполните план обслуживания вручную и проверьте журнал.

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

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

Теперь рассмотрим, как восстановить базы данных SQL Server из резервной копии. Для восстановления базы можно использовать графическую консоль SQL Server Management Studio или язык T-SQL.

Восстановление резервной копии с помощью SQL Server Management Studio

Запустите SSMS, щелкните по разделу Database и выберите пункт Restore Database.

Выберите базу данных. В окне появится список резервных копий, зарегистрированных в SQL Server для этой базы данных.

Для примера, воспользуемся Point-In-Time восстановлением и выберем момент, на который мы хотим восстановить базу данных. Нажмите Timeline.

Выберите опцию “Close existing connections to destination database”, если ваша база данных находится в статус Online

Нажмите ОК. После этого база данных восстановится на выбранный момент времени.

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

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

USE [master]
ALTER DATABASE [TestDatabase2] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
BACKUP LOG [TestDatabase2] TO DISK = N’E:\MSSQL15.NODE2\MSSQL\Backup\TestDatabase2_LogBackup_2020-02-17_15-39-43.bak’ WITH NOFORMAT, NOINIT, NAME = N’TestDatabase2_LogBackup_2020-02-17_15-39-43′, NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY, STATS = 5
RESTORE DATABASE [TestDatabase2] FROM DISK = N’E:\MSSQL15.NODE2\MSSQL\Backup\full.bak’ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [TestDatabase2] FROM DISK = N’E:\MSSQL15.NODE2\MSSQL\Backup\trans.bak’ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [TestDatabase2] FROM DISK = N’E:\MSSQL15.NODE2\MSSQL\Backup\trans.bak’ WITH FILE = 2, NOUNLOAD, STATS = 5, STOPAT = N’2020-02-17T15:38:23′
ALTER DATABASE [TestDatabase2] SET MULTI_USER
GO

В данном случае база данных переводится в SINGLE_USER, но нужно быть аккуратным с этим параметром, так как в некоторых ситуациях вы можете закрыть себе доступ, если кто-то откроет сессию раньше вас.

Дальше выполняется tail-log бекап, затем восстанавливается полный бекап и следом восстанавливаются бекапы журнала транзакций. Обратите внимание на параметр STOPAT, база данных восстановиться на момент 15:38:23

Рекомендации и best practice по резервному копированию SQL Server

• Резервные копии не должны храниться на том же диске, что и ваш SQL Server. Это правило касается любых резервных копий. При выходе из строя основного дискового массива вы должны иметь доступ к вашим резервным копиям. Если позволяют ресурсы, лучше хранить резервные копии сразу на нескольких разрозненных массивах.
• Процесс резервного копирования должен минимально влиять на работу пользователей. Полные резервные копии лучше делать тогда, когда пользовательская активность на сервере минимальна.
• Регулярно проверяйте целостность резервных копий и проводите тестовые восстановления. Вы всегда должны быть уверены, что ваши бекапы валидны и готовы к восстановлению в любое время.
• Заранее рассчитайте время, необходимое для полного восстановления при аварии. Часто в базах хранится критически важная для бизнеса информация, поэтому ваш руководитель должен знать минимальное время, которое потребуется для восстановления после аварии. Если даже вас об этом не спрашивают, лучше заранее уведомить об этом, чтобы в случае аварии не возникло недопонимания.

Предыдущая статья Следующая статья

Бэкап MS SQL Server и восстановление базы из резервной копии

Бэкап MS SQL — важнейший элемент обеспечения информационной безопасности серверов MS SQL. Handy Backup предоставляет пользователям эффективное автоматизированное решение, выполняющее бэкап и восстановление базы SQL.

Попробовать бесплатно

Версия 8.5.0 от 19 сентября 2023. 116 MB
30-дневный полнофункциональный пробный период

Преимущества бэкапа MS SQL Server с Handy Backup

Различные режимы бэкапа

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

Средства безопасности данных

В Handy Backup предусмотрено шифрование бэкапов MS SQL Server встроенным алгоритмом BlowFish и сжатие данных. Кроме того, в корпоративных сетях снятый с MS SQL бэкап базы может быть переслан на FTP-хранилище по защищённому протоколу SFTP или FTPS.

Автоматический бэкап MS SQL по расписанию

Для задачи создания или восстановления бэкапа MS SQL может быть задано точное время запуска копирования по расписанию, а также интервал повторения этой задачи. Можно сделать бэкап MS SQL и по некоторому событию, например, при подключении USB-диска.

Другие функции Handy Backup, упрощающие автоматический бэкап MS SQL

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

• Запуск настроенной программы как службы Windows в «бесшумном» режиме;
• Горячий бэкап MS SQL, позволяющий обойтись без остановки работы СУБД;
• Единый плагин для бэкапа MS SQL 2016, 2012, 2008 и ранних версий базы;
• Отправка отчётов и уведомлений о работе задач администратора по почте.

Рекомендуемое решение
12000 ₽ за лицензию

Handy Backup Office Expert

Handy Backup Office Expert — решениен для небольших предприятий, где базы MS SQL хранятся на одном сервере. Бесплатный пробный период 30 дней!

Настройка бэкапов MS SQL в Handy Backup

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

1. Откройте Handy Backup, создайте новую задачу с помощью кнопки на панели управления или элемента меню.
2. На Шаге 1 мастера новых задач выберите тип задачи – бэкап (резервное копирование) ваших данных.
3. Перейдите к Шагу 2. На левой панели (источники данных) разверните список Database и щёлкните на названии MSSQL.

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

1. Откроется диалог выбора данных. Отметьте “галочками” те базы данных, для которых вы собираетесь делать бэкап MS SQL в создаваемой задаче.
2. Нажмите ОК, вернитесь в окно Шага 2. Выберите другие данные для бэкапа, если это нужно. Нажмите “Далее”.

1. На Шаге 3 выберите хранилище для бэкапа базы MS SQL, на Шаге 4 – другие настройки (дифференциальный бэкап, метки и т.д.).
2. На Шаге 5 укажите параметры сжатия и шифрования данных, если вы хотите сделать бэкап MS SQL Защищённым от случайного доступа.
3. На Шаге 6 для автоматического бэкапа MS SQL задайте расписание. Выберите время запуска задачи, укажите интервал повторения. Вы также можете указать системные события, при которых задача запускается.

1. На Шаге 7 вы можете также указать другие программы, запускаемые до и/или после бэкапа MS SQL Server.

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

1. На финальном шаге дайте вашей задаче имя. Завершите работу мастера новых задач.

Видеоурок: Как создать бэкап MS SQL по расписанию

В видео показан процесс создания автоматического бэкапа базы MS SQL по расписанию в простом и в продвинутом режимах Handy Backup.

Внимание: Видеоурок основыван на предположении, что Handy Backup уже установлен на ваш компьютер. Если это не так, пожалуйста, скачайте и установите программу перед тем как начать обучение.

Попробовать бесплатно

Версия 8.5.0 от 19 сентября 2023. 116 MB
30-дневный полнофункциональный пробный период

Как восстановить базу MS SQL из бэкапа?

1. На Шаге 1 выберите задачу восстановления данных. На следующем шаге откройте ваше хранилище, содержащее файлы бэкапа MS SQL.
2. Дважды щёлкните на файле backup.hbi в каталоге хранения. Откроется новый диалог.
3. Нажимайте кнопку «Далее» до конца работы мастера задач, если вам просто нужно восстановить базу MS SQL на то место, откуда она была скопирована.

1. Если вы хотите перенести базу данных в новое место, создать клон или настроить автоматическую репликацию, то выберите интересующую вас базу и затем нажмите кнопку «Изменить место» в диалоге восстановления.
2. Откроется диалог изменения места восстановления. Выберите конфигурацию базы данных, в которую вы намереваетесь копировать данные из бэкапа MS SQL.
3. Далее продолжайте шаг за шагом создавать задачу восстановления.

Приобретение дополнительного плагина для бэкапа MS SQL

В решениях Handy Backup Standard и Handy Backup Professional плагин MSSQL недоступен, но его можно приобрести к этим версиям дополнительно!

Это решение оправдано, например, в следующих случаях:

• Создание копий устаревших версий базы данных (например, бэкап MS SQL 2008) на одном сервере;
• Бэкап локальных или небольших баз данных для одной рабочей станции (например, бэкап MS SQL Express).

Приобретение плагина MSSQL для одной машины в этих случаях обеспечивает экономию средств при сохранении эффективности работы.

Возможности для хранения и восстановления бэкапа MS SQL

В Handy Backup бэкап БД MS SQL может храниться на множестве современных носителей информации. К числу поддерживаемых хранилищ относятся:

1. Локальные и внешние жёсткие диски, USB устройства;
2. Сетевые диски и папки, устройства NAS, сетевые машины * ;
3. Серверы хранения информации, работающие по протоколам FTP, SFTP и FTPS;
4. Облачные сервисы: Amazon S3, Google Диск, Яндекс.Диск, Dropbox, OneDrive;
5. Любые коммерческие и частные облака, поддерживающие протокол WebDAV.

* Обмен данными с машинами в локальной сети, помимо специализированных интерфейсов и протоколов, поддерживается только сетевыми решением Handy Backup Server Network с установленными на удалённых машинах Сетевыми Агентами!

Вопросы использования плагина MSSQL Backup

Вопрос: Как бэкап MSSQL действует на состояние сервера MS SQL?

Ответ: Вы можете создавать бэкапы и восстанавливать базы данных MS SQL Server как в активном, так и в выключенном состоянии сервера. Все функции Handy Backup для работы с базами данных поддерживают технологии «горячего бэкапа», позволяющие копировать информацию из баз данных без остановки СУБД.

Вопрос: Могу ли я использовать плагин Database для бэкапа данных MS SQL Server?

Ответ: Да, конечно — при условии, что вы установите и зарегистрируете в системе подходящий драйвер ODBC для вашего экземпляра MS SQL Server. При этом некоторые функции, доступные для бэкапа MSSQL с помощью специализированного плагина, будут выполняться существенно медленнее или вообще не смогут работать при использовании универсального плагина Database.

Попробовать бесплатно

Версия 8.5.0 от 19 сентября 2023. 116 MB
30-дневный полнофункциональный пробный период

Handy Backup – мощная, универсальная программа, обеспечивающая бэкап MS SQL и восстановление данных. Попробуйте её прямо сейчас, скачав и установив полную версию на 30-дневный бесплатный пробный период!

Читайте также:

• Бэкап файловой базы 1С
• Резервное копирование Oracle

• PostgreSQL резервное копирование
• Бэкап базы данных MySQL

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

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

Для этого можно использовать либо встроенный в SQL Server планировщик заданий – «SQL Server Agent» (в бесплатную версию не входит), либо стандартный «Планировщик Windows» в сочетании с утилитой SQLCMD.EXE, которая позволяет выполнять запросы к SQL Server из командной строки. В планировщике необходимо создать как минимум семь заданий (по одному на каждый день недели), каждое из которых будет (раз в неделю) заменять один из семи файлов, содержащих соответствующую резервную копию базы данных.

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

• С помощью «Планировщика Windows» (для бесплатной версии)
• С помощью «SQL Server Agent» (в бесплатную версию не входит)

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