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

아티클
01/09/2024

적용 대상:SQL Server

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

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

제한 사항

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

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

권장 사항

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

보안

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

SQL Server 2012(11.x)부터 암호 및 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입니다.

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 대화 상자를 닫습니다.

Take SQL backup

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

이 예제에서는 데이터베이스가 SQLTestDB 원하는 위치에 있는 디스크에 백업됩니다.

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

Change DB location

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.
확인을 선택합니다.

Encrypted backup

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 데이터베이스 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 옵션:

{ 압축 | 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 옵션을 사용합니다. 기존 백업 집합에 추가하는 방법에 대한 자세한 내용은 Media Sets, Media Families 및 Backup Sets(SQL Server)를 참조하세요.

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

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

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

Important

문의 FORMAT 절 BACKUP 을 사용할 때는 백업 미디어에 이전에 저장된 백업이 모두 삭제되므로 주의해야 합니다.

예제

다음 예제에서는 다음 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에 대한 전체 백업

관련 작업

관련 콘텐츠

피드백

피드백

추가 리소스