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

아티클
03/03/2023
읽는 데 25분 걸림

적용 대상:SQL Server

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

자세한 내용은 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 데이터베이스 엔진의 해당 인스턴스에 연결한 다음, 개체 탐색기에서 서버 트리를 확장합니다.
데이터베이스를 확장하고 사용자 데이터베이스를 선택하거나 시스템 데이터베이스 를 확장한 다음 시스템 데이터베이스를 선택합니다.
백업하려는 데이터베이스를 마우스 오른쪽 단추로 클릭하고 작업을 가리킨 다음 백업... 을 선택합니다.
데이터베이스 백업 대화 상자에서 선택한 데이터베이스가 드롭다운 목록에 표시됩니다(서버에 있는 다른 데이터베이스로 변경할 수 있음).
백업 유형 드롭다운 목록에서 백업 유형을 선택합니다. 기본값은 Full입니다.

중요

차등 또는 트랜잭션 로그 백업을 수행하려면 1개 이상의 전체 데이터베이스 백업을 수행해야 합니다.
백업 구성 요소 아래에서 데이터베이스를 선택합니다.
대상 섹션에서 백업 파일의 기본 위치를 검토합니다(../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 대화 상자를 닫습니다.

Transact-SQL 사용

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

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

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

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

옵션	Description
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 Enterprise 이상에서만 사용 가능. 이 백업에서 백업 압축 을 수행하고 서버 수준 기본값을 재정의할지 여부를 지정합니다.
ENCRYPTION (ALGORITHM, SERVER CERTIFICATE | ASYMMETRIC KEY) : SQL Server 2014 이상에서만 사용할 암호화 알고리즘과 암호화 보안에 사용할 인증서 또는 비대칭 키를 지정합니다.
설명= { '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 절을 사용합니다. 필요에 따라 새 미디어에 미디어 이름과 설명을 지정합니다.

중요

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

다음 예에서는 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 사용

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

참고

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

자세한 내용은 SQL Server PowerShell Provider을(를) 참조하세요.

중요

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

예

A. 전체 백업(로컬)

다음 예에서는 서버 인스턴스 <myDatabase> 의 기본 백업 위치에 Computer\Instance데이터베이스의 전체 데이터베이스 백업을 만듭니다. 선택 사항으로, 이 예제에서는 -BackupAction Database를 지정합니다.

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

$credential = Get-Credential

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

B. Azure에 전체 백업

다음 예제에서는 인스턴스에서 Azure Blob Storage 데이터베이스 <myDatabase> 의 <myServer> 전체 백업을 만듭니다. 읽기, 쓰기 및 나열 권한이 있는 저장된 액세스 정책을 만들었습니다. 저장된 액세스 정책에 연결된 공유 액세스 서명을 사용하여 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