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

이 문서에서는 Microsoft SQL Server 백업 및 복원 작업 중에 발생할 수 있는 일반적인 문제에 대한 솔루션을 제공하고 이러한 작업에 대한 추가 정보에 대한 참조를 제공합니다.

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

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

백업 및 복원 작업 시 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 사용하여 백업 및 복원 작업의 진행률을 추적할 수 있습니다.

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

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

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

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

검사 항목

1. 다음 표에 나열된 알려진 문제가 있는지 확인합니다. 해당 문서에서 설명하는 수정 사항 및 모범 사례를 적용하거나 변경 내용을 구현해야 하는지 여부를 고려합니다.

   기술 자료 또는 온라인 설명서 링크	설명 및 권장 작업
   SQL Server에서 백업 및 복원 성능 최적화	온라인 설명서 항목에서는 백업/복원 작업의 성능을 향상시키는 데 사용할 수 있는 다양한 모범 사례를 다룹니다. 예를 들어 SQL Server를 SE_MANAGE_VOLUME_NAME 실행하는 Windows 계정에 특별한 권한을 할당하여 데이터 파일을 즉시 초기화할 수 있습니다. 이렇게 하면 상당한 성능 향상을 얻을 수 있습니다.
   2920151 Windows Server 2012 R2 기반 장애 조치(failover) 클러스터에 권장되는 핫픽스 및 업데이트 2822241 Windows 8 및 Windows Server 2012 업데이트 롤업: 2013년 4월	현재 시스템 롤업에는 SQL Server와 같은 프로그램의 성능을 저하시킬 수 있는 시스템 수준에서 알려진 문제에 대한 수정 사항이 포함될 수 있습니다. 이러한 업데이트를 설치하면 이러한 문제를 방지하는 데 도움이 될 수 있습니다.
   2878182 수정: Windows Server 2012를 실행하는 서버에서는 애플리케이션의 사용자 모드 프로세스가 응답하지 않습니다.	백업 작업은 I/O 집약적이며 이 버그의 영향을 받을 수 있습니다. 이러한 문제를 방지하려면 이 수정 사항을 적용합니다.
   SQL Server에서 작동하도록 바이러스 백신 소프트웨어 구성	바이러스 백신 소프트웨어는 .bak 파일에 대한 잠금을 보유할 수 있습니다. 이는 백업 및 복원 작업의 성능에 영향을 줄 수 있습니다. 이 문서의 지침에 따라 바이러스 검사에서 백업 파일을 제외합니다.
   2820470 Windows에 더 이상 존재하지 않는 공유 폴더에 액세스하려고 할 때 지연된 오류 메시지	Windows 2012 이상 버전에 더 이상 존재하지 않는 공유 폴더에 액세스하려고 할 때 발생하는 문제를 설명합니다.
   967351 NTFS 볼륨의 조각화된 파일이 특정 크기 이상으로 증가하지 않을 수 있습니다.	NTFS 파일 시스템이 많이 조각화될 때 발생하는 문제에 대해 설명합니다.
   대용량 시스템 볼륨을 백업할 때 304101 Backup 프로그램이 실패합니다.
   2455009 수정: SQL Server 2005, SQL Server 2008 또는 SQL Server 2008 R2의 트랜잭션 로그 내에 많은 VLL이 있는 경우 데이터베이스를 복구할 때 성능 저하	많은 가상 로그 파일이 있으면 데이터베이스를 복원하는 데 필요한 시간에 영향을 줄 수 있습니다. 복원 작업의 복구 단계에서 특히 그렇습니다. 많은 VLL이 있어 발생할 수 있는 다른 가능한 문제에 대한 자세한 내용은 데이터베이스 작업을 완료하는 데 시간이 오래 걸리거나 트랜잭션 로그에 가상 로그 파일이 많을 때 오류를 트리거하는 것을 참조하세요.
   네트워크 위치에 대한 백업 또는 복원 작업이 느림	SQL Server를 실행하는 서버에서 비슷한 크기의 파일을 네트워크 위치에 복사하여 네트워크에 문제를 격리합니다. 성능을 확인합니다.

2. 문제의 원인에 대한 자세한 포인터는 SQL Server 오류 로그 및 Windows 이벤트 로그에서 오류 메시지를 확인합니다.

3. 타사 소프트웨어 또는 데이터베이스 유지 관리 계획을 사용하여 동시 백업을 수행하는 경우 백업이 기록되는 드라이브에서 경합을 최소화하기 위해 일정을 변경해야 하는지 여부를 고려합니다.

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

서로 다른 SQL Server 버전 간의 데이터베이스 복원에 영향을 주는 문제

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

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

다음 방법을 사용하여 이후 버전의 SQL Server에서 호스트되는 데이터베이스를 이전 버전의 SQL Server로 복사합니다.

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

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

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

Always On 환경의 백업 작업 문제

Always On 환경에서 백업 작업 또는 유지 관리 계획에 영향을 주는 문제가 발생하는 경우 다음 사항에 유의하세요.

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

Always On 환경의 백업에 대한 자세한 내용은 다음 항목을 참조하세요.

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

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

파일 문제를 나타내는 오류 메시지가 표시되면 손상된 백업 파일의 징후입니다. 다음은 백업 세트가 손상된 경우 발생할 수 있는 오류의 몇 가지 예입니다.

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

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

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

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

이러한 오류를 방지하려면 손상된 데이터베이스를 백업하지 않도록 백업을 실행할 때 BACKUP CHECKSUM 옵션을 사용하도록 설정합니다. 자세한 내용은 백업 및 복원 중에 발생할 수 있는 미디어 오류(SQL Server)를 참조하세요.

백업 도구를 사용하여 백업을 실행할 때 추적 플래그 3023을 사용하도록 설정하여 체크섬을 사용하도록 설정할 수도 있습니다. 자세한 내용은 백업 유틸리티에서 옵션을 노출하지 않는 경우 CHECKSUM 옵션을 사용하도록 설정하는 방법을 참조하세요.

이러한 문제를 해결하려면 사용 가능한 다른 백업 파일을 찾거나 새 백업 세트를 만들어야 합니다. Microsoft는 손상된 백업 세트에서 데이터를 검색하는 데 도움이 되는 솔루션을 제공하지 않습니다.

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

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

• 시나리오 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 서비스 계정에 백업이 기록되는 폴더에 대한 읽기 및 쓰기 권한이 없는 경우 이러한 시나리오 중 하나가 발생할 수 있습니다. 백업 문은 작업 단계의 일부로 또는 SQL Server Management Studio에서 수동으로 실행할 수 있습니다. 두 경우 모두 SQL Server 서비스 시작 계정의 컨텍스트에서 항상 실행됩니다. 따라서 서비스 계정에 필요한 권한이 없으면 앞에서 설명한 오류 메시지가 표시됩니다.

자세한 내용은 백업 디바이스를 참조하세요.

타사 백업 애플리케이션을 사용하는 백업 또는 복원 작업이 실패합니다.

SQL Server는 VDI(가상 백업 디바이스 인터페이스) 도구를 제공합니다. 이 API를 사용하면 독립 소프트웨어 공급업체가 SQL Server를 제품에 통합하여 백업 및 복원 작업을 지원할 수 있습니다. 이러한 API는 최대 안정성 및 성능을 제공하고 전체 범위의 SQL Server 백업 및 복원 기능을 지원하도록 엔지니어링됩니다. 여기에는 전체 스냅샷 및 핫 백업 기능이 포함됩니다.

일반적 문제 해결 단계

• SQL Server 2012 이전 버전의 경우 SQLWriter 서비스가 시작되고 시작 계정이 로컬 시스템으로 설정되어 있는지 확인합니다. 또한 NT AUTHORITY\SYSTEM 로그인이 SQL Server에 있고 백업이 실행되는 인스턴스의 Sysadmin 서버 역할의 일부인지 확인합니다.

• SQL Server 2012 및 이후 버전의 경우 설치 과정에서 [NT SERVICE\SQLWriter]라는 새 로그인이 생성되어 로그인으로 프로비저닝됩니다. 이 로그인이 SQL Server에 있고 Sysadmin 서버 역할의 일부인지 확인합니다.

• SQL Server를 실행하는 서버의 명령 프롬프트에서 명령을 실행할 때 VSSADMIN LIST WRITERS SqlServerWriter가 나열되는지 확인합니다. 이 작성기는 작성기로 나열되어야 하며 VSS 백업이 성공적으로 완료될 수 있도록 안정 상태여야 합니다.

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

  증상 또는 시나리오	KB 문서
  대/소문자를 구분하는 데이터베이스 백업 실패	2987610 수정: SQL Server 2012 SP2에서 VSS를 사용하여 대/소문자를 구분하는 데이터 정렬이 있는 데이터베이스를 백업하는 경우 오류 발생
  VSS 기록기를 사용하여 만든 타사 백업은 실패하고 8229 오류를 반환할 수 있습니다.	2987610 수정: SQL Server 2012 SP2에서 VSS를 사용하여 대/소문자를 구분하는 데이터 정렬이 있는 데이터베이스를 백업하는 경우 오류 발생
  VDI 백업 작동 방식 이해	작동 방식: SQL Server - VDI(VSS) 백업 리소스
  Azure Site Recovery 에이전트가 실패를 보고합니다.	SQL Server 2008 R2를 호스팅하는 서버에 대해 ASR 에이전트 또는 기타 비 구성 요소 VSS 백업이 실패함

추가 리소스

작동 방식: 동시에 백업할 수 있는 데이터베이스 수는 몇 개입니까?

기타 문제

SQL Server 백업 및 복원 작업에 대한 FAQ

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

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

백업 도중 SQL Server가 장애 조치(fails over)되는 경우 어떻게 해야 하나요?

중단된 복원 작업(Transact-SQL)을 다시 시작할 때 복원 또는 백업 작업을 다시 시작합니다.

최신 버전의 이전 프로그램 버전에서 데이터베이스 백업을 복원할 수 있나요? 그 반대의 경우도 마찬가지인가요?

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

SQL Server 데이터베이스 백업을 확인할 어떻게 할까요? 있나요?

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

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

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

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

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

일반 문제 해결 팁

• 백업이 기록되는 폴더의 SQL Server 서비스 계정에 읽기 및 쓰기 권한을 프로비전해야 합니다. 자세한 내용은 백업 권한을 참조하세요.
• 백업이 작성되는 폴더에 데이터베이스 백업을 수용할 수 있는 충분한 공간이 있는지 확인합니다. 저장 프로시저를 sp_spaceused 사용하여 특정 데이터베이스에 대한 대략적인 백업 크기를 가져올 수 있습니다.
• 항상 최신 버전의 SSMS를 사용하여 작업 및 유지 관리 계획의 구성과 관련된 알려진 문제가 발생하지 않도록 합니다.
• 작업을 테스트 실행하여 백업이 성공적으로 만들어졌는지 확인합니다. 항상 백업을 확인하는 논리를 추가합니다.
• 시스템 데이터베이스를 한 서버에서 다른 서버로 이동하려는 경우 시스템 데이터베이스 이동을 검토 합니다.
• 간헐적인 백업 오류가 발생하는 경우 SQL Server 버전의 최신 업데이트에서 이미 수정된 문제가 발생하는지 확인합니다. 자세한 내용은 SQL Server 버전 및 업데이트를 참조하세요.
• SQL Express 버전에 대한 백업을 예약하고 자동화하려면 SQL Server Express에서 SQL Server 데이터베이스 백업 예약 및 자동화를 참조하세요.

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

• 백업 및 복원 작업에 대한 자세한 내용은 온라인 설명서의 다음 항목을 참조하세요.

  "SQL Server 데이터베이스 백업 및 복원": 이 항목에서는 SQL Server 데이터베이스에 대한 백업 및 복원 작업의 개념을 설명하고, 추가 항목에 대한 링크를 제공하며, 다양한 백업 또는 복원 작업(예: 백업 확인 및 T-SQL 또는 SSMS를 사용하여 백업)을 실행하는 자세한 절차를 제공합니다. SQL Server 설명서에서 이 주제에 대한 부모 항목입니다.

• 다음 표에는 백업 및 복원 작업과 관련된 특정 작업에 대해 검토할 수 있는 추가 항목이 나와 있습니다.

  참조	설명
  BACKUP(Transact-SQL)	백업과 관련된 기본 질문에 대한 답변을 제공합니다. 다양한 종류의 백업 및 복원 작업의 예를 제공합니다.
  백업 디바이스(SQL Server)	다양한 백업 디바이스를 이해하고 네트워크 공유, Azure Blob Storage 및 관련 작업에 백업하는 데 유용한 참조를 제공합니다.
  복구 모델(SQL Server)	단순, 전체 및 대량 로그의 다양한 복구 모델을 자세히 설명합니다. 복구 모델이 백업에 미치는 영향에 대한 정보를 제공합니다.
  백업 및 복원: 시스템 데이터베이스(SQL Server)	전략을 설명하고 시스템 데이터베이스의 백업 및 복원 작업을 수행할 때 알아야 할 사항에 대해 설명합니다.
  복원 및 복구 개요(SQL Server)	복구 모델이 복원 작업에 미치는 영향을 설명합니다. 데이터베이스의 복구 모델이 복원 프로세스에 어떤 영향을 미칠 수 있는지에 대한 질문이 있는 경우 이를 검토해야 합니다.
  다른 서버에서 데이터베이스를 사용할 수 있도록 지원할 때 메타데이터 관리	데이터베이스가 이동되거나 로그인, 암호화, 복제, 권한 등에 영향을 주는 문제가 발생할 때 알아야 할 다양한 고려 사항입니다.
  트랜잭션 로그 백업 작업	전체 및 대량 로그 복구 모델에서 트랜잭션 로그를 백업 및 복원(적용)하는 방법에 대한 개념을 제공합니다. 트랜잭션 로그(로그 백업)의 일상적인 백업을 수행하여 데이터를 복구하는 방법을 설명합니다.
  Microsoft Azure에 대한 SQL Server 관리 백업	관리되는 백업 및 관련 프로시저를 소개합니다.