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

아티클
06/04/2024

적용 대상:SQL Server

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

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

제한 사항

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

백업 개념과 태스크의 개요를 보고 자세히 살펴보려면 계속하기 전에 백업 개요(SQL Server)를 참조하세요.

권장 사항

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

보안

TRUSTWORTHY가 데이터베이스 백업에서 OFF로 설정됩니다. TRUSTWORTHY를 ON으로 설정하는 방법에 대한 자세한 내용은 ALTER DATABASE SET 옵션(Transact-SQL)을 참조하세요.

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 사용

참고 항목

SQL Server Management Studio를 사용하여 백업 작업을 지정할 때 스크립트 버튼을 클릭하고 스크립트 대상을 선택하여 해당하는 Transact-SQL BACKUP 스크립트를 생성할 수 있습니다.

Microsoft SQL Server 데이터베이스 엔진의 적절한 인스턴스에 연결한 후 개체 탐색기에서 서버 트리를 확장합니다.
데이터베이스를 확장하고, 사용자 데이터베이스를 선택하거나 시스템 데이터베이스를 확장하고 시스템 데이터베이스를 선택합니다.
백업하려는 데이터베이스를 마우스 오른쪽 버튼으로 클릭하고 작업을 가리킨 다음 백업...을 선택합니다.
데이터베이스 백업 대화 상자에서 선택한 데이터베이스가 드롭다운 목록에 표시됩니다(서버의 다른 데이터베이스로 변경할 수 있습니다).
백업 유형 드롭다운 목록에서 백업 유형을 선택합니다. 기본값은 전체입니다.

Important

차등 또는 트랜잭션 로그 백업을 수행하려면 먼저 하나 이상의 전체 데이터베이스 백업을 수행해야 합니다.
Backup 구성 요소 아래에서 데이터베이스를 선택합니다.
대상 섹션에서 백업 파일의 기본 위치를 검토합니다(../mssql/data 폴더).

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

백업 대상을 제거하려면 백업 대상을 선택하고 제거를 선택합니다. 기존 백업 대상의 콘텐츠를 보려면 선택하고 콘텐츠를 선택합니다.
(선택 사항) 미디어 옵션 및 백업 옵션 페이지에서 사용 가능한 다른 설정을 검토합니다.

다양한 백업 옵션에 대한 자세한 내용은 일반 페이지, 미디어 옵션 페이지 및 백업 옵션 페이지를 참조하세요.
확인을 선택하여 백업을 시작합니다.
백업이 성공적으로 완료되면 확인을 선택하여 SQL Server Management Studio 대화 상자를 닫습니다.

추가 정보

전체 데이터베이스 백업을 만든 후 차등 데이터베이스 백업 또는 트랜잭션 로그 백업을 만들 수 있습니다.
(선택 사항) 복사 전용 백업 을 선택하여 복사 전용 백업을 만들 수도 있습니다. 복사 전용 백업은 일반 SQL Server 백업 시퀀스와 독립적인 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 데이터베이스가 디스크에 기본 백업 위치로 백업됩니다.

Microsoft SQL Server 데이터베이스 엔진의 적절한 인스턴스에 연결한 후 개체 탐색기에서 서버 트리를 확장합니다.
데이터베이스를 확장하고 SQLTestDB를 마우스 오른쪽 버튼으로 선택하여 작업을 가리킨 다음 백업...을 선택합니다.
확인을 선택합니다.
백업이 성공적으로 완료되면 확인을 선택하여 SQL Server Management Studio 대화 상자를 닫습니다.

SQL 백업 수행

B. 디스크에 기본 위치가 아닌 곳으로 전체 백업

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

Microsoft SQL Server 데이터베이스 엔진의 적절한 인스턴스에 연결한 후 개체 탐색기에서 서버 트리를 확장합니다.
데이터베이스를 확장하고 SQLTestDB를 마우스 오른쪽 버튼으로 선택하여 작업을 가리킨 다음 백업...을 선택합니다.
대상 섹션의 일반 페이지에 있는 백업 대상: 드롭다운 목록에서 디스크를 선택합니다.
모든 기존 백업 파일이 제거될 때까지 제거 를 선택합니다.
추가를 선택하면 백업 대상 선택 대화 상자가 열립니다.
파일 이름 입력란에 유효한 경로와 파일 이름을 입력하고 확장명으로 .bak를 사용하여 이 파일의 분류를 간소화합니다.
확인을 선택한 다음 확인을 다시 선택하여 백업을 시작합니다.
백업이 성공적으로 완료되면 확인을 선택하여 SQL Server Management Studio 대화 상자를 닫습니다.

DB 위치 변경

C. 암호화된 백업 만들기

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

Microsoft SQL Server 데이터베이스 엔진의 적절한 인스턴스에 연결한 후 개체 탐색기에서 서버 트리를 확장합니다.
데이터베이스, 시스템 데이터베이스를 차례로 확장하고 master를 마우스 오른쪽 단추로 선택한 후 새 쿼리를 선택하여 SQLTestDB 데이터베이스에 연결된 쿼리 창을 엽니다.

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

-- Create the master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';  

-- 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 = '23987hxJ#KL95234nl0zBe'

-- Create the certificate encrypted by the master key
CREATE CERTIFICATE MyCertificate
WITH SUBJECT = 'Backup Cert', EXPIRY_DATE = '20201031';

개체 탐색기에서 데이터베이스 노드의 SQLTestDB를 마우스 오른쪽 버튼으로 선택하고 작업을 가리킨 다음, 백업...을 선택합니다.
미디어 덮어쓰기 섹션의 미디어 옵션 페이지에서 새 미디어 세트에 백업하고 기존 백업 세트 모두 지우기를 선택합니다.
암호화 섹션의 백업 옵션 페이지에서 백업 암호화 확인란을 선택합니다.
알고리즘 드롭다운 목록에서 AES 256을 선택합니다.
인증서 또는 비대칭 키 드롭다운 목록에서 MyCertificate를 선택합니다.
확인을 선택합니다.

암호화된 백업

D. Azure Blob Storage 백업

이 예제에서는 Azure Blob Storage에 SQLTestDB의 전체 데이터베이스 백업을 만듭니다. 이 예제에서는 Blob 컨테이너가 있는 스토리지 계정이 이미 있다고 가정합니다. 이 예제에서는 공유 액세스 서명을 만듭니다. 컨테이너에 기존 공유 액세스 서명이 있으면 이 예제는 실패합니다.

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

Microsoft SQL Server 데이터베이스 엔진의 적절한 인스턴스에 연결한 후 개체 탐색기에서 서버 트리를 확장합니다.
데이터베이스를 확장하고 SQLTestDB를 마우스 오른쪽 버튼으로 선택하여 작업을 가리킨 다음 백업...을 선택합니다.
대상 섹션의 일반 페이지에 있는 백업 대상: 드롭다운 목록에서 URL를 선택합니다.
추가를 선택하면 백업 대상 선택 대화 상자가 열립니다.
SQL Server Management Studio에서 사용하려는 Azure Storage 컨테이너를 이전에 등록한 경우 해당 컨테이너를 선택합니다. 그렇지 않은 경우 새 컨테이너를 선택하여 새 컨테이너를 등록합니다.
Microsoft 구독에 연결 대화 상자에서 계정에 로그인합니다.
스토리지 계정 선택 드롭다운 텍스트 상자에서 스토리지 계정을 선택합니다.
Blob 컨테이너 선택 드롭다운 입력란에서 Blob 컨테이너를 선택합니다.
공유 액세스 정책 만료 드롭다운 일정 상자에서 이 예제에서 만든 공유 액세스 정책의 만료 날짜를 선택합니다.
자격 증명 만들기를 선택하여 SQL Server Management Studio에서 공유 액세스 서명 및 자격 증명을 생성합니다.
확인을 선택하여 Microsoft 구독에 연결 대화 상자를 닫습니다.
백업 파일 입력란에서 백업 파일의 이름을 수정합니다(선택 사항).
확인을 선택하여 백업 대상 선택 대화 상자를 닫습니다.
확인을 선택하여 백업을 시작합니다.
백업이 성공적으로 완료되면 확인을 선택하여 SQL Server Management Studio 대화 상자를 닫습니다.

참고 항목

관리 ID를 사용하여 Azure Blob Storage에 백업은 현재 지원되지 않습니다.

Transact-SQL 사용

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

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

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

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

옵션	설명
database	백업할 데이터베이스입니다.
backup_device [ ,...n ]	백업 작업에 사용할 1-64개의 백업 디바이스 목록을 지정합니다. 물리적 백업 디바이스를 지정하거나 이미 정의된 경우 해당 논리 백업 디바이스를 지정할 수 있습니다. 물리적 백업 디바이스를 지정하려면 DISK 또는 TAPE 옵션을 사용합니다. { DISK \| TAPE } =physical_backup_device_name 자세한 내용은 백업 디바이스(SQL Server)를 참조하세요.
WITH with_options [ ,...o ]	o는 하나 이상의 옵션을 지정하는 데 사용됩니다. 몇 가지 WITH의 기본 옵션에 대한 자세한 내용은 2단계를 참조하세요.

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

기본 백업 세트 WITH 옵션은 다음과 같습니다.

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

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

백업 미디어를 포맷하기 위해 FORMAT 옵션을 사용합니다.

FORMAT [ , MEDIANAME = { media_name | @media_name_variable } ] [ , MEDIADESCRIPTION = { text | @text_variable } ]

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

Important

BACKUP 문의 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. 디스크 디바이스에 백업

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

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 사용

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

참고 항목

이러한 예제에는 SqlServer 모듈이 필요합니다. 설치되어 있는지 확인하려면 Get-Module -Name SqlServer를 실행 합니다. 설치하려면 PowerShell의 관리자 세션에서 Install-Module -Name SqlServer를 실행합니다.

자세한 내용은 SQL Server PowerShell 공급자를 참조하세요.

Important

SQL Server Management Studio 내에서 PowerShell 창을 열어 SQL Server 설치에 연결하는 경우 SSMS의 자격 증명이 PowerShell과 SQL Server 인스턴스 간의 연결을 설정하는 데 자동으로 사용되므로 자격 증명 부분을 생략할 수 있습니다.

예제

A. 전체 백업(로컬)

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

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

$credential = Get-Credential

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

B. Azure에 전체 백업

다음 예제에서는 Azure Blob Storage에 <myServer> 인스턴스의 <myDatabase> 데이터베이스 전체 백업을 만듭니다. 읽기, 쓰기 및 나열 권한이 있는 저장된 액세스 정책을 만들었습니다. 저장된 액세스 정책에 연결된 공유 액세스 서명을 사용하여 SQL Server 자격 증명인 https://<myStorageAccount>.blob.core.windows.net/<myContainer>를 만들었습니다. PowerShell 명령은 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 Management Studio 사용

추가 정보

예제

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

B. 디스크에 기본 위치가 아닌 곳으로 전체 백업

C. 암호화된 백업 만들기

D. Azure Blob Storage 백업

Transact-SQL 사용

예제

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

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

C. 논리 테이프 디바이스에 백업

PowerShell 사용

예제

A. 전체 백업(로컬)

B. Azure에 전체 백업

관련 작업

피드백

피드백

추가 리소스

다음을 통해 공유

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

제한 사항

권장 사항

보안

사용 권한

SQL Server Management Studio 사용

추가 정보

예제

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

B. 디스크에 기본 위치가 아닌 곳으로 전체 백업

C. 암호화된 백업 만들기

D. Azure Blob Storage 백업

Transact-SQL 사용

예제

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

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

C. 논리 테이프 디바이스에 백업

PowerShell 사용

예제

A. 전체 백업(로컬)

B. Azure에 전체 백업

관련 작업

관련 콘텐츠

피드백

피드백

추가 리소스