DatabaseBackup – хранимая процедура архивации (полный и инкрементный)

Оглавление

Хранимая процедура архивации (полный и инкрементный) DatabaseBackup (Официальный сайт) – этот скрипт очень помог при работе с MS SQL Express, так как позволяет выполнять архивацию по сети, на несколько хранилищ и Azure Blob Storage. Поддерживают полные, инкрементные и дифференциальные архивы. Поддерживает шифрование, сжатие и проверку созданного архива. Все то что можно сделать в Maintenes Plan, доступно и тут.

Параметры

Databases

Выбор баз. В качестве аргументов, отвечающих за выбор объектов, можно передать SYSTEM_DATABASES, USER_DATABASES, ALL_DATABASES и AVAILABILITY_GROUP_DATABASES если поддерживается. Знак (-) используется для исключения базы, а знак процента (%) выполняет роль символа подстановки. Все используемые параметры могут быть разделены занятой (,)

Переменная Описание
SYSTEM_DATABASES Все системные базы  (master, msdb, and model)
USER_DATABASES Все пользовательские базы
ALL_DATABASES Все базы
AVAILABILITY_GROUP_DATABASES Все базы в группе доступности
USER_DATABASES, -AVAILABILITY_GROUP_DATABASES Все пользовательские базы данных, которые не входят в группы доступности
Db1 База данных Db1
Db1, Db2 Базы данных Db1 и Db2
USER_DATABASES, -Db1 Все пользовательские базы, за исключением Db1
%Db% Все базы, содержащие в имени “Db”
%Db%, -Db1 Все базы, содержащие в имени “Db”. за исключением Db1
ALL_DATABASES, -%Db% Все базы, не содержащие в имени “Db”

Directory

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

Переменная Описание
NULL Создает резервную копию в каталоге по умолчанию, указанном в SQL Server. По умолчанию
C:\Backup Архивация в директорию C:\Backup
C:\Backup, D:\Backup Архивация в директорию C:\Backup и D:\Backup
\\Server1\Backup Архивация по сети, в общую папку \\Server1\Backup
\\Server1\Backup, \\Server2\Backup Архивация по сети в общие папки \\Server1\Backup и \\Server2\Backup

DatabaseBackup создает структуру каталогов с именем сервера, именем экземпляра, именем базы данных и типом резервного копирования в корневом каталоге резервного копирования. Если база данных является частью группы доступности, вместо имени сервера и имени экземпляра используются имя имени группы и группы доступности.

BackupType

Укажите тип резервного копирования: полный, дифференциальный или журнал транзакций.

Переменная Описание
FULL Полный архив
DIFF Дифференциальный архив
LOG Архив транзакций

DatabaseBackup использует команду SQL Server BACKUP: BACKUP DATABASE для полной резервной копии, BACKUP DATABASE WITH DIFFERENTIAL для дифференциальной резервной копии и BACKUP LOG для резервного копирования журнала транзакций.

Разница между дифференциальным и инкрементным архивом следующая:

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

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

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

Verify

Проверка созданного архива.

Переменная Описание
Y Проверять архив
N Не проверять. По умолчанию

Используется команда SQL Server RESTORE VERIFYONLY.

CleanupTime

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

CleanupMode

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

Переменная Описание
BEFORE_BACKUP Удалить старые архив до резервного копирования
AFTER_BACKUP Удалить старые архив после резервного копирования. Если резервная копия или проверка не удалась, файлы резервных копий не удаляются. По умолчанию

Compress

Сжимайть резервную копию. Если значение не указано, используется резервное сжатие по умолчанию в sys.configurations.

Переменная Описание
NULL Используется стандартное сжатие по умолчанию в sys.configurations. По умолчанию.
Y Сжимать архив
N Не сжимать архив

Опция Compress  в DatabaseBackup использует параметры COMPRESSION и NO_COMPRESSION в команде SQL Server BACKUP.

CopyOnly

Выполните резервное копирование только для копирования.

Переменная Описание
Y Выполнить только копирование
N Выполнять нормально резервное копирование. По умолчанию

Опция CopyOnly в DatabaseBackup использует параметр COPY_ONLY в команде SQL Server BACKUP.

ChangeBackupType

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

Переменная Описание
Y Измените тип резервного копирования, если резервная копия не может быть выполнена
N Пропустите резервное копирование, если резервная копия не может быть выполнена. По умолчанию

DatabaseBackup проверяет diff_base_lsn в sys.master_files, чтобы определить, может ли выполняться дифференциальная резервная копия. Если дифференциальная резервная копия невозможна, база данных пропускается по умолчанию. Кроме того, вы можете установить ChangeBackupType на Y, чтобы вместо этого выполнить полную резервную копию.

DatabaseBackup проверяет last_log_backup_lsn в sys.database_recovery_status, чтобы определить, может ли быть выполнена резервная копия журнала транзакций в полном объеме или модель восстановления с большим объемом журнала. Если резервное копирование журнала транзакций невозможно, база данных по умолчанию пропускается. Кроме того, вы можете установить ChangeBackupType на Y, чтобы вместо этого выполнялась дифференциальная или полная резервная копия.

BackupSoftware

Укажите стороннее программное обеспечение для резервного копирования; в противном случае выполняется резервное копирование SQL Server.

Переменная Описание
NULL SQL Server. По умолчанию
LITESPEED LiteSpeed for SQL Server
SQLBACKUP Red Gate SQL Backup Pro
SQLSAFE Idera SQL Safe Backup

CheckSum

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

Переменная Описание
Y Включить контрольные суммы резервного копирования
N Не включать. По умолчанию

Параметр CheckSum в DatabaseBackup использует опцию CHECKSUM в команде SQL Server BACKUP.

BlockSize

Укажите физический размер блока в байтах. Параметр BlockSize в DatabaseBackup использует параметр BLOCKSIZE в команде SQL Server BACKUP.

BufferCount

Укажите количество буферов ввода-вывода, которые будут использоваться для операции резервного копирования. Параметр BufferCount в DatabaseBackup использует параметр BUFFERCOUNT в команде SQL Server BACKUP.

MaxTransferSize

Укажите самую большую единицу передачи в байтах, которая будет использоваться между SQL Server и носителем резервного копирования. Параметр MaxTransferSize в DatabaseBackup использует параметр MAXTRANSFERSIZE в команде SQL Server BACKUP.

NumberOfFiles

Укажите количество резервных файлов. По умолчанию используется количество резервных каталогов, а максимальное – 64 файла.

CompressionLevel

Установите LiteSpeed ​​для SQL Server, Red Gate SQL Backup Pro или Idera SQL Safe Backup уровень сжатия. В LiteSpeed ​​для SQL Server поддерживаются уровни сжатия от 0 до 8. В Red Gate SQL Backup Pro поддерживаются уровни с 0 по 4, а в Idera SQL Safe Backup поддерживаются уровни с 1 по 4.

Description

Введите описание для резервной копии.

Параметр Описание в DatabaseBackup использует опцию DESCRIPTION в команде SQL Server BACKUP.

Threads

Укажите LiteSpeed ​​для SQL Server, Red Gate SQL Backup Pro или Idera SQL Safe Backup количество потоков. Максимальное количество потоков – 32.

Throttle

Укажите максимальное использование ЦП LiteSpeed ​​для SQL Server в процентах.

Encrypt

Шифрование архива.

Переменная Описание
Y Зашифровать.
N Не шифровать. По умолчанию

Параметр Encrypt в DatabaseBackup использует параметр ENCRYPTION в команде SQL Server BACKUP.

EncryptionAlgorithm

Указывает тип шифрования

Переменная Описание
NULL Без шифрования. По умолчанию
RC2_40 RC2 40-bit (LiteSpeed for SQL Server)
RC2_56 RC2 56-bit (LiteSpeed for SQL Server)
RC2_112 RC2 112-bit (LiteSpeed for SQL Server)
RC2_128 RC2 128-bit (LiteSpeed for SQL Server)
TRIPLE_DES_3KEY Triple DES (SQL Server 2014 or LiteSpeed for SQL Server)
RC4_128 RC4 128-bit (LiteSpeed for SQL Server)
AES_128 AES 128-bit (SQL Server 2014, LiteSpeed for SQL Server, Red Gate SQL Backup Pro, or Idera SQL Safe Backup)
AES_192 AES 192-bit (SQL Server 2014 or LiteSpeed for SQL Server)
AES_256 AES 256-bit (SQL Server 2014, LiteSpeed for SQL Server, Red Gate SQL Backup Pro, or Idera SQL Safe Backup)

Параметр EncryptionAlgorithm в DatabaseBackup использует опции ENCRYPTION и ALGORITHM в команде SQL Server BACKUP.

ServerCertificate

Сертификат сервера, который используется для шифрования резервной копии.

Параметр ServerCertificate в DatabaseBackup использует параметры ENCRYPTION и SERVER CERTIFICATE в команде SQL Server BACKUP.

ServerAsymmetricKey

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

Параметр ServerAsymmetricKey в DatabaseBackup использует параметры ENCRYPTION и SERVER ASYMMETRIC KEY в команде SQL Server BACKUP.

EncryptionKey

Ключ, который используется для шифрования резервной копии. Это используется с LiteSpeed ​​для SQL Server, Red Gate SQL Backup Pro и Idera SQL Safe Backup.

ReadWriteFileGroups

Выполнение резервного копирования основной файловой группы и любых файловых групп чтения / записи.

Переменная Описание
Y Резервное копирование основной файловой группы и любых файловых групп чтения / записи
N Обычное резервное копирование. По умолчанию.

Опция ReadWriteFileGroups в DatabaseBackup использует параметр READ_WRITE_FILEGROUPS в команде SQL Server BACKUP.

OverrideBackupPreference

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

Переменная Описание
Y Предпочтение резервного копирования для групп доступности
N Без предпочтения резервной копии для групп доступности. По умолчанию

NoRecovery

Выполнить резервное копирование журнала транзакция и оставить базу данных в состоянии ВОССТАНОВЛЕНИЕ

Переменная Описание
Y Выполните резервное копирование журнала
N Выполнить нормальное архивирование. По умолчанию

Опция NoRecovery в DatabaseBackup использует параметр NORECOVERY в команде SQL Server BACKUP.

URL

Укажите имя домена и контейнер для резервного копирования в хранилище Azure Blob.

Параметр URL в DatabaseBackup использует параметр URL в команде SQL Server BACKUP.

Credential

Specify a CREDENTIAL for backup to Windows Azure Blob Storage.

The Credential option in DatabaseBackup uses the CREDENTIAL option in the SQL Server BACKUP command.

MirrorDirectory

Укажите один или несколько каталогов для выполнения зеркальной резервной копии.

Параметр MirrorDirectory в DatabaseBackup использует параметр MIRROR TO в команде SQL Server BACKUP

MirrorCleanupTime

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

MirrorCleanupMode

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

Переменная Описание
BEFORE_BACKUP До архивации
AFTER_BACKUP Удалите старые резервные файлы после выполнения резервного копирования. По умолчанию

AvailabilityGroups

Выберите группы доступности. Поддерживается ключевое слово ALL_AVAILABILITY_GROUPS. Символ дефиса (-) используется для исключения групп доступности, а процентный символ (%) используется для выбора подстановочных знаков. Все эти операции можно объединить, используя запятую (,)

Переменная Описание
ALL_AVAILABILITY_GROUPS Все группы доступности
AG1 Группа доступности AG1
AG1, AG2 Группы доступности AG1 и AG1
ALL_AVAILABILITY_GROUPS, -AG1 Все группы доступности, кроме AG1
%AG% Все группы доступности, содержащие в своем имени “AG”
%AG%, -AG1 Все группы доступности содержащие в своем имени “AG”, кроме AG1
ALL_AVAILABILITY_GROUPS, -%AG% Все группы доступности, за исключением групп, содержащих в имени AG

Updateability

Выбрать READ_ONLY / READ_WRITE – базы данных.

Переменная Описание
ALL Выбрать READ_ONLY и READ_WRITE – базы. По умолчанию
READ_ONLY Базы в режиме только для чтения
READ_WRITE Базы в режиме чтения запись

sys.databases используется для проверки, является ли база данных READ_ONLY или READ_WRITE.

LogToTable

Запись лога в таблицу dbo.CommandLog.

Переменная Описание
Y Записывать лог в таблицу
N Не записывать лог. По умолчанию

Execute

Выполнить команды. По умолчанию команды выполняются нормально. Если этот параметр установлен в N, то команды печатаются только.

Переменная Описание
Y Выполнять команды. По умолчанию
N Только напечатать

Примеры

A. Резервное копирование всех пользовательских баз данных с использованием контрольных сумм и сжатием; проверкой резервной копии; и удалением старых файлов резервных копий

EXECUTE dbo.DatabaseBackup
@Databases = ‘USER_DATABASES’,
@Directory = ‘C:\Backup’,
@BackupType = ‘FULL’,
@Verify = ‘Y’,
@Compress = ‘Y’,
@CheckSum = ‘Y’,
@CleanupTime = 24

B. Резервное копирование всех пользовательских баз данных в общий сетевой ресурс и проверка резервной копии

EXECUTE dbo.DatabaseBackup
@Databases = ‘USER_DATABASES’,
@Directory = ‘\\Server1\Backup’,
@BackupType = ‘FULL’,
@Verify = ‘Y’

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

EXECUTE dbo.DatabaseBackup
@Databases = ‘USER_DATABASES’,
@Directory = ‘\\Server1\Backup, \\Server2\Backup, \\Server3\Backup, \\Server4\Backup’,
@BackupType = ‘FULL’,
@Verify = ‘Y’,
@NumberOfFiles = 4

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

EXECUTE dbo.DatabaseBackup
@Databases = ‘USER_DATABASES’,
@Directory = ‘C:\Backup’,
@BackupType = ‘FULL’,
@Compress = ‘Y’,
@CheckSum = ‘Y’,
@BufferCount = 50,
@MaxTransferSize = 4194304,
@NumberOfFiles = 64

E. Резервное копирование всех пользовательских баз данных в хранилище Azure Blob с использованием сжатия

EXECUTE dbo.DatabaseBackup
@Databases = ‘USER_DATABASES’,
@URL = ‘https://myaccount.blob.core.windows.net/mycontainer’,
@Credential = ‘MyCredential’,
@BackupType = ‘FULL’,
@Compress = ‘Y’,
@Verify = ‘Y’

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

EXECUTE dbo.DatabaseBackup
@Databases = ‘USER_DATABASES’,
@Directory = ‘C:\Backup’,
@BackupType = ‘LOG’,
@ChangeBackupType = ‘Y’

G. Резервное копирование всех пользовательских баз данных с использованием сжатия, шифрования и сертификата сервера

EXECUTE dbo.DatabaseBackup @Databases = ‘USER_DATABASES’,
@Directory = ‘C:\Backup’,
@BackupType = ‘FULL’,
@Compress = ‘Y’,
@Encrypt = ‘Y’,
@EncryptionAlgorithm = ‘AES_256’,
@ServerCertificate = ‘MyCertificate’

H. Резервное копирование всех пользовательских баз данных с использованием сжатия, шифрования и LiteSpeed ​​для SQL Server и ограничение использования ЦП до 10 процентов

EXECUTE dbo.DatabaseBackup
@Databases = ‘USER_DATABASES’,
@Directory = ‘C:\Backup’,
@BackupType = ‘FULL’,
@BackupSoftware = ‘LITESPEED’,
@Compress = ‘Y’,
@Encrypt = ‘Y’,
@EncryptionAlgorithm = ‘AES_256’,
@EncryptionKey = ‘MyPassword’,
@Throttle = 10

I. Резервное копирование всех пользовательских баз данных с использованием сжатия, шифрования и Red Gate SQL Backup Pro

EXECUTE dbo.DatabaseBackup
@Databases = ‘USER_DATABASES’,
@Directory = ‘C:\Backup’,
@BackupType = ‘FULL’,
@BackupSoftware = ‘SQLBACKUP’,
@Compress = ‘Y’,
@Encrypt = ‘Y’,
@EncryptionAlgorithm = ‘AES_256’,
@EncryptionKey = ‘MyPassword’

J. Резервное копирование всех пользовательских баз данных с использованием сжатия, шифрования и безопасного резервного копирования Idera SQL

EXECUTE dbo.DatabaseBackup
@Databases = ‘USER_DATABASES’,
@Directory = ‘C:\Backup’,
@BackupType = ‘FULL’,
@BackupSoftware = ‘SQLSAFE’,
@Compress = ‘Y’,
@Encrypt = ‘Y’,
@EncryptionAlgorithm = ‘AES_256’,
@EncryptionKey = ‘8tPyzp4i1uF/ydAN1DqevdXDeVoryWRL’

Execution

Выполните хранимые процедуры из заданий SQL Server Agent CmdExec, используя sqlcmd и -b. Передайте параметры хранимым процедурам по имени:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q “EXECUTE dbo.DatabaseBackup @Databases = ‘USER_DATABASES’, @Directory = ‘C:\Backup’, @BackupType = ‘FULL'” -b

Просмотров: 94

Звёзд: 1Звёзд: 2Звёзд: 3Звёзд: 4Звёзд: 5 (1 оценок, среднее: 5,00 из 5)
Загрузка...

Оставить комментарий

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