Transact-SQL 스냅샷 백업 만들기

  • 아티클

적용 대상: SQL Server 2022(16.x)

이 문서에서는 Transact-SQL 스냅샷 백업을 사용하는 방법, 이유 및 방법을 설명합니다. Transact-SQL 스냅샷 백업은 SQL Server 2022(16.x)의 새로운 기능입니다.


데이터베이스는 날이 갈수록 점점 커지고 있습니다. 일반적으로 SQL Server 백업은 스트리밍 백업입니다. 스트리밍 백업은 데이터베이스의 크기에 따라 달라집니다. 백업 작업은 백업 기간 동안 동시 OLTP 워크로드의 처리량에 영향을 미치는 리소스(CPU, 메모리, I/O, 네트워크)를 사용합니다. 데이터 크기에 영향을 받지 않고 백업 성능을 일정하게 만드는 한 가지 방법은 기본 스토리지 하드웨어 또는 서비스에서 제공하는 메커니즘을 사용하여 스냅샷 백업을 수행하는 것입니다.

백업 자체는 하드웨어 수준에서 수행되므로 순수 SQL Server 솔루션이 아닙니다. SQL Server는 파일이 나중에 복원될 수 있는 상태가 되도록 먼저 스냅샷에 대한 데이터 및 로그 파일을 준비해야 합니다. 이 작업이 완료되면 I/O가 SQL Server에서 고정되고 컨트롤이 백업 애플리케이션에 전달되어 스냅샷을 완료합니다. 스냅샷이 성공적으로 완료되면 애플리케이션이 컨트롤을 다시 SQL Server로 반환해야 합니다. 그러면 I/O가 다시 시작됩니다. 스냅샷 작업 기간 동안 I/O를 중지해야 하므로 서버의 워크로드가 장기간 중단되지 않도록 스냅샷이 신속하게 수행되어야 합니다. 과거에는 사용자가 스냅샷 백업을 완료하기 위해 SQL 기록기 서비스를 기반으로 구축된 타사 솔루션에 의존했습니다. SQL 기록기 서비스는 SQL Server VDI(가상 디바이스 인터페이스)와 함께 Windows VSS(볼륨 섀도 서비스)를 사용하여 SQL Server와 디스크 수준 스냅샷 간의 오케스트레이션을 수행합니다. SQL 기록기 서비스를 기반으로 하는 백업 클라이언트는 복잡한 경향이 있으며 Windows에서만 작동합니다. T-SQL 스냅샷 백업을 사용하면 오케스트레이션의 SQL Server 측면을 일련의 T-SQL 명령으로 처리할 수 있습니다. 이를 통해 사용자는 Windows 또는 Linux에서 실행할 수 있는 작은 백업 애플리케이션을 만들거나 기본 스토리지가 스냅샷을 시작하는 스크립팅 인터페이스를 지원하는 경우 스크립팅된 솔루션도 만들 수 있습니다.

다음은 SQL Server 2022(16.x) 이상에서 도입된 T-SQL 스냅샷 백업 기능을 사용하여 Azure SQL IaaS Virtual Machine에서 데이터베이스를 백업하고 복원하는 엔드 투 엔드 솔루션을 보여 주는 샘플 PowerShell 스크립트 입니다.

Workflow

T-SQL 스냅샷 백업 구문은 공급업체 종속 스냅샷 메커니즘을 일시 중단 및 백업 작업과 분리합니다. 이 구문을 사용하여 다음을 수행할 수 있습니다.

  1. ALTER 명령을 사용하여 데이터베이스를 동결합니다. 이는 기본 스토리지의 스냅샷을 수행할 수 있는 기회를 제공합니다. 그런 다음 데이터베이스를 재개하고 BACKUP 명령을 사용하여 스냅샷을 기록할 수 있습니다.
  2. 새 BACKUP GROUP 및 BACKUP SERVER 명령을 사용하여 여러 데이터베이스의 스냅샷을 동시에 수행합니다. 이 옵션을 사용하면 기본 스토리지의 스냅샷 세분성에서 스냅샷을 수행할 수 있으므로 동일한 디스크의 스냅샷을 여러 번 수행할 필요가 없습니다.
  3. 전체 백업을 수행하고 전체 백업을 COPY_ONLY. 이러한 백업도 기록됩니다 msdb .
  4. 스냅샷 FULL 백업 후 일반 스트리밍 접근 방식으로 수행된 로그 백업을 사용하여 지정 시간 복구를 수행합니다. 원하는 경우 스트리밍 차등 백업도 지원됩니다.

참고 항목

ALTER 명령을 사용하여 데이터베이스를 일시 중단하면 첫 번째 단계에서 차등 비트맵이 지워집니다. 스냅샷이 실패하여 또는 다른 이유로 백업을 수행하지 않고 데이터베이스를 재개하기로 결정하면 차등 비트맵이 유효하지 않게 됩니다. 따라서 차등 백업을 수행하려면 전체 데이터베이스를 검색해야 하므로 후속 차등 백업은 더 I/O 집약적입니다. 차등 비트맵은 성공적인 스냅샷 백업 후에 다시 유효해집니다.

다음 다이어그램에서는 T-SQL 스냅샷 백업의 개략적인 워크플로를 보여 줍니다.

Diagram that shows process from suspend, to snapshot, to backup.

중간 스냅샷 단계에서는 기본 스토리지에서 스냅샷을 시작해야 합니다. 다음 다이어그램은 백업 스크립트가 SQL Server와 함께 작동하여 스냅샷 백업 프로세스를 완료하는 방법의 예를 보여줍니다.

Diagram shows example of how the backup script can work with SQL Server to complete the backup process.

마찬가지로 복원 스크립트는 다음과 같이 작동할 수 있습니다.

Diagram shows how the restore script can work with SQL Server to complete the restore task from a snapshot backup.

제한 사항

이 기능을 사용하여 백업할 수 있는 최대 데이터베이스 수는 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를 사용하려면 RESTORE 명령을 실행하기 전에 사용자가 스냅샷 URI에서 탑재 지점으로 데이터베이스 파일을 복사해야 합니다. 사용자는 RESTORE DATABASE와 함께 이 스냅샷 백업 메타데이터 파일에서 RESTORE HEADERONLY, RESTORE FILELISTONLY와 같은 기존의 모든 T-SQL 명령을 실행할 수 있습니다. 이 구문은 디스크 또는 URL에 스냅샷 백업 메타데이터 작성을 지원합니다. 스냅샷 백업 세트도 스트리밍 백업 세트와 마찬가지로 단일 파일에 추가할 수 있습니다.

참고 항목

URL에 백업하는 경우 SQL Server on Windows에 대해 페이지 Blob이 지원되지만 블록 Blob을 사용하는 것이 좋습니다. SQL Server on 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;

참고 항목

이러한 명령 중 어느 것도 시스템 데이터베이스 일시 중단을 지원하지 않습니다. mastermodelmsdb

단일 명령으로 여러 사용자 데이터베이스를 일시 중단합니다

서버에 있는 모든 사용자 데이터베이스의 스냅샷을 단일 백업 세트로 기록합니다.

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;

참고 항목

스냅샷 백업을 위해 데이터베이스를 일시 중단할 때 이미 지정되었으므로 BACKUP 명령에서 COPY_ONLY 사용할 필요가 없습니다.

백업 세트에 태그 지정

백업 명령에서 MEDIANAME 및 MEDIADESCRIPTION 옵션을 사용하여 스냅샷과 연결된 URI에 태그를 지정할 수 있습니다. 이렇게 사용하면 백업 파일이 데이터베이스 메타데이터와 함께 기본 스냅샷 정보를 전달할 수 있습니다. NAME 및 DESCRIPTION 옵션을 사용하여 개별 백업 세트 스냅샷으로 URI에 태그를 지정할 수도 있습니다.

SQL Server는 LABEL 정보를 어떤 방식으로도 해석하지 않습니다. 그러나 사용자가 RESTORE LABELONLY 명령을 사용하여 스냅샷 백업과 연결된 URI를 보는 데 도움이 됩니다.

그런 다음 URI에 있는 스냅샷 디스크를 VM에 연결하여 스냅샷을 복원할 수 있습니다. MEDIANAME 및 MEDIADESCRIPTION에 저장된 스냅샷 URI는 이후에 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를 사용하여 여러 백업 집합에서 특정 백업 집합을 구체적으로 선택하려면 RESTORE FILELISTONLY에서 이미 지원되는 FILE 절을 사용합니다.

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

Screenshot of SSMS output to backups set from query.

데이터베이스에 RESTORE FILELISTONLY 출력 필터링

필터링 RESTORE FILELISTONLY를 사용하여 선택한 백업 세트 내의 여러 데이터베이스에서 단일 데이터베이스를 선택하려면 FILE 절을 새로 도입된 DBNAME 절과 함께 사용합니다. DBNAME 절은 스냅샷 백업 세트에서만 사용할 수 있습니다.

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

Screenshot of results of filtering RESTORE FILELISTONLY output to a database.

스냅샷 데이터베이스 복원

스냅샷 백업에서 데이터베이스를 복원하는 것은 데이터베이스를 연결하는 것과 같습니다. 복구 없이 데이터베이스를 연결해야 하는 경우 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 명령을 사용하여 분할할 수 있습니다. 이렇게 하면 데이터베이스당 하나의 백업 집합이 생성됩니다.

서버 스냅샷에 단일 백업 집합이 포함된 백업 파일에 데이터베이스 3개가 포함된 경우 다음 명령은 각 데이터베이스에 대해 하나씩 3개의 백업 집합을 생성합니다. 출력 파일에 대한 디렉터리를 <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

참고 항목