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 백업 디바이스 개체를 참조하세요.

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

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

검사

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

    기술 자료 또는 온라인 설명서 링크 설명 및 권장 작업
    SQL Server 백업 및 복원 성능 최적화 온라인 설명서 항목에서는 백업/복원 작업의 성능을 개선하는 데 사용할 수 있는 다양한 모범 사례를 다룹니다. 예를 들어 SQL Server 실행 중인 Windows 계정에 특별한 권한을 할당 SE_MANAGE_VOLUME_NAME 하여 데이터 파일을 즉시 초기화할 수 있습니다. 이렇게 하면 상당한 성능 향상이 발생할 수 있습니다.
    Windows Server 2012 R2 기반 장애 조치(failover) 클러스터에 권장되는 핫픽스 및 업데이트 2920151

    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
    2455009 수정: 2005년 SQL Server, 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 instance 수행된 백업을 SQL Server 2017 instance 복원할 수 없습니다. 그렇지 않으면 다음 오류 메시지가 나타납니다.

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

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

참고

다음 절차에서는 이름이 SQL_A(상위 버전) 및 SQL_B(하위 버전)인 두 개의 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 및 기타 도구에서 메모리 부족 문제가 발생할 수 있습니다. SQL Server Integration Services(Integration Services), 복제 또는 기타 옵션을 사용하여 이후 버전에서 이전 버전의 SQL Server 데이터베이스 복사본을 만드는 것이 좋습니다.

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

Always On 환경의 백업 작업 문제

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

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

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

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

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

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

  • 3243: 디바이스 '%ls'의 미디어 패밀리가 Microsoft 테이프 형식 버전 %d.%d를 사용하여 만들어졌습니다. SQL Server 버전 %d.%d를 지원합니다.

참고

Restore Header 문을 사용하여 백업을 검사 수 있습니다.

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

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

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

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

참고

백업 파일이 한 서버에서 성공적으로 복원되지만 다른 서버에서는 복원되지 않는 경우 서버 간에 파일을 복사하는 다른 방법을 시도해 보세요. 예를 들어 일반 복사 작업 대신 robocopy 를 사용해 보세요.

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

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

  • 시나리오 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 Service 계정의 현재 권한을 검사 수 있습니다.

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

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

고급 문제 해결 단계

추가 리소스

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

기타 문제

증상/시나리오 수정 작업 또는 추가 정보
데이터베이스에서 변경 내용 추적을 사용하도록 설정하고 다음과 유사한 오류를 반환하면 백업이 실패할 수 있습니다.

"오류: 3999, 심각도: 17, 상태: 1.

<타임 스탬프> spid <spid> 오류 2601로 인해 dbid 8의 디스크에 커밋 테이블을 플러시하지 못했습니다. 자세한 내용은 오류 로그를 확인하세요."


 다음 Microsoft 기술 자료 문서를 참조하세요.
암호화된 데이터베이스의 백업 복원 문제 TDE 보호 데이터베이스를 다른 SQL Server 이동
Standard 버전에서 Enterprise 버전에서 CRM 백업을 복원하려고 하면 실패합니다. Microsoft Dynamics CRM 데이터베이스를 복원할 때 "이 버전의 SQL Server 데이터베이스를 시작할 수 없습니다." 오류 2567984

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 백업 및 복원 작업에 대한 참조 topics

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

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

  • 다음 표에는 백업 및 복원 작업과 관련된 특정 작업에 대해 검토할 수 있는 추가 topics 나열되어 있습니다.

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