Резервное копирование MS SQL Server
Обновлено:
Опубликовано:
Есть несколько способов создания резервной копии MS SQL. Для разовых операций прекрасно подойдет графический инструмент SQL Management Studio. Для автоматизации — Powershell или cmd. Данные операции применяются к любым базам, как для 1С, так и любых других приложений.
В management studio
Командная строка (cmd)
Powershell
Срок действия резервного набора данных
Путь расположения резервных копий
Усечение логов
С помощью графического интерфейса
Открываем MS SQL Management Studio. Кликаем правой кнопкой мыши по базе, для которой хотим сделать резервную копию - Задачи - Создать резервную копию:
В открывшемся окне оставляем полный тип копий и путь к резервному файлу (при необходимости, можно его поменять, удалив и создав снова. Можно указать как локальный диск, так и сетевой):
После завершения процесса мы увидим сообщение «Резервное копирование базы ... успешно завершено».
С помощью командной строки (cmd)
Данный способ удобно использовать для автоматизации резервного копирования. Более того, команды подходят как для Windows, так и Linux. Выполняется при помощи утилиты sqlcmd.
Синтаксис:
sqlcmd -S <server> -U <user> -P <password> -Q "BACKUP DATABASE [<database>] TO DISK = N'<file path>' <options>"
Пример готового скрипта
@echo off
set dd=%DATE:~0,2%
set mm=%DATE:~3,2%
set yyyy=%DATE:~6,4%
set curdate=%dd%-%mm%-%yyyy%
set username=sa
set password=my_pass
set db=work1
sqlcmd -S localhost -U %username% -P %password% -Q "BACKUP DATABASE [%db%] TO DISK = N'D:\Backup\MSSQL\%db%_%curdate%.bak' WITH NOFORMAT, NOINIT, NAME = N'%db%-full', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10"
set db=work2
sqlcmd -S localhost -U %username% -P %password% -Q "BACKUP DATABASE [%db%] TO DISK = N'D:\Backup\MSSQL\%db%_%curdate%.bak' WITH NOFORMAT, NOINIT, NAME = N'%db%-full', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10"
* в данном примере мы подключаемся к локальному SQL серверу под учетной записью sa с паролем my_pass и делаем резервную копию баз work1 и work2. Резервные копии размещаем по пути D:\Backup\MSSQL. Имя файлов резервных копий work1_<текущая дата>.bak и work2_<текущая дата>.bak
* некоторые опции могут не работать, в зависимости от используемой редакции MS SQL.
Для автоматизации скрипта, создайте задание в планировщике, чтобы скрипт запускался по расписанию.
Типы резервных копий
Хорошей практикой является создание разных типов копий:
1) Полное копирование — резервирование всей базы. Выполняется командой, рассмотренной выше, например:
sqlcmd -S localhost -U sa -P my_pass -Q "BACKUP DATABASE work1 TO DISK = N'D:\Backup\MSSQL\bak_full.bak' WITH NOFORMAT, NOINIT, NAME = N'bak-full', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
* в данном примере мы подключаемся к локальному серверу под пользователем sa с паролем my_pass и делаем полную копию базы work1; саму копию сохраняем в виде файла D:\Backup\MSSQL\bak_full.bak.
2) Разностное (дифференциальное) — резервирование базы данных с момента создания последней полной копии. Выполняется командой для резервного копирования с добавлением опции DIFFERENTIAL:
sqlcmd -S localhost -U sa -P my_pass -Q "BACKUP DATABASE work1 TO DISK = N'D:\Backup\MSSQL\bak_diff.bak' WITH DIFFERENTIAL, NOFORMAT, NOINIT, NAME = N'bak-diff', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
3) Инкрементальное или копирование логов. Выполняется Transact-SQL:
sqlcmd -S localhost -U sa -P my_pass -Q "BACKUP LOG work1 TO DISK = N'D:\Backup\MSSQL\bak_log.bak' WITH NOFORMAT, NOINIT, NAME = N'bak-log', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
* обратите внимение, команда похожа на команду для полного резервного копирования — вместо DATABASE пишем LOG.
С помощью Powershell
Данный способ может быть не доступен на старых системах. В остальном, стоит придерживаться именно такого способа резервного копирования.
Для выполнения команды, сначала импортируем модуль:
import-module sqlps -DisableNameChecking
Синтаксис:
Backup-SqlDatabase -ServerInstance <имя SQL сервера> -Database <имя базы> -BackupFile <путь к файлу с резервной копией>
Пример скрипта на powershell
$server = "SQL01"
$curdate = Get-Date -Format yyyyMMdd
import-module sqlps -DisableNameChecking
$db = work1
Backup-SqlDatabase -ServerInstance $server -Database $db -BackupFile $db_$curdate.bak
* где выполняется резервное копирования базы work1 на сервере SQL01
Также как и для cmd, данный скрипт можно поместить в планировщик для запуска по расписанию.
Срок действия резервного набора данных
Данная настройка позволяет указать, через какой промежуток времени резервную копию можно удалить (перезаписать). Важно понимать, что настройка не влияет на сам период восстановления — если срок истек, восстановиться из набора можно.
Задать параметр можно в основном окне при создании резервной копии:
Путь расположения резервных копий
Все резервные копии по умолчанию будут попадать в каталог резервных копий. Чтобы его посмотреть и поменять, при необходимости, выполняем следующее.
Кликаем правой кнопкой мыши по корневому разделу SQL Server и выбираем свойства:
Переходим в раздел Параметры баз данных (1) - в подразделе «Места хранения, используемые базой данных по умолчанию» мы увидим путь до места размещения резервных копий (2), который можно поменять кнопкой справа (3):
Усечение логов
Хорошей идеей будет удалить записи в журналах после создания резервной копии. Данные журналы имеют свойства разрастаться, что приводит к переполнению дисков.
Чтобы сжать журнал из графического интерфейса в MS SQL Management Studio кликните по нужной базе правой кнопой мыши - перейдите в Задачи - Сжать - Файлы:
Для опции Тип файла выбираем Журнал:
Выбираем, до какого размера усечь журнал:
Нажимаем OK.
Также мы можем выполнить данное действие из строки SQL:
DBCC SHRINKFILE("database_name_log", 1024);
Также выше приведет пример скрипта в cmd. Можно добавить в него усечение лога, например:
set db=work1
sqlcmd -S localhost -U %username% -P %password% -Q "BACKUP DATABASE [%db%] TO DISK = N'D:\Backup\MSSQL\%db%_%curdate%.bak' WITH NOFORMAT, NOINIT, NAME = N'%db%-full', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10"
sqlcmd -S localhost -U %username% -P %password% -Q "DBCC SHRINKFILE("%db%_log", 1024)"
