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

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

Итоги

SQL Server Express не включает агент SQL Server, поэтому вы не можете планировать задания резервного копирования или планы обслуживания так, как можно использовать в других выпусках SQL Server. Чтобы автоматизировать резервные копии в SQL Server Express, объединить три компонента: sp_BackupDatabases хранимую процедуру, sqlcmd служебную программу командной строки и планировщик задач Windows. В этой статье описаны шаги по настройке запланированного полного и разностного резервного копирования, а также резервного копирования журналов транзакций баз данных SQL Server Express. В нем также объясняется, как убедиться, что запланированное резервное копирование было выполнено.

Эта статья применима только к редакциям SQL Server Express. Он не применяется к SQL Server Express LocalDB.

Почему SQL Server Express не может запланировать резервное копирование с помощью агент SQL Server

агент SQL Server — это компонент, который выполняет запланированные задания и планы обслуживания, и он не включен в SQL Server Express. Полное сравнение того, что включает в себя каждый выпуск, см. в разделе "Выпуски" и поддерживаемые функции SQL Server.

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

служебная программа sqlcmd
SQL Server Management Studio (SSMS)
расширение MSSQL для Visual Studio Code
Скрипт Transact-SQL, использующий семейство команд BACKUP (Transact-SQL)

Чтобы запустить эти резервные копии по расписанию, а не по запросу, используйте планировщик задач Windows в качестве подсистемы планирования, как описано в следующих шагах. Сведения о типах резервного копирования и стратегии см. в статье "Резервное копирование и восстановление баз данных SQL Server".

Prerequisites

Прежде чем начать, убедитесь, что у вас есть:

Запущенный экземпляр SQL Server Express. В примерах в этой статье используется именованный экземпляр .\SQLEXPRESSпо умолчанию.
Членство в предопределённой серверной роли sysadmin на этом экземпляре, которое требуется для создания хранимой процедуры в базе данных master.
Локальная папка или диск с достаточным объемом свободного места для хранения файлов резервной копии. В этих примерах используется D:\SQLBackups.
Разрешение на создание запланированных задач на компьютере, на котором выполняется SQL Server Express.

Шаг 1. Создание хранимой процедуры sp_BackupDatabases

sp_BackupDatabases— это хранимая процедура, предоставляемая Microsoft, которая выполняет соответствующую BACKUP команду для одной базы данных или каждой веб-базы данных в экземпляре. Вы создаете master его один раз в базе данных.

Скачайте скрипт из SQL_Express_Backups.sql и сохраните его локально, например C:\Temp\SQL_Express_Backups.sql. Затем создайте хранимую процедуру с помощью SSMS или sqlcmd.

Создание хранимой процедуры с помощью SSMS

Откройте SSMS и подключитесь к экземпляру SQL Server Express. В поле "Имя сервера" введите .\SQLEXPRESSи нажмите кнопку "Подключить".
> Выберитефайл "Открыть>файл", выберите сохраненный файл SQL_Express_Backups.sql и нажмите кнопку "Открыть".
Убедитесь, что на панели инструментов отображается masterсписок баз данных. Скрипт начинается с USE [master], поэтому он автоматически выбирает нужную базу данных.
Выберите "Выполнить " (или нажмите клавишу F5). В области Сообщения отображается Commands completed successfully.

Создание хранимой процедуры с помощью sqlcmd

В командной строке выполните следующую команду:

sqlcmd -S .\SQLEXPRESS -E -i "C:\Temp\SQL_Express_Backups.sql"

Убедитесь, что хранимая процедура создана

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

SELECT name, create_date
FROM master.sys.procedures
WHERE name = 'sp_BackupDatabases';

параметры sp_BackupDatabases

Хранимая процедура принимает следующие параметры:

Parameter	Обязательный	Description
`@backupLocation`	Yes	Папка, получающая файлы резервной копии, например `D:\SQLBackups\`. Папка уже должна существовать.
`@backupType`	Yes	Тип резервного копирования: `F` для полного, `D` разностного или `L` для журнала транзакций.
`@databaseName`	Нет	База данных для резервного копирования. Если этот параметр не указан, хранимая процедура создает резервную копию каждой веб-базы данных в экземпляре, за исключением баз данных, исключенных скриптом.

Хранимая процедура никогда не создаёт резервную копию tempdb. Он также пропускает примеры баз данных Northwindи pubsAdventureWorks для каждого типа резервного копирования, и пропускает master разностные резервные копии и резервные копии журналов, так как эти типы резервных копий не поддерживаются для master базы данных.

Important

Имена исключенных баз данных жестко закодированы в скрипте. Если у вас есть база данных, использующая одно из этих имен, хранимая процедура пропускает ее, не сообщая об ошибке. Чтобы создать резервную копию такой базы данных, измените DELETE @DBs WHERE DBNAME IN (...) списки в скрипте перед созданием хранимой процедуры.

Шаг 2. Установка служебной программы sqlcmd

Программа sqlcmd позволяет выполнять инструкции Transact-SQL, системные хранимые процедуры и файлы скриптов из командной строки. Он установлен с SSMS, и он также доступен в качестве автономного скачивания для компьютеров, у которых нет SSMS. Чтобы установить его, см. статью "Скачать и установить служебную программу sqlcmd".

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

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

Шаг 3. Создание пакетного файла Sqlbackup.bat

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

Прежде чем выбрать пример, рассмотрите следующие моменты:

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

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

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

Пример 2. Разностные резервные копии всех баз данных с помощью проверки подлинности SQL Server

REM Sqlbackup.bat
sqlcmd -U <YourSQLLogin> -P <StrongPassword> -S .\SQLEXPRESS -b -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='D'"

Примечание.

Для создания резервных копий учетная запись должна входить в предопределенную роль базы данных db_backupoperator или db_owner в каждой базе данных, резервную копию которой вы создаете, либо в предопределенную роль сервера sysadmin. Дополнительные сведения см. в разделе "Роли уровня базы данных".

Пример 3. Резервное копирование журналов транзакций всех баз данных с помощью проверки подлинности Windows

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

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

Пример 4. Полное резервное копирование одной базы данных с помощью проверки подлинности Windows

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

Чтобы сделать разностную резервную копиюUSERDB, измените параметр Dна @backupType . Чтобы создать резервную копию журнала транзакций, измените ее на L.

Параметр -b приводит к завершению работы sqlcmd и возврату значения DOS ERRORLEVEL, равного 1, если ошибка SQL Server имеет уровень серьёзности выше 10. Без -bsqlcmd возвращает 0 даже в случае сбоя резервного копирования, а Планировщик заданий сообщает, что задача выполнена успешно.

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

Шаг 4. Планирование пакетного файла в планировщике задач Windows

Выполните следующие действия, чтобы запустить Sqlbackup.bat по расписанию:

На компьютере под управлением SQL Server Express выберите "Пуск " и введите планировщик задач в текстовом поле.
В разделе "Лучший матч" выберите планировщик задач, чтобы запустить его.
В планировщике задач щелкните правой кнопкой мыши планировщик задач (локальный) и выберите "Создать базовую задачу".
Введите имя новой задачи (например, SQLBackup), а затем нажмите кнопку "Далее".
Выберите "Ежедневно " для триггера задачи и нажмите кнопку "Далее".
Установите значение повторения в один день и нажмите кнопку "Далее".
Нажмите кнопку "Пуск программы" в качестве действия и нажмите кнопку "Далее".
Выберите "Обзор", выберите файлSqlbackup.bat , созданный на шаге 3. Создайте пакетный файл Sqlbackup.bat и нажмите кнопку "Открыть".
Установите флажок Открывать диалоговое окно «Свойства» для этой задачи при нажатии кнопки «Готово», а затем нажмите кнопку «Готово».
На вкладке Общие просмотрите параметры безопасности и подтвердите следующее для учетной записи, указанной в разделе При выполнении задачи использовать следующую учетную запись пользователя:
- Учетная запись имеет как минимум разрешения на чтение и выполнение для запуска утилиты sqlcmd.
- Если пакетный файл использует проверку подлинности Windows, учетная запись имеет разрешение на резервное копирование баз данных SQL Server.
- Если пакетный файл использует проверку подлинности SQL Server, то логин SQL Server, указанный в пакетном файле, имеет разрешения на резервное копирование баз данных.
Измените оставшиеся параметры, чтобы соответствовать вашим требованиям, а затем нажмите кнопку "ОК".

Совет

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

Дополнительные сведения о параметрах планирования см. в разделе "Планировщик задач".

Убедитесь, что запланированное резервное копирование запущено

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

Проверьте папку резервного копирования для новых файлов .bak (полные и разностные резервные копии) или TRN-файлы (резервные копии журналов транзакций). Хранимая процедура добавляет метку даты и времени к каждому имени файла.
В планировщике задач выберите библиотеку планировщика задач, выберите задачу и просмотрите столбец результата последнего запуска . В этом столбце показан код выхода, возвращенный пакетным файлом. Так как в примерах используется -b параметр, значение 0x1 означает, что команда резервного копирования завершилась ошибкой. Другие значения см. в разделе "Ошибка планировщика задач" и константы успешного выполнения.
Запросите историю резервного копирования в экземпляре:
```
SELECT database_name, type, backup_start_date, backup_finish_date, physical_device_name
FROM msdb.dbo.backupset AS bs
INNER JOIN msdb.dbo.backupmediafamily AS bmf
    ON bs.media_set_id = bmf.media_set_id
ORDER BY backup_start_date DESC;
```
Эти таблицы являются частью истории резервного копирования, которую SQL Server ведет в базе данных msdb. Дополнительные сведения см. в разделе История резервного копирования и сведения заголовка (SQL Server).