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

Параметры

Databases

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

Directory

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

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

BackupType

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

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

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

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

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

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

Verify

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

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

CleanupTime

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

CleanupMode

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

Compress

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

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

CopyOnly

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

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

ChangeBackupType

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

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

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

BackupSoftware

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

CheckSum

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

Параметр 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

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

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

EncryptionAlgorithm

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

Параметр 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

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

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

OverrideBackupPreference

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

NoRecovery

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

Опция 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

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

AvailabilityGroups

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

Updateability

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

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

LogToTable

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

Execute

Выполнить команды. По умолчанию команды выполняются нормально. Если этот параметр установлен в 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

Похожие сообщения:

MS SQL — архивация, оптимизация, проверка DatabaseIntegrityCheck — хранимая процедура проверки целостности базы данных Выполнение операций над несколькими или всеми базами MS SQL Оптимизация БД MSSQL