전체 데이터베이스 백업 만들기

적용 대상:SQL Server

이 문서에서는 SQL Server Management Studio, Transact-SQL 또는 PowerShell을 사용하여 SQL Server에서 전체 데이터베이스 백업을 만드는 방법을 설명합니다.

자세한 내용은 Azure Blob Storage 및 AZURE Blob Storage 의 URL에 대한 SQL Server 백업을 사용하여 SQL Server 백업 및 복원을 참조하세요.

제한점

• 명시적 또는 암시적 트랜잭션에서는 BACKUP 문을 사용할 수 없습니다.
• 최신 버전의 SQL Server에서 만든 백업은 이전 버전의 SQL Server에서 복원할 수 없습니다.

백업 개념 및 작업에 대한 개요 및 자세한 내용은 계속하기 전에 Backup 개요(SQL Server) 를 참조하세요.

권장 사항

• 데이터베이스 크기가 증가함에 따라 전체 데이터베이스 백업을 완료하는 데 더 많은 시간이 걸리고 더 많은 저장소 공간이 필요합니다. 대규모 데이터베이스의 경우 일련의 차등 데이터베이스 백업을 사용하여 전체 데이터베이스 백업을 보완하는 것이 좋습니다.
• sp_spaceused 시스템 저장 프로시저를 사용하여 전체 데이터베이스 백업의 크기를 예측합니다.
• 기본적으로 백업 작업을 성공적으로 수행할 때마다 SQL Server 오류 로그와 시스템 이벤트 로그에 항목이 추가됩니다. 자주 백업하는 경우 성공 메시지가 빠르게 누적되어 엄청난 오류 로그가 발생하므로 다른 메시지를 찾기가 어려워집니다. 이 경우 스크립트가 이러한 백업 로그 항목에 종속되지 않을 경우 추적 플래그 3226을 사용하여 이러한 항목을 표시하지 않을 수 있습니다. 자세한 내용은 DBCC TRACEON을 사용하여 추적 플래그 설정을 참조하세요.

보안

TRUSTWORTHY 은 데이터베이스 백업에 대해 설정 OFF 됩니다. 설정하는 TRUSTWORTHYON방법에 대한 자세한 내용은 ALTER DATABASE SET 옵션을 참조하세요.

SQL Server 2012(11.x)부터는 백업을 PASSWORD 만드는 데 사용할 수 있는 옵션과 MEDIAPASSWORD 옵션이 없습니다. 암호를 사용하여 만든 백업은 계속 복원할 수 있습니다.

사용 권한

BACKUP DATABASE 및 BACKUP LOG 권한은 기본적으로 sysadmin 고정 서버 역할과 db_owner 및 db_backupoperator 고정 데이터베이스 역할의 멤버로 설정됩니다.

백업 디바이스의 물리적 파일에서 발생하는 소유권과 사용 권한 문제는 백업 작업에 영향을 미칠 수 있습니다. SQL Server 서비스는 디바이스에서 읽고 디바이스에 씁니다. SQL Server 서비스가 실행되는 계정에는 백업 디바이스에 대한 쓰기 권한이 있어야 합니다. 그러나 시스템 테이블의 백업 디바이스에 대한 항목을 추가하는 sp_addumpdevice는 파일 액세스 권한을 확인하지 않습니다. 백업 디바이스의 실제 파일의 문제는 백업을 사용하거나 복원을 시도할 때까지 나타나지 않을 수 있습니다.

SQL Server Management Studio 사용

1. SQL Server 데이터베이스 엔진의 적절한 인스턴스에 연결한 후 개체 탐색기에서 서버 트리를 확장합니다.

2. 데이터베이스를 확장하고, 사용자 데이터베이스를 선택하거나 시스템 데이터베이스를 확장하고 시스템 데이터베이스를 선택합니다.

3. 백업할 데이터베이스를 마우스 오른쪽 단추로 클릭하고 작업을 가리킨 다음 백업을 선택합니다.

4. 데이터베이스 백업 대화 상자에서 선택한 데이터베이스가 드롭다운 목록에 나타납니다. (데이터베이스를 서버의 다른 데이터베이스로 변경할 수 있습니다.)

5. 백업 유형 목록에서 백업 유형을 선택합니다. 기본값은 Full입니다.

   중요한

   차등 또는 트랜잭션 로그 백업을 수행하려면 먼저 하나 이상의 전체 데이터베이스 백업을 수행해야 합니다.

6. Backup 구성 요소 아래에서 데이터베이스를 선택합니다.

7. 대상 섹션에서 백업 파일의 기본 위치를 검토합니다(../mssql/data 폴더).

   백업을 사용하여 다른 디바이스를 선택할 수 있습니다. 추가를 선택하여 백업 개체 및/또는 대상을 추가합니다. 백업 속도를 높이기 위해 여러 파일에 백업 세트를 스트라이프할 수 있습니다.

   백업 대상을 제거하려면 백업 대상을 선택한 다음 제거를 선택합니다. 기존 백업 대상의 내용을 보려면 선택한 다음 콘텐츠를 선택합니다.

8. (선택 사항) 미디어 옵션 및 백업 옵션 페이지에서 사용 가능한 다른 설정을 검토합니다.

   다양한 백업 옵션에 대한 자세한 내용은 데이터베이스 백업(일반 페이지),데이터베이스 백업(미디어 옵션 페이지) 및 데이터베이스 백업(백업 옵션 페이지)을 참조하세요.

9. 확인을 선택하여 백업을 시작합니다.

10. 백업이 성공적으로 완료되면 확인을 선택하여 SQL Server Management Studio 대화 상자를 닫습니다.

추가 정보

• 전체 데이터베이스 백업을 만든 후 차등 데이터베이스 백업 또는 트랜잭션 로그 백업을 만들 수 있습니다.

• (선택 사항) 복사 전용 백업 확인란을 선택하여 복사 전용 백업을 만들 수 있습니다. 복사 전용 백업은 기존 SQL Server 백업 시퀀스에 관계없이 SQL Server 백업입니다. 자세한 내용은 복사 전용 백업을 참조하세요. 서로 다른 백업 유형에서는 복사 전용 백업을 사용할 수 없습니다.

• URL에 백업하는 경우 미디어 옵션 페이지에서 미디어 덮어쓰기 옵션을 사용할 수 없습니다.

예제

다음 예제에서는 다음 Transact-SQL 코드를 사용하여 테스트 데이터베이스를 만듭니다.

USE [master]
GO

CREATE DATABASE [SQLTestDB]
GO

USE [SQLTestDB]
GO
CREATE TABLE SQLTest
   (
      ID INT NOT NULL PRIMARY KEY,
      c1 VARCHAR(100) NOT NULL,
      dt1 DATETIME NOT NULL DEFAULT getdate()
   );
GO

USE [SQLTestDB]
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1')
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2')
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3')
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4')
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5')
GO

SELECT * FROM SQLTest
GO

A. 디스크에 대한 전체 백업을 기본 위치로

이 예제에서는 데이터베이스가 SQLTestDB 기본 백업 위치의 디스크에 백업됩니다.

1. SQL Server 데이터베이스 엔진의 적절한 인스턴스에 연결한 후 개체 탐색기에서 서버 트리를 확장합니다.

2. 데이터베이스를 확장하고 SQLTestDB를 마우스 오른쪽 버튼으로 선택하여 작업을 가리킨 다음 백업...을 선택합니다.

3. 확인을 선택합니다.

4. 백업이 성공적으로 완료되면 확인을 선택하여 SQL Server Management Studio 대화 상자를 닫습니다.

B. 기본이 아닌 위치에 디스크에 전체 백업

이 예제에서는 데이터베이스가 SQLTestDB 선택한 위치의 디스크에 백업됩니다.

1. SQL Server 데이터베이스 엔진의 적절한 인스턴스에 연결한 후 개체 탐색기에서 서버 트리를 확장합니다.

2. 데이터베이스를 확장하고 SQLTestDB를 마우스 오른쪽 버튼으로 선택하여 작업을 가리킨 다음 백업...을 선택합니다.

3. 대상 섹션의 일반 페이지에서 백업 목록에서 디스크를 선택합니다.

4. 기존 백업 파일이 모두 제거될 때까지 제거 를 선택합니다.

5. 추가를 선택합니다. 백업 대상 선택 대화 상자가 열립니다.

6. 파일 이름 상자에 올바른 경로와 파일 이름을 입력합니다. .bak 확장으로 사용하여 파일 분류를 간소화합니다.

7. 확인을 선택한 다음 확인을 다시 선택하여 백업을 시작합니다.

8. 백업이 성공적으로 완료되면 확인을 선택하여 SQL Server Management Studio 대화 상자를 닫습니다.

C. 암호화된 백업 만들기

이 예제에서는 데이터베이스가 SQLTestDB 기본 백업 위치에 대한 암호화를 사용하여 백업됩니다.

1. SQL Server 데이터베이스 엔진의 적절한 인스턴스에 연결한 후 개체 탐색기에서 서버 트리를 확장합니다.

2. 데이터베이스를 확장하고 시스템 데이터베이스를 확장하고 마우스 오른쪽 단추를 클릭한 master다음 새 쿼리를 선택하여 데이터베이스에 연결된 SQLTestDB 쿼리 창을 엽니다.

3. 다음 명령을 실행하여 데이터베이스 내에서 데이터베이스 마스터 키 와 인증서 를 master 만듭니다.

   -- Create the master key.
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
   
   -- If the master key already exists, open it in the same session that you create the certificate. (See next step.)
   OPEN MASTER KEY DECRYPTION BY PASSWORD = '<password>'
   
   -- Create the certificate encrypted by the master key.
   CREATE CERTIFICATE MyCertificate
   WITH SUBJECT = 'Backup Cert', EXPIRY_DATE = '20201031';
   

4. 개체 탐색기에서 데이터베이스 노드의 SQLTestDB를 마우스 오른쪽 버튼으로 선택하고 작업을 가리킨 다음, 백업...을 선택합니다.

5. 미디어 옵션 페이지의 미디어 덮어쓰기 섹션에서 새 미디어 세트로 백업을 선택하고 기존 백업 세트를 모두 지웁다.

6. 백업 옵션 페이지의 암호화 섹션에서 백업 암호화를 선택합니다.

7. 알고리즘 목록에서 AES 256을 선택합니다.

8. 인증서 또는 비대칭 키 목록에서 .를 선택합니다MyCertificate.

9. 확인을 선택합니다.

D. Azure Blob Storage로 백업하기

이 예제에서는 Azure Blob Storage에 대한 SQLTestDB 전체 데이터베이스 백업을 만듭니다. 이 예제는 Blob 컨테이너가 있는 스토리지 계정이 이미 있다고 가정하여 작성되었습니다. 이 예제에서는 공유 액세스 서명을 만듭니다. 컨테이너에 기존 공유 액세스 서명이 있는 경우 예제가 실패합니다.

스토리지 계정에 Blob Storage 컨테이너가 없는 경우 계속하기 전에 컨테이너를 만듭니다. 범용 스토리지 계정 만들기 및 컨테이너 만들기를 참조하세요.

1. SQL Server 데이터베이스 엔진의 적절한 인스턴스에 연결한 후 개체 탐색기에서 서버 트리를 확장합니다.

2. 데이터베이스를 확장하고 SQLTestDB를 마우스 오른쪽 버튼으로 선택하여 작업을 가리킨 다음 백업...을 선택합니다.

3. 일반 페이지의 대상 섹션에서 백업 목록에서 URL을 선택합니다.

4. 추가를 선택합니다. 백업 대상 선택 대화 상자가 열립니다.

5. 이전에 SQL Server Management Studio에서 사용하려는 Azure Storage 컨테이너를 등록한 경우 선택합니다. 그렇지 않은 경우 새 컨테이너를 선택하여 새 컨테이너를 등록합니다.

6. Microsoft 구독에 연결 대화 상자에서 계정에 로그인합니다.

7. 스토리지 계정 선택 상자에서 스토리지 계정을 선택합니다.

8. Blob 컨테이너 선택 상자에서 Blob 컨테이너를 선택합니다.

9. 공유 액세스 정책 만료 일정 상자에서 이 예제에서 만든 공유 액세스 정책의 만료 날짜를 선택합니다.

10. 자격 증명 만들기를 선택하여 SQL Server Management Studio에서 공유 액세스 서명 및 자격 증명을 생성합니다.

11. 확인을 선택하여Microsoft 구독에 연결 대화 상자를 닫습니다.

12. 백업 파일 상자에서 원하는 경우 백업 파일의 이름을 변경합니다.

13. 확인을 선택하여 백업 대상 선택 대화 상자를 닫습니다.

14. 확인을 선택하여 백업을 시작합니다.

15. 백업이 성공적으로 완료되면 확인을 선택하여 SQL Server Management Studio 대화 상자를 닫습니다.

Transact-SQL 사용

다음을 지정하여 문을 실행 BACKUP DATABASE 하여 전체 데이터베이스 백업을 만듭니다.

• 백업할 데이터베이스의 이름입니다.
• 전체 데이터베이스 백업이 기록되는 백업 디바이스

전체 데이터베이스 백업에 대한 기본 Transact-SQL 구문은 다음과 같습니다.

BACKUP DATABASE <database>
TO <backup_device> [ , ...n ]
[ WITH <with_options> [ , ...o ] ];

필요에 따라 하나 이상의 WITH 옵션을 지정합니다. 여기에 몇 가지 기본 WITH 옵션이 설명되어 있습니다. 모든 옵션에 대한 자세한 내용은 WITH을 참조하세요.

기본 백업 세트 WITH 옵션:

• { 압축 | NO_COMPRESSION }. SQL Server 2008(10.0.x) Enterprise 이상에서만 백업 압축 이 수행되는지 여부를 지정하고 서버 수준 기본값을 재정의합니다.
• 암호화(알고리즘, 서버 인증서 | 비대칭 키). SQL Server 2014 이상에서만 사용할 암호화 알고리즘과 암호화를 보호하는 데 사용할 인증서 또는 비대칭 키를 지정합니다.
• DESCRIPTION = { 'text' | @text_variable }. 백업 집합을 설명하는 자유 형식 텍스트를 지정합니다. 문자열을 최대 255자까지 지정할 수 있습니다.
• NAME = { backup_set_name | @backup_set_name_var }. 백업 세트의 이름을 지정합니다. 이름은 최대 128자까지 지정할 수 있습니다. 지정하지 않으면 NAME 비어 있습니다.

기본적으로 BACKUP은 기존 미디어 세트에 백업을 추가하여 기존 백업 세트를 유지합니다. 이 구성을 명시적으로 지정하려면 이 옵션을 사용합니다 NOINIT . 기존 백업 집합에 추가하는 방법에 대한 자세한 내용은 미디어 세트, 미디어 패밀리 및 백업 세트(SQL Server)를 참조하세요.

백업 미디어의 서식을 지정하려면 다음 FORMAT 옵션을 사용합니다.

FORMAT [ , 미디어이름 = { media_name | @media_name_variable } ] [ , 미디어설명 = { text | @text_variable } ]

FORMAT 미디어를 처음 사용하거나 기존 데이터를 모두 덮어쓰려는 경우 절을 사용합니다. 필요에 따라 새 미디어에 미디어 이름과 설명을 지정합니다.

예제

다음 예제에서는 다음 Transact-SQL 코드를 사용하여 테스트 데이터베이스를 만듭니다.

USE [master]
GO

CREATE DATABASE [SQLTestDB]
GO

USE [SQLTestDB]
GO
CREATE TABLE SQLTest (
   ID INT NOT NULL PRIMARY KEY,
   c1 VARCHAR(100) NOT NULL,
   dt1 DATETIME NOT NULL DEFAULT GETDATE()
)
GO

USE [SQLTestDB]
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1')
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2')
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3')
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4')
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5')
GO

SELECT * FROM SQLTest
GO

A. 디스크 디바이스에 백업

다음 예제에서는 전체 SQLTestDB 데이터베이스를 디스크에 백업합니다. 새 미디어 세트를 만드는 데 사용됩니다 FORMAT .

USE SQLTestDB;
GO
BACKUP DATABASE SQLTestDB
TO DISK = 'c:\tmp\SQLTestDB.bak'
   WITH FORMAT,
      MEDIANAME = 'SQLServerBackups',
      NAME = 'Full Backup of SQLTestDB';
GO

B. 테이프 디바이스에 백업

다음 예제에서는 전체 SQLTestDB 데이터베이스를 테이프에 백업합니다. 이전 백업에 백업을 추가합니다.

USE SQLTestDB;
GO
BACKUP DATABASE SQLTestDB
   TO TAPE = '\\.\Tape0'
   WITH NOINIT,
      NAME = 'Full Backup of SQLTestDB';
GO

C. 논리적 테이프 장치에 백업

다음 예에서는 테이프 드라이브에 대한 논리적 백업 디바이스를 만듭니다. 그런 다음, 전체 SQLTestDB 데이터베이스를 해당 디바이스에 백업합니다.

-- Create a logical backup device,
-- SQLTestDB_Bak_Tape, for tape device \\.\tape0.
USE master;
GO
EXEC sp_addumpdevice 'tape', 'SQLTestDB_Bak_Tape', '\\.\tape0'; USE SQLTestDB;
GO
BACKUP DATABASE SQLTestDB
   TO SQLTestDB_Bak_Tape
   WITH FORMAT,
      MEDIANAME = 'SQLTestDB_Bak_Tape',
      MEDIADESCRIPTION = '\\.\tape0',
      NAME = 'Full Backup of SQLTestDB';
GO

PowerShell 사용

cmdlet을 Backup-SqlDatabase 사용합니다. 전체 데이터베이스 백업을 명시적으로 나타내려면 매개 변수를 -BackupAction 기본값 Database으로 지정합니다. 전체 데이터베이스 백업의 경우 이 매개 변수는 선택 사항입니다.

예제

A. 전체 백업(로컬)

다음 예에서는 서버 인스턴스 <myDatabase> 의 기본 백업 위치에 Computer\Instance데이터베이스의 전체 데이터베이스 백업을 만듭니다. 필요에 따라 이 예제에서는 .를 지정합니다 -BackupAction Database.

전체 구문 예제는 Backup-SqlDatabase를 참조하세요.

$credential = Get-Credential

Backup-SqlDatabase -ServerInstance Computer[\Instance] -Database <myDatabase> -BackupAction Database -Credential $credential

B. Azure에 전체 백업

다음 예제에서는 Blob Storage에 인스턴스에 데이터베이스 <myDatabase> 의 <myServer> 전체 백업을 만듭니다. 읽기, 쓰기 및 나열 권한이 있는 저장된 액세스 정책을 만들었습니다. SQL Server 자격 증명 https://<myStorageAccount>.blob.core.windows.net/<myContainer>은 저장된 액세스 정책과 연결된 공유 액세스 서명을 사용하여 만들어졌습니다. 이 명령은 매개 변수를 $backupFile 사용하여 위치(URL) 및 백업 파일 이름을 지정합니다.

$credential = Get-Credential
$container = 'https://<myStorageAccount>blob.core.windows.net/<myContainer>'
$fileName = '<myDatabase>.bak'
$server = '<myServer>'
$database = '<myDatabase>'
$backupFile = $container + '/' + $fileName

Backup-SqlDatabase -ServerInstance $server -Database $database -BackupFile $backupFile -Credential $credential

관련 작업

• 차등 데이터베이스 백업 만들기(SQL Server)
• SSMS를 사용하여 데이터베이스 백업 복원
• 단순 복구 모델에서 데이터베이스 백업 복원(Transact-SQL)
• 실패 지점으로 데이터베이스 복원 - 전체 복구
• 데이터베이스를 새 위치로 복원(SQL Server)
• 유지 관리 계획 마법사 사용

관련 콘텐츠

• SQL Server 백업 및 복원 작업 문제 해결
• 백업 개요(SQL Server)
• 트랜잭션 로그 백업(SQL Server)
• 미디어 세트, 미디어 패밀리 및 백업 세트(SQL Server)
• sp_addumpdevice(Transact-SQL)
• BACKUP(Transact-SQL)
• 데이터베이스 백업(일반 페이지)
• 데이터베이스 백업(백업 옵션 페이지)
• 차등 백업(SQL Server)
• 전체 데이터베이스 백업(SQL Server)