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가 먼저 스냅샷을 위해 데이터 및 로그 파일을 준비해야 파일을 나중에 복원할 수 있는 상태가 보장됩니다. 이 작업이 완료되면 SQL Server에서 쓰기 작업이 일시 중지되고(읽기 요청은 계속 허용됨) 스냅샷을 완료하기 위해 컨트롤이 백업 애플리케이션으로 넘어갑니다. 스냅샷이 성공적으로 완료되면 애플리케이션이 컨트롤을 다시 SQL Server로 반환해야 합니다. 그러면 쓰기 작업이 다시 시작됩니다. 스냅샷 작업 기간 동안 쓰기 작업을 동결해야 하므로 서버의 워크로드가 장시간 중단되지 않도록 스냅샷을 빠르게 실행하는 것이 중요합니다. 과거에는 사용자가 스냅샷 백업을 완료하기 위해 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 가상 머신에서 데이터베이스를 백업하고 복원하는 엔드투엔드 솔루션을 보여 주는 샘플 PowerShell 스크립트입니다.
워크플로
T-SQL 스냅샷 백업 구문은 공급업체 종속 스냅샷 메커니즘을 일시 중단 및 백업 작업과 분리합니다. 이 구문을 사용하여 다음을 수행할 수 있습니다.
- ALTER 명령을 사용하여 데이터베이스를 동결합니다. 이는 기본 스토리지의 스냅샷을 수행할 수 있는 기회를 제공합니다. 그런 다음 데이터베이스를 재개하고 BACKUP 명령을 사용하여 스냅샷을 기록할 수 있습니다.
- 새 BACKUP GROUP 및 BACKUP SERVER 명령을 사용하여 여러 데이터베이스의 스냅샷을 동시에 수행합니다. 이 옵션을 사용하면 기본 스토리지의 스냅샷 세분성에서 스냅샷을 수행할 수 있으며 동일한 디스크의 스냅샷을 여러 번 수행할 필요가 없습니다.
- FULL 백업과 COPY_ONLY FULL 백업을 수행합니다. 이러한 백업은
msdb
에도 기록됩니다. - 스냅샷 FULL 백업 후 일반 스트리밍 접근 방식으로 수행된 로그 백업을 사용하여 지정 시간 복구를 수행합니다. 원하는 경우 스트리밍 차등 백업도 지원됩니다.
참고 항목
ALTER 명령을 사용하여 데이터베이스를 일시 중단하면 첫 번째 단계에서 차등 비트맵이 지워집니다. 스냅샷이 실패하여 또는 다른 이유로 백업을 수행하지 않고 데이터베이스를 재개하기로 결정하면 차등 비트맵이 유효하지 않게 됩니다. 따라서 차등 백업을 수행하려면 전체 데이터베이스를 검색해야 하므로 후속 차등 백업은 더 I/O 집약적입니다. 차등 비트맵은 성공적인 스냅샷 백업 후에 다시 유효해집니다.
다음 다이어그램에서는 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 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;
참고 항목
이러한 명령 중 어느 것도 스냅샷 백업을 위해 master
, model
및 msdb
와 같은 시스템 데이터베이스 일시 중지를 지원하지 않습니다.
단일 명령으로 여러 사용자 데이터베이스를 일시 중단합니다
서버에 있는 모든 사용자 데이터베이스의 스냅샷을 단일 백업 세트로 기록합니다.
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;
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 명령을 사용하여 분할할 수 있습니다. 그러면 데이터베이스당 하나의 백업 세트가 생성됩니다.
서버 스냅샷이 단일 백업 세트가 포함된 백업 파일에 3개의 데이터베이스를 포함하고 있는 경우 다음 명령은 각 데이터베이스에 대해 하나씩 3개의 백업 세트를 생성합니다. 또한 출력 파일에 대해 <file_name_prefix>_<unique_time_stamp>
로 디렉터리를 만듭니다.
RESTORE BACKUPSETONLY
FROM DISK = 'd:\temp\db1.bkm'
WITH METADATA_ONLY;
RESTORE BACKUPSETONLY를 사용하여 여러 데이터베이스가 포함된 백업 세트에서 특정 데이터베이스 추출
사용자가 백업 세트의 3개 데이터베이스 중 하나를 출력하려는 경우 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