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

Статья
01/02/2025

Область применения: 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 Writer для выполнения моментальных резервных копий. Служба записи 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. С помощью этого параметра моментальные снимки можно выполнять на уровне детализации моментальных снимков базового уровня хранилища, устраняя необходимость выполнять моментальный снимок одного и того же диска несколько раз.
Выполните FULL резервное копирование и COPY_ONLY FULL резервное копирование. Эти резервные копии также записываются msdb .
Выполните восстановление на конкретный момент времени, используя резервные копии журналов, созданные стандартным методом потоковой передачи после резервного копирования снимков FULL. При необходимости также поддерживается создание разностных резервных копий в режиме потоковой передачи.

Примечание.

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

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;

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

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

ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;

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

Примечание.

Ни одна из этих команд не поддерживает приостановку системных баз данных (master, modelи msdb) для резервного копирования моментальных снимков.

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, как показано в следующих примерах.

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

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

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

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

Приостановить базу данных (что заморозит операции записи ввода-вывода как для файлов данных, так и для журналов).
```
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;
```
Снимок всех базовых дисков, в которых находятся данные базы данных и файлы журналов. Этот шаг зависит от оборудования.
Выполните резервное копирование с помощью параметра METADATA_ONLY, создающего выходные данные, содержащие метаданные моментального резервного копирования (.bkm).
```
BACKUP DATABASE testdb1
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY;
```

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

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

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

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

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

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

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;

Снимок экрана: результат SSMS из запроса в резервный набор данных.

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

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

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

Снимок экрана: результаты фильтрации выходных данных RESTORE FILELISTONLY в базу данных.

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;

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

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

В. Следите за состоянием приостановки и полученными блокировками

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

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

Поделиться через

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

Рабочий процесс

Ограничения

Примеры

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

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

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

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

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

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

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

H. Результаты резервного копирования моментального снимка с использованием RESTORE HEADERONLY

I. Результат резервного копирования снимка с помощью RESTORE FILELISTONLY

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

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

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

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

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

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

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

В. Следите за состоянием приостановки и полученными блокировками

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

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

T. Пример скрипта устранения неполадок T-SQL

Обратная связь

Дополнительные ресурсы

Поделиться через

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

Рабочий процесс

Ограничения

Примеры

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

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

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

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

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

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

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

H. Результаты резервного копирования моментального снимка с использованием RESTORE HEADERONLY

I. Результат резервного копирования снимка с помощью RESTORE FILELISTONLY

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

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

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

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

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

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

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

В. Следите за состоянием приостановки и полученными блокировками

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

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

T. Пример скрипта устранения неполадок T-SQL

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

Обратная связь

Дополнительные ресурсы