SQL Server 백업 및 복원 작업 문제 해결

원래 제품 버전: SQL Server
원래 KB 번호: 224071

요약

이 문서는 일반적인 SQL Server 백업 및 복원 작업 문제를 해결하는 데 도움이 됩니다. 이러한 문제에는 느린 백업 또는 복원 성능, 버전 호환성 오류, Always On 가용성 그룹 백업 작업, 미디어 오류, 권한 오류, 타사 VDI 및 VSS 백업, 변경 내용 추적 오류 및 암호화된 데이터베이스 복원이 포함됩니다. 이 문서에는 자주 묻는 질문 섹션과 SQL Server 백업 및 복원에 대한 참조 항목에 대한 링크도 포함되어 있습니다.

백업 및 복원 작업에 오랜 시간이 소요됩니다.

백업 및 복원 작업 시 I/O가 많이 발생합니다. 백업 및 복원 처리량은 기본 I/O 하위 시스템이 I/O 볼륨을 처리하도록 얼마나 잘 최적화되었는지에 따라 달라집니다. 백업 작업이 중지되거나 완료하는 데 너무 오래 걸리는 것으로 의심되는 경우 다음 방법 중 하나 이상을 사용하여 완료 시간을 예측하거나 백업 또는 복원 작업의 진행률을 추적합니다.

• SQL Server 오류 로그에는 이전 백업 및 복원 작업에 대한 정보가 포함되어 있습니다. 이러한 세부 정보를 사용하여 데이터베이스를 현재 상태로 백업하고 복원하는 데 필요한 시간을 예측할 수 있습니다. 다음은 오류 로그의 샘플 출력입니다.

  RESTORE DATABASE successfully processed 315 pages in 0.372 seconds (6.604 MB/sec)
  

• SQL Server 2016 이상 버전에서는 XEvent backup_restore_progress_trace를 사용하여 백업 및 복원 작업의 진행률을 추적합니다.

• percent_complete sys.dm_exec_requests 열을 사용하여 진행 중인 백업 및 복원 작업의 진행률을 추적합니다.

• 성능 모니터 카운터를 사용하여 Device throughput Bytes/sec 백업 및 Backup/Restore throughput/sec 복원 처리량을 측정합니다. 자세한 내용은 SQL Server, Backup 디바이스 개체를 참조 하세요.

• estimate_backup_restore 스크립트를 사용하여 예상 백업 시간을 가져옵니다.

• 작동 방법: 복원/백업이란?을 참조하세요. 이 블로그 게시물은 백업 또는 복원 작업의 현재 단계에 대한 인사이트를 제공합니다.

느린 백업 또는 복원 성능 조사

1. 아래 표의 알려진 문제를 겪고 있는지 확인하고, 해당 해결 방법이나 모범 사례를 적용해 보세요.

   기술 자료 링크	설명 및 권장 작업
   SQL Server 데이터베이스 백업 및 복원	백업 및 복원 성능을 향상시킬 수 있는 모범 사례를 다룹니다. 예를 들어 SQL Server 실행하는 Windows 계정에 SE_MANAGE_VOLUME_NAME 권한을 부여하여 즉시 파일 초기화로 데이터 파일 작업의 속도를 높일 수 있습니다.
   SQL Server에서 작동하도록 바이러스 백신 소프트웨어 구성	바이러스 백신 소프트웨어는 백업 및 복원 작업의 성능에 영향을 줄 수 있는 파일에 대한 .bak 잠금을 보유할 수 있습니다. 이 문서의 지침에 따라 바이러스 검사에서 백업 파일을 제외합니다.
   네트워크 위치에 대한 백업 또는 복원 작업이 느림	비슷한 크기의 파일을 SQL Server 실행하는 서버에서 네트워크 위치에 복사하여 네트워크에 문제를 격리하고 성능을 확인합니다.

2. SQL Server 오류 로그 및 Windows 이벤트 로그에서 문제의 원인을 가리키는 오류 메시지를 확인합니다.

3. 타사 소프트웨어 또는 데이터베이스 유지 관리 계획을 사용하여 동시 백업을 수행하는 경우 백업이 기록된 드라이브에서 경합을 최소화하도록 일정을 변경하는 것이 좋습니다.

4. Windows 관리자와 협력하여 하드웨어에 대한 펌웨어 업데이트를 확인합니다.

이전 SQL Server 버전으로 백업을 복원할 때 발생하는 오류

Symptoms

백업을 만든 버전보다 이전 버전의 SQL Server SQL Server 백업을 복원할 수 없습니다. 예를 들어 SQL Server 2022 인스턴스에서 수행된 백업을 SQL Server 2019 인스턴스로 복원할 수 없습니다. 그렇지 않으면 다음 오류 메시지가 나타납니다.

오류 3169: 버전 %ls을(를) 실행하는 서버에서 데이터베이스가 백업되었습니다. 해당 버전은 버전 %ls을(를) 실행하는 이 서버와 호환되지 않습니다. 백업을 지원하는 서버에서 데이터베이스를 복원하거나 이 서버와 호환되는 백업을 사용합니다.

해결 방법

다음 메서드를 사용하여 SQL Server 최신 버전에서 호스트되는 데이터베이스를 이전 버전의 SQL Server 복사합니다.

1. SQL_A 및 SQL_B에 최신 버전의 SSMS(SQL Server Management Studio)를 다운로드하여 설치합니다.
2. SQL_A에서 다음 단계를 따르세요:
   1. < > > >을 마우스 오른쪽 단추로 클릭하고 전체 데이터베이스 및 모든 데이터베이스 개체를 스크립트하는 옵션을 선택합니다.
   2. 스크립팅 옵션 설정 화면에서 고급을 선택한 다음, 일반>SQL Server 버전용 스크립트 아래에서 SQL_B 버전을 선택합니다. 그런 다음 가장 적합한 저장 옵션을 선택하고 마법사를 계속합니다.
   3. bcp(대량 복사 프로그램 유틸리티)를 사용하여 다른 테이블에서 데이터를 복사합니다.
3. SQL_B에서 다음 단계를 따르세요:
   1. SQL_A 서버에서 생성된 스크립트를 사용하여 데이터베이스 스키마를 만듭니다.
   2. 각 테이블에서 외래 키 제약 조건 및 트리거를 사용하지 않도록 설정합니다. 테이블에 ID 열이 있는 경우 ID 삽입을 사용하도록 설정합니다.
   3. bcp를 사용하여 이전 단계에서 내보낸 데이터를 해당 테이블로 가져옵니다.
   4. 데이터 가져오기가 완료되면 외래 키 제약 조건 및 트리거를 사용하도록 설정하고 c단계에서 변경된 각 테이블에 대해 ID 삽입을 사용하지 않도록 설정합니다.

이 절차는 일반적으로 중소 규모의 데이터베이스에 적합합니다. 더 큰 데이터베이스의 경우 SSMS 및 기타 도구에서 메모리 부족 문제가 발생할 수 있습니다. SQL Server Integration Services(SSIS), 복제 또는 기타 옵션을 사용하여 이후 버전에서 이전 버전의 SQL Server 데이터베이스를 복사하는 것이 좋습니다.

데이터베이스용 스크립트를 생성하는 방법에 대한 자세한 내용은 스크립트 생성 옵션을 사용하여 데이터베이스 스크립팅을 참조하세요.

Always On 가용성 그룹의 백업 작업 문제

Symptoms

Always On 가용성 그룹 환경에서 백업 작업 또는 유지 관리 계획에 영향을 주는 문제가 발생합니다.

해결 방법

• 기본적으로 자동 백업 기본 설정은 보조를 선호하도록 설정됩니다. 이 설정은 주 복제본이 온라인의 유일한 복제본이 아닌 한 보조 복제본에서 백업이 수행되도록 지정합니다. 이 설정을 사용하여 데이터베이스의 차등 백업을 수행할 수 없습니다. 이 설정을 변경하려면 현재 주 복제본에서 SSMS를 사용하고 가용성 그룹의 속성 아래에 있는 백업 기본 설정 페이지로 이동합니다.
• 유지 관리 계획 또는 예약된 작업을 사용하여 데이터베이스의 백업을 생성하는 경우 가용성 그룹에 대한 가용성 복제본을 호스팅하는 모든 서버 인스턴스에서 각 가용성 데이터베이스에 대한 작업을 만듭니다.

Always On 환경의 백업에 대한 자세한 내용은 다음 문서를 참조하세요.

• Always On 가용성 그룹의 보조 복제본에 대한 백업 구성
• 지원되고 있는 백업을 가용성 그룹의 보조 복제본으로 오프로드

백업에서 데이터베이스를 복원할 때 미디어 오류 발생

Symptoms

파일 문제를 나타내는 오류 메시지는 일반적으로 손상된 백업 파일을 가리킵니다. 다음 오류는 백업 세트가 손상된 경우 발생할 수 있는 문제의 예입니다.

3241: 디바이스 '%ls'의 미디어 패밀리가 잘못 구성되었습니다. SQL Server는 이 미디어 패밀리를 처리할 수 없습니다.

3242: 디바이스 '%ls'의 파일이 유효한 Microsoft 테이프 형식 백업 세트가 아닙니다.

3243: 디바이스 '%ls'의 미디어 패밀리는 Microsoft Tape Format 버전 %d.%d(으)로 생성되었습니다. SQL Server는 버전 %d.%d을(를) 지원합니다.

원인

이러한 문제는 기본 하드웨어(하드 디스크, 네트워크 스토리지 등)에 영향을 주거나 바이러스 또는 맬웨어로 인해 발생할 수 있습니다. 보고된 오류에 대한 Windows 시스템 이벤트 로그 및 하드웨어 로그를 검토하고 적절한 조치(예: 펌웨어 업그레이드 또는 네트워킹 문제 해결)를 수행합니다.

해결 방법

• RESTORE HEADERONLY 문을 사용하여 백업을 확인합니다.
• 이러한 복원 오류 발생을 줄이려면 손상된 데이터베이스를 백업하지 않도록 백업을 실행할 때 BACKUP CHECKSUM 옵션을 사용하도록 설정합니다. 자세한 내용은 백업 및 복원 중 발생 가능한 미디어 오류(SQL Server)를 참조하세요.
• 백업 도구를 사용하여 백업을 실행할 때 추적 플래그 3023을 사용하도록 설정하여 체크섬을 사용하도록 설정할 수도 있습니다. 자세한 내용은 서버 구성: 백업 체크섬 기본값을 참조하세요.
• 이러한 문제를 해결하려면 사용 가능한 다른 백업 파일을 찾거나 새 백업 세트를 만듭니다. Microsoft는 손상된 백업 세트에서 데이터를 검색하는 데 도움이 되는 솔루션을 제공하지 않습니다.
• 백업 파일이 한 서버에서 성공적으로 복원되지만 다른 서버에서는 복원되지 않는 경우 서버 간에 파일을 복사하는 다른 방법을 시도해 보세요. 예를 들어 일반 복사 작업 대신 robocopy를 사용해봅니다. 네트워크 또는 대상 스토리지 디바이스에서 복사 작업 중에 파일이 변경되었는지 여부를 조사합니다.

권한 문제로 인해 백업이 실패합니다.

Symptoms

데이터베이스 백업 작업을 실행하려고 하면 다음 오류 중 하나가 발생합니다.

• 시나리오 1: SQL Server Management Studio에서 백업을 실행하면 백업이 실패하고 다음 오류 메시지가 반환됩니다.

  서버 <이름>에 대한 백업이 실패했습니다. (Microsoft.SqlServer.SmoExtended)
  System.Data.SqlClient.SqlError: 백업 디바이스 '디바이스 이름<'>을 열 수 없습니다. 운영 체제 오류 5(액세스가 거부됨). (Microsoft.SqlServer.Smo)

• 시나리오 2: 예약된 백업이 실패하고 실패한 작업의 작업 기록에 기록되고 다음과 유사한 오류 메시지를 생성합니다.

  Executed as user: <Owner of the job>. ....2 for 64-bit  Copyright (C) 2019 Microsoft. All rights reserved.    
  Started:  5:49:14 PM  Progress: 2021-08-16 17:49:15.47    
  Source: {GUID}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% complete  End Progress  
  Error: 2021-08-16 17:49:15.74     
  Code: 0xC002F210     
  Source: Back Up Database (Full) Execute SQL Task     
  Description: Executing the query "EXECUTE master.dbo.xp_create_subdir N'C:\backups\D..." failed with the following error: "xp_create_subdir() returned error 5, 'Access is denied.'". 
  Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
  

원인

SQL Server 서비스 계정에 백업이 기록된 폴더에 대한 읽기 및 쓰기 권한이 없는 경우 두 시나리오가 모두 발생할 수 있습니다. Backup 문은 작업 단계의 일부로 또는 SQL Server Management Studio 수동으로 실행할 수 있습니다. 두 경우 모두 SQL Server 서비스 시작 계정의 컨텍스트에서 실행됩니다. 따라서 서비스 계정에 필요한 권한이 없으면 앞에서 적어 두는 오류 메시지가 표시됩니다.

해결 방법

폴더 속성에서 Security 탭으로 이동한 다음 고급을 선택하고 Effective Access 탭을 사용하여 폴더에 대한 SQL Server 서비스 계정의 현재 권한을 확인합니다. 자세한 내용은 백업 디바이스를 참조하십시오.

타사 백업 또는 복원 작업 실패

SQL Server VDI(가상 백업 디바이스 인터페이스)를 제공합니다. 이 API를 통해 독립 소프트웨어 공급업체는 SQL Server 제품에 통합하여 백업 및 복원 작업을 지원할 수 있습니다. 이러한 API는 안정성과 성능을 제공하고 스냅샷 및 핫 백업 기능을 포함하여 모든 범위의 SQL Server 백업 및 복원 기능을 지원하도록 설계되었습니다.

일반적인 문제 해결 단계

• 지원되는 모든 버전의 SQL Server 설치 중에 NT SERVICE\SQLWriter라는 로그인이 생성되고 프로비전됩니다. 이 로그인이 SQL Server 있고 백업 중인 인스턴스에서 sysadmin 서버 역할의 일부인지 확인합니다. 또한 SQL Server VSS 기록기 서비스가 시작되고 시작 계정이 Local System로 설정되어 있는지 확인합니다.

• SQL Server 실행하는 서버의 관리자 권한 명령 프롬프트에서 SqlServerWriter 명령을 실행할 때 VSSADMIN LIST WRITERS 나열되어 있는지 확인합니다. 기록기가 있어야 하고 VSS 백업이 성공적으로 완료되려면 안정적인 상태여야 합니다.

• 자세한 내용은 백업 소프트웨어 및 공급업체의 지원 사이트에서 로그를 확인하세요.

  증상 또는 시나리오	참조
  VDI 백업 작동 방식 이해	작동 방식: SQL Server - VDI(VSS) 백업 리소스
  동시에 백업할 수 있는 데이터베이스 수	작동 방식: 동시에 백업할 수 있는 데이터베이스 수는 몇 개입니까?

변경 내용 추적을 사용하도록 설정하면 백업이 실패합니다.

Symptoms

데이터베이스에서 변경 내용 추적을 사용하도록 설정하면 백업이 실패할 수 있습니다. 다음과 같은 오류가 표시될 수 있습니다.

오류: 3999, 심각도: 17, 상태: 1.
<Time Stamp> spid <spid> 오류 2601로 인해 dbid 8의 디스크에 커밋 테이블을 플러시하지 못했습니다. 자세한 내용은 오류 로그를 확인하세요.

해결 방법

지원되는 SQL Server 버전에서 이 문제가 발생하는 경우 버전에 대한 최신 누적 업데이트를 설치합니다. 배경 및 기록 수정 사항은 다음 문서를 참조하세요.

• 2682488 수정: 변경 내용 추적을 사용하도록 설정한 후 SQL Server 2008, SQL Server 2008 R2 또는 SQL Server 2012 데이터베이스에서 백업 작업이 실패함

암호화된 데이터베이스의 백업을 복원할 때 오류 발생

Symptoms

TDE(투명한 데이터 암호화)로 보호되는 데이터베이스의 백업을 복원할 때 문제가 발생합니다.

해결 방법

이 문제를 해결하려면 TDE로 보호된 데이터베이스를 다른 SQL Server로 이동을 참고하세요.

SQL Server 백업 및 복원에 대한 질문과 대답

백업 작업의 상태를 확인하려면 어떻게 해야 하나요?

estimate_backup_restore 스크립트를 사용하여 백업 시간을 예측합니다.

SQL Server에 백업 도중 장애 조치가 발생하면 어떻게 해야 하나요?

중단된 복원 작업 다시 시작(Transact-SQL)에 따라 복원 또는 백업 작업을 다시 시작하십시오.

이전 버전의 최신 버전에서 데이터베이스 백업을 복원할 수 있으며 그 반대의 경우도 마찬가지입니까?

백업을 만든 버전보다 이전 버전의 SQL Server 사용하여 SQL Server 백업을 복원할 수 없습니다. 자세한 내용은 RESTORE 호환성 지원을 참조하세요.

SQL Server 데이터베이스 백업을 확인하려면 어떻게 해야 하나요?

RESTORE 문 - VERIFYONLY (Transact-SQL) 설명된 절차를 참조하세요.

SQL Server에서 데이터베이스의 백업 기록을 얻으려면 어떻게 해야 하나요?

SQL Server에서 데이터베이스의 백업 기록을 가져오는 방법을 참조하세요.

64비트 서버에서 32비트 백업을 복원할 수 있나요? 그 반대의 경우도 마찬가지인가요?

예. SQL Server 디스크 스토리지 형식은 64비트 및 32비트 환경에서 동일합니다. 따라서 백업 및 복원 작업은 64비트 및 32비트 환경에서 작동합니다.

TDE(투명한 데이터 암호화)로 보호되는 데이터베이스를 백업하고 복원하려면 어떻게 해야 하나요?

데이터베이스, 데이터베이스 암호화 키의 서버 인증서 및 인증서의 프라이빗 키를 백업합니다. 다른 인스턴스에서 백업을 복원하려면 먼저 서버 인증서(프라이빗 키 포함)를 대상 인스턴스의 master 데이터베이스로 복원한 다음 사용자 데이터베이스 백업을 복원합니다. 단계별 지침은 TDE로 보호된 데이터베이스를 다른 SQL Server로 이동을(를) 참조하세요.

백업 압축은 TDE 사용 데이터베이스에서 작동하나요?

예. SQL Server 2016부터는 BACKUP 문에서 MAXTRANSFERSIZE에 65536(64 KB)보다 큰 값을 지정하면 TDE가 사용하도록 설정된 데이터베이스에서 백업 압축이 작동합니다. 이 설정이 없으면 압축을 요청하는 경우에도 백업이 압축되지 않고 실행됩니다. 자세한 내용은 Backup 압축을 참조하세요.

VDI 및 VSS 백업은 Always On 가용성 그룹 보조 복제본과 어떻게 상호 작용하나요?

SQL 기록기 서비스를 통해 수행된 VSS 기반(스냅샷) 백업은 주 복제본에 대해서만 지원됩니다. 보조 복제본의 경우 보조 복제본에 대한 VSS 전체 백업이 지원되지 않으므로 VDI 클라이언트를 통해 복사 전용 전체 백업을 요청합니다. 자세한 내용은 활성 보조 복제본: 보조 복제본의 백업(Always On 가용성 그룹)을 참조하세요.

일반 문제 해결 팁

• 백업을 작성하는 폴더의 SQL Server 서비스 계정에 Read 및 Write 권한을 부여합니다. 자세한 내용은 백업 권한을 참조하세요.
• 백업을 작성하는 폴더에 데이터베이스 백업을 위한 충분한 공간이 있는지 확인합니다. sp_spaceused 저장 프로시저를 사용하여 데이터베이스의 백업 크기에 대한 대략적인 예상을 가져옵니다.
• 최신 버전의 SSMS를 사용하여 작업 및 유지 관리 계획 구성과 관련된 알려진 문제를 방지합니다.
• 작업을 테스트 실행하여 백업이 성공적으로 생성되었는지 확인합니다. 백업을 확인하는 논리를 추가합니다.
• 시스템 데이터베이스를 한 서버에서 다른 서버로 이동하려는 경우 시스템 데이터베이스 이동을 검토합니다.
• 간헐적인 백업 오류가 표시되는 경우 SQL Server 버전의 최신 업데이트가 문제를 해결하는지 확인합니다. 자세한 내용은 SQL Server 버전 및 업데이트 참조하세요.
• SQL Server Express 버전에 대한 백업을 예약하고 자동화하려면 SQL Server Express 참조하세요.

SQL Server 백업 및 복원에 대한 참조 항목

다음 표에서는 특정 백업 및 복원 작업을 검토할 항목을 나열합니다.

관련 콘텐츠

• SQL Server 데이터베이스 백업 및 복원
• 백업에 대한 권한
• 백업 및 복원 중 발생 가능한 미디어 오류(SQL Server)
• 다른 SQL Server로 TDE 보호 데이터베이스 이동