Создание резервной копии моментальных снимков Transact-SQL

Область применения: SQL Server 2022 (16.x)

В этой статье объясняется, почему и как использовать резервные копии моментальных снимков Transact-SQL. Резервные копии моментальных снимков Transact-SQL (T-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 отделяет зависящий от поставщика механизм моментальных снимков от операций приостановки и резервного копирования. С помощью этого синтаксиса можно сделать следующее:

1. Заморозите базу данных с ALTER помощью команды, которая предоставляет возможность выполнить моментальный снимок базового хранилища. После этого можно оттаить базу данных и записать моментальный снимок с BACKUP помощью команды.

2. Выполнение моментальных снимков нескольких баз данных одновременно с новыми BACKUP GROUP и BACKUP SERVER командами. С помощью этого параметра моментальные снимки можно выполнять при детализации моментального снимка базового хранилища, устраняя необходимость выполнения моментального снимка одного диска несколько раз.

3. Выполнение FULL резервных копий и COPY_ONLY FULL резервных копий. Эти резервные копии также записываются msdb .

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

На следующей диаграмме показан высокоуровневый рабочий процесс резервного копирования моментальных снимков 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. Синтаксис поддерживает запись метаданных резервного копирования моментальных снимков в DISK или URL. Резервные наборы данных моментальных снимков также могут быть добавлены аналогично резервным наборам данных потоковой передачи.

А. Приостановка отдельной пользовательской базы данных для резервного копирования моментальных снимков и запись резервной копии базы данных

ALTER DATABASE testdb1
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;

BACKUP DATABASE testdb1
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FORMAT;

B. Приостановка нескольких пользовательских баз данных для резервного копирования моментальных снимков

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

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;

C. Приостановка всех пользовательских баз данных на сервере для резервного копирования моментальных снимков

Если все пользовательские базы данных на сервере должны быть приостановлены, используйте следующую команду.

ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;

BACKUP SERVER
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FORMAT;

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

Запишите моментальный снимок всех пользовательских баз данных на сервере в одном резервном наборе:

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;

Е. Выполнение резервных копий моментальных снимков только для копирования

Так как разностная растровая карта очищается до заморозки, 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;

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

Если у вас есть база данных с файлами данных (.mdf и .ndf) на нескольких дисках, а также файл журнала транзакций (.ldf) на другом диске, можно выполнить резервное копирование моментальных снимков следующим образом:

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

   ALTER SERVER CONFIGURATION
   SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;
   

2. Снимок всех базовых дисков, в которых находятся данные базы данных и файлы журналов. Этот шаг зависит от оборудования.

3. Выполните резервное копирование с помощью METADATA_ONLY параметра, создающего выходные данные, содержащие метаданные резервного копирования моментальных снимков (.bkm).

   BACKUP DATABASE testdb1
   TO DISK = 'D:\Temp\db.bkm'
   WITH METADATA_ONLY;
   

Чтобы восстановить эту резервную копию на более позднем этапе, выполните следующие действия.

1. Подключите или подключите диски моментальных снимков на виртуальной машине, где требуется восстановить.

2. .bkm При восстановлении базы данных используйте файл (из шага 3 в предыдущем списке).

3. Если диски отличаются во время восстановления, используйте MOVE параметр для логических файлов, чтобы поместить их в нужное место назначения. Пример см . в примере N.

G. Тег набора резервных копий

Вы можете использовать MEDIANAME параметры и MEDIADESCRIPTION параметры в команде резервного копирования для тега URI, связанного с моментальным снимком. Это позволяет файлу резервного копирования передавать базовые сведения моментального снимка вместе с метаданными базы данных. Кроме того, можно использовать NAME параметры и DESCRIPTION параметры для тега URI с помощью отдельного моментального снимка набора резервных копий.

SQL Server не интерпретирует LABEL информацию каким-либо образом. Однако он помогает пользователю просматривать URI, связанный с резервной копией моментальных снимков с RESTORE LABELONLY помощью команды.

Затем можно подключить диски моментальных снимков, расположенные в URI, к виртуальной машине, чтобы восстановить моментальный снимок. URI моментального снимка, хранящийся в и MEDIANAME MEDIADESCRIPTION затем доступен для просмотра в msdb таблице dbo.backupmediasetбазы данных.

H. Выходные данные резервного копирования моментальных снимков с помощью RESTORE HEADERONLY

Выходные данные со RESTORE HEADERONLY следующим примером, если база данных, группа и сервер выполняются последовательно и записываются в тот же выходной файл:

RESTORE HEADERONLY
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY;

I. Выходные данные резервного копирования моментальных снимков с помощью RESTORE FILELISTONLY

Выходные данные с RESTORE FILELISTONLY первым резервным набором по умолчанию:

RESTORE FILELISTONLY
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY;

J. Фильтрация выходных данных RESTORE FILELISTONLY в резервный набор данных

Чтобы выбрать определенный резервный набор из нескольких резервных наборов RESTORE FILELISTONLY, используйте FILE предложение, которое уже поддерживается RESTORE FILELISTONLY.

RESTORE FILELISTONLY
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FILE = 3;

K. Фильтрация выходных данных RESTORE FILELISTONLY в базу данных

Чтобы дополнительно выбрать одну базу данных из нескольких баз данных в выбранном резервном наборе RESTORE FILELISTONLY, используйте FILE предложение с предложением DBNAME . Предложение DBNAME можно использовать только в резервных наборах моментальных снимков.

RESTORE FILELISTONLY
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb3';

L. Восстановление базы данных моментальных снимков

Восстановление базы данных из резервной копии моментальных снимков похоже на подключение базы данных. Выполните команду восстановления без 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';

M. Восстановление базы данных моментальных снимков, указанной посередине

Если база данных, которая должна находиться RESTORED в середине, укажите базу данных, которую необходимо восстановить с 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;

О. Восстановление базы данных под другим именем

Базу данных можно восстановить под другим именем. Если база данных, которая должна находиться RESTORED в середине, укажите базу данных, которую необходимо восстановить с 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;

O. Использование RESTORE BACKUPSETONLY для извлечения баз данных из резервного набора, содержащего несколько баз данных

Резервный набор моментальных снимков, содержащий несколько баз данных из группы или моментального снимка сервера, можно разделить с RESTORE BACKUPSETONLY помощью команды. Эта команда создает один резервный набор для каждой базы данных.

Если моментальный снимок сервера содержит три базы данных в файле резервной копии с одним резервным набором, следующая команда создает три резервных набора, по одному для каждой базы данных. Он создает каталог с <file_name_prefix>_<unique_time_stamp> выходными файлами.

RESTORE BACKUPSETONLY
FROM DISK = 'D:\Temp\db1.bkm'
WITH METADATA_ONLY;

P. Использование RESTORE BACKUPSETONLY для извлечения определенной базы данных в резервном наборе, содержащего несколько баз данных.

RESTORE BACKUPSETONLY поддерживает параметр, DBNAME если пользователь хочет вывести одну базу данных из трех баз данных в резервном наборе. Он также поддерживает FILE параметр для фильтрации нескольких резервных наборов в файле резервной копии.

RESTORE BACKUPSETONLY
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb2';

В. Мониторинг состояния приостановки и блокировки, приобретенных

Вы можете использовать следующие динамические административные представления (динамические административные представления):

• sys.dm_server_suspend_status (просмотр состояния приостановки)
• sys.dm_tran_locks (просмотр приобретенных блокировок)

R. Список сведений о наборе резервных копий

В следующем примере скрипта перечислены сведения о наборе резервных копий для резервных копий моментальных снимков Transact-SQL.

SELECT database_name,
    type,
    backup_size,
    backup_start_date,
    backup_finish_date,
    is_snapshot
FROM msdb.dbo.backupset
WHERE is_snapshot = 1;

S. Проверьте, приостановлена ли база данных для резервного копирования моментальных снимков

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

SELECT SERVERPROPERTY('SuspendedDatabaseCount');
SELECT SERVERPROPERTY('IsServerSuspendedForSnapshotBackup');
SELECT DATABASEPROPERTYEX('db1', 'IsDatabaseSuspendedForSnapshotBackup');

T. Пример скрипта устранения неполадок 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

Связанный контент

• backupmediaset (Transact-SQL)
• BACKUP (Transact-SQL)