Создание резервной копии моментальных снимков Transact-SQL
Область применения: SQL Server 2022 (16.x)
В этой статье объясняется, почему и как использовать резервные копии моментальных снимков Transact-SQL. Резервные копии моментальных снимков Transact-SQL являются новыми в SQL Server 2022 (16.x).
Базы данных становятся все объемнее изо дня на день. Традиционно резервные копии SQL Server являются потоковыми резервными копиями. Потоковая архивация зависит от размера базы данных. Операции резервного копирования используют ресурсы (ЦП, память, операции ввода-вывода, сеть), которые влияют на пропускную способность параллельной рабочей нагрузки OLTP в течение длительности резервного копирования. Одним из способов сделать производительность резервного копирования постоянной, а не зависящей от размера данных, является выполнение резервного копирования моментального снимка с помощью механизмов, предоставляемых базовым оборудованием хранилища или службой хранилища.
Так как само резервное копирование происходит на аппаратном уровне, это не чистое решение SQL Server. SQL Server сначала должен подготовить файлы данных и журналов для моментального снимка, чтобы файлы гарантированно были в состоянии, которое может быть восстановлено позже. После этого операции записи приостановлены на SQL Server (запросы на чтение по-прежнему разрешены), а управление передается приложению резервной копии для завершения моментального снимка. После успешного завершения моментального снимка приложение должно вернуть управление обратно в SQL Server, где затем возобновляются операции записи. Так как необходимо заморозить операции записи в течение длительной операции моментального снимка, важно быстро выполнить моментальный снимок, чтобы рабочая нагрузка на сервере не прерывалась в течение длительного периода. Раньше пользователи полагались на сторонние решения, созданные на базе модуля записи SQL для создания резервных копий моментальных снимков. Служба модуля записи SQL зависит от Windows VSS (служба теневого копирования томов) и SQL Server VDI (интерфейс виртуального устройства) для выполнения оркестрации между SQL Server и моментальным снимком на уровне диска. Клиенты резервного копирования на базе службы модуля записи SQL, как правило, сложные и работают только в Windows. С помощью резервных копий моментальных снимков T-SQL часть оркестрации, выполняемая на стороне SQL Server, может обрабатываться с помощью ряда команд T-SQL. Это позволяет пользователям создавать собственные небольшие приложения резервного копирования, которые могут работать в Windows или Linux, или даже сценариев, если базовое хранилище поддерживает интерфейс сценариев для запуска моментального снимка.
Ниже приведен пример скрипта PowerShell, демонстрирующего комплексное решение резервного копирования и восстановления базы данных в виртуальной машине IaaS SQL Azure с помощью возможностей резервного копирования моментальных снимков T-SQL, представленных в SQL Server 2022 (16.x) (и более поздних версий).
Рабочий процесс
Синтаксис резервного копирования моментальных снимков T-SQL отделяет зависящий от поставщика механизм моментальных снимков от операций приостановки и резервного копирования. С помощью этого синтаксиса можно сделать следующее:
- Заморозить базу данных с помощью команды ALTER для того, чтобы сделать снимок базового хранилища. После этого можно разморозить базу данных и записать моментальный снимок с помощью команды BACKUP.
- Выполнять моментальные снимки нескольких баз данных одновременно с помощью новых команд BACKUP GROUP и BACKUP SERVER. С помощью этого параметра моментальные снимки можно выполнять при детализации моментального снимка базового хранилища, устраняя необходимость выполнения моментального снимка одного диска несколько раз.
- Выполнение полного резервного копирования и COPY_ONLY полных резервных копий. Эти резервные копии также записываются
msdb. - Выполните восстановление на определенный момент времени с использованием резервных копий журналов, созданных с использованием обычного потокового подхода после полного резервного копирования моментального снимка. При желании также можно воспользоваться поддержкой разностных резервных копий потоковой передачи.
Примечание.
Разностные растровые изображения очищаются на первом этапе во время приостановки базы данных с помощью команды ALTER. Если пользователь решит разморозить базу данных без выполнения резервного копирования из-за сбоя моментального снимка или по любой другой причине, разностное растровое изображение станет недопустимым. Таким образом, любые последующие разностные резервные копии будут более интенсивными, так как они должны сканировать всю базу данных для создания разностных резервных копий. Разностное растровое изображение снова станет допустимым после успешного резервного копирования моментальных снимков.
На следующей диаграмме показан высокоуровневый рабочий процесс резервного копирования моментальных снимков T-SQL:
На этапе среднего снимка требуется, чтобы вы запускали моментальный снимок в базовом хранилище. На следующей схеме показан пример работы скрипта резервного копирования с SQL Server для завершения процесса резервного копирования моментальных снимков:
Точно так же сценарий восстановления может работать следующим образом:
Ограничения
Максимальное количество баз данных, которые можно создать с помощью этой функции, — 64. Если на сервере есть более 64 баз данных, появится следующая ошибка:
Error message:
Msg 925, Level 19, State 1, Line 4
Maximum number of databases used for each query has been exceeded. The maximum allowed is 64.
Примеры
В следующих разделах показаны различные команды T-SQL, используемые для резервного копирования моментальных снимков на диск. Когда резервная копия моментального снимка записывается на диск, в файл записываются только метаданные, подключенные к резервной копии моментальных снимков. Выходные данные не содержат содержимого базы данных, кроме заголовка и содержимого файла. Файл оболочки, созданный как часть выполнения резервного копирования моментальных снимков, должен использоваться с фактическим универсальным кодом ресурса (URI моментального снимка) для создания полной резервной копии. RESTORE базы данных из этого файла требует, чтобы пользователь скопирует файлы базы данных из URI моментального снимка в точку подключения до выдачи команды RESTORE. Пользователи могут выполнять все традиционные команды T-SQL, такие как RESTORE HEADERONLY, RESTORE FILELISTONLY в этом файле метаданных резервного копирования моментальных снимков вместе с RESTORE DATABASE. Синтаксис поддерживает запись метаданных резервного копирования моментальных снимков на диск или по URL-адресу. Резервные наборы данных моментальных снимков также могут быть добавлены аналогично резервным наборам данных потоковой передачи.
Примечание.
Для резервного копирования по URL-адресу предпочтительнее использовать блочные BLOB-объекты, хотя страничные BLOB-объекты поддерживаются для SQL Server в Windows. Для SQL Server на Linux и для контейнеров поддерживаются только блочные BLOB-объекты.
Приостановка отдельной пользовательской базы данных для резервного копирования моментальных снимков и запись резервной копии базы данных
ALTER DATABASE testdb1
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;
BACKUP DATABASE testdb1
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
Приостановка нескольких пользовательских баз данных для резервного копирования моментальных снимков
Если несколько баз данных на одном и том же базовом диске, можно приостановить несколько баз данных с помощью следующей команды.
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(GROUP = (testdb1, testdb2));
BACKUP GROUP testdb1, testdb2
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
Приостановка всех пользовательских баз данных на сервере для резервного копирования моментальных снимков
Если все пользовательские базы данных на сервере должны быть приостановлены, используйте следующую команду.
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;
BACKUP SERVER
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
Примечание.
Ни одна из этих команд не поддерживает приостановку системных баз данных: masterи modelmsdb резервного копирования моментальных снимков.
Приостановка нескольких пользовательских баз данных с помощью одной команды
Запись моментального снимка всех пользовательских баз данных на сервере в один резервный набор данных:
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(GROUP = (testdb1, testdb2));
BACKUP GROUP testdb1, testdb2
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
Примечание.
По умолчанию, приостановка команд резервного копирования моментальных снимков очищает разностное растровое изображение. Если вы предпочитаете выполнять резервное копирование, используйте COPY_ONLY ключевое слово, как показано в следующих примерах.
Выполнение резервных копий моментальных снимков только для копирования
Так как разностное растровое изображение перед замораживанием очищается, команда SUSPEND_FOR_SNAPSHOT_BACKUP предоставляет параметр выбора (COPY_ONLY), чтобы не очищать изображение перед замораживанием.
ALTER DATABASE testdb1
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(MODE = COPY_ONLY);
BACKUP DATABASE testdb1
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(GROUP = (testdb1, testdb2), MODE = COPY_ONLY);
BACKUP GROUP testdb1, testdb2
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(MODE = COPY_ONLY);
BACKUP SERVER
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
Примечание.
Не обязательно использовать COPY_ONLY в команде BACKUP, так как она уже указана при приостановке базы данных для резервного копирования моментальных снимков.
Тег набора резервных копий
Вы можете использовать параметры MEDIANAME и MEDIADESCRIPTION в команде резервного копирования для тега URI, связанного с моментальным снимком. Это позволяет файлу резервного копирования передавать базовые сведения моментального снимка вместе с метаданными базы данных. Можно также использовать параметры NAME и DESCRIPTION для тега URI с помощью отдельного моментального снимка набора резервных копий.
SQL Server не интерпретирует сведения LABEL каким-либо образом. Однако он помогает пользователю просмотреть URI, связанный с резервной копией моментальных снимков с помощью команды RESTORE LABELONLY.
Затем можно подключить диски моментальных снимков, расположенные в URI, к виртуальной машине, чтобы восстановить моментальный снимок. URI моментального снимка, хранящийся в MEDIANAME и MEDIADESCRIPTION, также будет доступен для просмотра в msdb таблице msdb.dbo.backupmediasetбазы данных.
Выходные данные резервного копирования моментальных снимков с помощью RESTORE HEADERONLY
Если база данных, группа и сервер выполняются последовательно и записываются в один и тот же выходной файл, выходные данные с параметром RESTORE HEADERONLY выглядят следующим образом:
RESTORE HEADERONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY;
Выходные данные резервного копирования моментальных снимков с помощью RESTORE FILELISTONLY
Выходные данные с параметром RESTORE FILELISTONLY отображают первичный резервный набор по умолчанию:
RESTORE FILELISTONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY;
Фильтрация выходных данных RESTORE FILELISTONLY в резервный набор данных
Чтобы выбрать определенный резервный набор из нескольких резервных наборов с помощью RESTORE FILELISTONLY, используйте предложение FILE, которое уже поддерживается в RESTORE FILELISTONLY.
RESTORE FILELISTONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3;
Фильтрация выходных данных RESTORE FILELISTONLY в базу данных
Фильтрация. Для дальнейшего выбора отдельной базы данных из нескольких баз данных в выбранном резервном наборе данных с предложением RESTORE FILELISTONLY используйте предложение FILE с новым предложением DBNAME. Предложение DBNAME можно использовать только в резервных наборах данных моментальных снимков.
RESTORE FILELISTONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb3';
Восстановление базы данных моментальных снимков
Восстановление базы данных из резервной копии моментальных снимков похоже на подключение базы данных. Запустите команду восстановления без параметра RECOVERY, если базу данных необходимо подключить без восстановления. По умолчанию RESTORE выбирает первичную базу данных в резервном наборе данных моментальных снимков. В следующем примере выполняется восстановление testdb1. Если testdb1 уже находится на сервере, включите предложение REPLACE. Перед запуском RESTORE необходимо подключить файлы базы данных.
RESTORE DATABASE testdb1
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, REPLACE, --> no DBNAME clause - restore first database in backup set
MOVE 'testdb1' TO 'd:\temp\snap\testdb1.mdf',
MOVE 'testdb1_log' TO 'd:\temp\snap\testdb1_log.ldf';
Восстановление базы данных моментальных снимков, указанной посередине
Если база данных, которую необходимо восстановить, находится посередине, укажите базу данных для восстановления с помощью предложения DBNAME. Следующий синтаксис восстанавливает указанную базу данных в предложении DBNAME.
RESTORE DATABASE testdb3
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb3', --> restores testdb3 database
MOVE 'testdb3' TO 'd:\temp\snap\testdb3.mdf',
MOVE 'testdb3_log' TO 'd:\temp\snap\testdb3_log.ldf',
NORECOVERY;
Восстановление базы данных под другим именем
Базу данных можно восстановить под другим именем. Если база данных, которую необходимо восстановить, находится посередине, укажите базу данных для восстановления с помощью предложения DBNAME. Следующий синтаксис восстанавливает указанную базу данных с предложением DBNAME и переименовывает ее в testdb33.
RESTORE DATABASE testdb33 --> renames the specified database testdb3 to testdb33.
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb3', --> original name specified here
MOVE 'testdb3' TO 'd:\temp\snap\testdb3.mdf',
MOVE 'testdb3_log' TO 'd:\temp\snap\testdb3_log.ldf',
NORECOVERY;
Использование RESTORE BACKUPSETONLY для извлечения баз данных из резервного набора, содержащего несколько баз данных
Резервный набор данных моментальных снимков, содержащий несколько баз данных из моментального снимка группы или сервера, можно разделить с помощью команды RESTORE BACKUPSETONLY. При этом создается один резервный набор для каждой базы данных.
Если моментальный снимок сервера содержит три базы данных в файле резервной копии с одним резервным набором, следующая команда создает три резервных набора, по одному для каждой базы данных. Он создает каталог с <file_name_prefix>_<unique_time_stamp> выходными файлами.
RESTORE BACKUPSETONLY
FROM DISK = 'd:\temp\db1.bkm'
WITH METADATA_ONLY;
Использование RESTORE BACKUPSETONLY для извлечения определенной базы данных в резервном наборе, содержащего несколько баз данных.
RESTORE BACKUPSETONLY поддерживает параметр DBNAME, если пользователь хочет вывести одну базу данных из трех баз данных в резервном наборе. С его помощью также поддерживается параметр FILE для фильтрации нескольких наборов резервных копий в файле резервной копии.
RESTORE BACKUPSETONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb2';
Динамические административные представления (DMV) для просмотра состояния приостановки и полученных блокировок
sys.dm_server_suspend_status (db_id, db_name, suspend_session_id, suspend_time_ms, is_diffmap_cleared, is_writeio_frozen)
sys.dm_tran_locks (resource_type, resource_database_id, resource_lock_partition, request_mode, request_type, request_status, request_owner_type, request_session_id)
Список сведений о наборе резервных копий для резервных копий моментальных снимков T-SQL
SELECT database_name,
type,
backup_size,
backup_start_date,
backup_finish_date,
is_snapshot
FROM msdb.dbo.backupset
WHERE is_snapshot = 1;
Свойства уровня сервера и базы данных для проверки того, была ли база данных приостановлена для резервного копирования моментальных снимков
SELECT SERVERPROPERTY('SuspendedDatabaseCount');
SELECT SERVERPROPERTY('IsServerSuspendedForSnapshotBackup');
SELECT DATABASEPROPERTYEX('db1', 'IsDatabaseSuspendedForSnapshotBackup');
Пример скрипта устранения неполадок T-SQL
Следующий пример скрипта T-SQL можно использовать для обнаружения приостановленных баз данных на сервере и отмены их при необходимости.
IF (SERVERPROPERTY('IsServerSuspendedForSnapshotBackup') = 1)
BEGIN
--full server suspended, requires server level thaw
PRINT 'Full server is suspended, requires server level thaw'
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = OFF
END
ELSE
BEGIN
IF (SERVERPROPERTY('SuspendedDatabaseCount') > 0)
BEGIN
DECLARE @curdb SYSNAME
DECLARE @sql NVARCHAR(500)
DECLARE mycursor CURSOR FAST_FORWARD
FOR
SELECT db_name
FROM sys.dm_server_suspend_status;
OPEN mycursor
FETCH NEXT
FROM mycursor
INTO @curdb
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'unfreezing DB ' + @curdb
SET @sql = 'ALTER DATABASE ' + @curdb + ' SET SUSPEND_FOR_SNAPSHOT_BACKUP = OFF'
EXEC sp_executesql @SQL
FETCH NEXT
FROM mycursor
INTO @curdb
END
PRINT 'All DB unfrozen'
CLOSE mycursor;
DEALLOCATE mycursor;
END
ELSE
-- no suspended database, thus no user action needed.
PRINT 'No database/server is suspended for snapshot backup'
END
См. также
Обратная связь
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделе https://aka.ms/ContentUserFeedback.
Отправить и просмотреть отзыв по