Backup...

이젠 기억이 가물가물...
걍 정리해놔야지.. ㅋㅋㅋ


/*

==========================================================
대량로그복구모델
단순모델

- 데이터베이스 백업의 종류
전체백업
차등백업

* 전체백업
BACKUP DATABASE 데이터베이스이름 TO 백업할 파일 또는 장치
EX) BACKUP DATABASE AdventureWorks TO DISK = "C:\백업폴더\백업.bak";
EX) BACKUP DATABASE AdventureWorks TO advDevice;

* 차등백업
BACKUP DATABASE 데이터베이스이름 TO 백업할 파일 또는 장치 WITH DIFFERENTIAL

* 트랜잭션 로그 백업
BACKUP LOG 데이터베이스이름 TO 백업할 파일 또는 장치

* 부분백업
BACKUP DATABASE 데이터베이스이름 READ_WRITE_FILEGROUPS TO 장치

* 부분차등백업

* 백업압축
WITH COMPRESSION

* 미러백업
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\adv.bak'
MIRROR TO DISK = 'd:\adv.bak'
WITH FORMAT

* 복사전용백업
BACKUP DATABASE 데이터베이스이름 TO 장치 WITH COPY_ONLY

* 체크섬기능
BACKUP DATABASE 데이터베이스이름 TO 장치 WITH CHECKSUM

* 백업매체 초기화
BACKUP DATABASE 데이터베이스이름 TO 장치 WITH INIT

* 다중백업장치의 초기화
BACKUP DATABASE 데이터베이스이름 TO 장치1, 장치2, 장치3 WITH FORMAT

* 비밀번호 지정
BACKUP DATABASE 데이터베이스이름 TO 장치 WITH PASSWORD = '비밀번호'

* 백업 중 오류발생시 계속 여부
BACKUP DATABASE 데이터베이스이름 TO 장치 WITH CONTINUE_AFTER_ERROR

* 진행률 표시
BACKUP DATABASE 데이터베이스이름 TO 장치 WITH STATS

* 데이터베이스에 문제 발생시 로그 백업(=비상 로그 백업)
BACKUP LOG 데이터베이스이름 TO 장치 WITH NO_TRUNCATE

 

==========================================================
데이터베이스 복원

전체복원
RESTORE DATABASE 데이터베이스이름 FROM 백업장치

차등복원
RESTORE DATABASE 데이터베이스이름 FROM 백업장치

로그복원
RESTORE LOG 데이터베이스이름 FROM 백업장치

복원중일때
WITH NORECOVERY

복원 후에 제한된 사용자만 접근 허용
RESOTRE DATABASE 데이터베이스이름 FROM 백업장치 WITH RESTRICTED_USER

전체 OPEN
RESTORE DATABASE 데이터베이스이름 WITH RECOVERY

복원 시 데이터 파일의 이동
RESTORE DATABASE 데이터베이스이름 FROM 백업장치
 WITH MOVE 'AdventureWorks_data' TO 'D:\adv.mdf',
 MOVE 'AdventureWorks_log' TO 'D:\adv.ldf'
 
오류가 발생해도 계속 복원하기
RESTORE DATABASE 데이터베이스이름 FROM 백업장치
 WITH CONTINUE_AFTER_ERROR

원래 파일이 있으면 덮어쓰기
RESTORE DATABASE 데이터베이스이름 FROM 백업장치 WITH REPLACE

정확한 시점까지만 복원하기
RESTORE DATABASE 데이터베이스이름 FROM 백업장치
 WITH STOPAT = '날짜와 시간'

데이터베이스 스냅숏으로 복원하기
RESTORE DATABASE 데이터베이스이름 FROM DATABASE_SNAPSHOT = '스냅숏이름';

*/

USE master;
DROP DATABASE testDB1;  -- 혹시기존에DB가있으면삭제, 오류가나도상관없음
GO
CREATE DATABASE [testDB1] ON  PRIMARY
( NAME = N'testDB1'
, FILENAME = N'C:\데이터파일\testDB1.mdf'
)
 LOG ON
( NAME = N'testDB1_log'
, FILENAME = N'C:\로그파일\testDB1_log.ldf'
);
GO
USE [testDB1]
CREATE TABLE tbl1 (no int);

INSERT INTO tbl1 VALUES(10);
/* 1.위 입력 후 전체백업 */

INSERT INTO tbl1 VALUES(20);
INSERT INTO tbl1 VALUES(30)
/* 2.위 입력 후 차등백업 */

INSERT INTO tbl1 VALUES(40);
INSERT INTO tbl1 VALUES(50);;
/* 3.위 입력 후 로그백업 */

INSERT INTO tbl1 VALUES(60);
/* 4.위 입력 후 로그백업 */

INSERT INTO tbl1 VALUES(70);
INSERT INTO tbl1 VALUES(80);
INSERT INTO tbl1 VALUES(90);
SELECT * FROM tbl1;
/* 5.위 입력 후 문제 발생 시킴 */

USE master;
ALTER DATABASE testDB1 SET OFFLINE;

/* 6.문제발생을 위해 mdf 파일 삭제 */

ALTER DATABASE testDB1 SET ONLINE;

/* 로그파일이 남아있다면 비상로그백업 */
BACKUP LOG 데이터베이스이름 TO DISK = 'C:\비상로그백업.bak'
 WITH NO_TRUNCATE, INIT

/* 데이터베이스 복원작업 */

 

 

/*
파일그룹으로 생성된 데이터베이스 일 경우
*/
USE master;
DROP DATABASE testDB2;  -- 혹시 기존에 DB가 있으면 삭제, 오류가 나도 상관 없음
GO
CREATE DATABASE [testDB2] ON  PRIMARY
( NAME = N'FG1-1'
, FILENAME = N'c:\데이터파일\FG1-1.mdf'
),
( NAME = N'FG1-2'
, FILENAME = N'c:\데이터파일\FG1-2.ndf'
),
 FILEGROUP [FG2]
( NAME = N'FG2-1'
, FILENAME = N'c:\데이터파일\FG2-1.ndf'
),
( NAME = N'FG2-2'
, FILENAME = N'c:\데이터파일\FG2-2.ndf'
),
FILEGROUP [FG3]
( NAME = N'FG3-1'
, FILENAME = N'c:\데이터파일\FG3-1.ndf'
),
( NAME = N'FG3-2'
, FILENAME = N'c:\데이터파일\FG3-2.ndf'
)
 LOG ON
( NAME = N'testDB2_log'
, FILENAME = N'c:\로그파일\testDB2_log.ldf'
)
GO

USE testDB2;
CREATE TABLE tbl1 (num int) ;
GO
CREATE TABLE tbl2 (num int)  ON FG2;
GO
CREATE TABLE tbl3 (num int)  ON FG3;
GO

USE testDB2;
INSERT INTO tbl1 VALUES(10);
INSERT INTO tbl2 VALUES(11);
INSERT INTO tbl3 VALUES(12);
GO
BACKUP DATABASE testDB2 TO disk = 'c:\testDB2.bak'
WITH NAME = N'제1회-전체백업', INIT;
GO

BACKUP LOG testDB2 TO disk = 'c:\testDB2.bak'
WITH NAME=N'제1회-로그백업';
GO
/* 1. 데이터 입력 후 전체 백업 실시 */

USE testDB2;
INSERT INTO tbl1 VALUES(20);
INSERT INTO tbl2 VALUES(21);
INSERT INTO tbl3 VALUES(22);
GO
BACKUP DATABASE testDB2 FILEGROUP = 'PRIMARY'
  TO disk = 'c:\testDB2.bak'
  WITH NAME=N'제2회-파일그룹백업' ;
GO
BACKUP LOG testDB2 TO disk = 'c:\testDB2.bak'
WITH NAME=N'제2회-로그백업';
GO
/* 2. 데이터 입력 후 Primary 그룹만 백업 */

USE testDB2;
INSERT INTO tbl1 VALUES(30);
INSERT INTO tbl2 VALUES(31);
INSERT INTO tbl3 VALUES(32);
GO
BACKUP DATABASE testDB2 FILEGROUP = 'FG2'
  TO disk = 'c:\testDB2.bak'
  WITH NAME=N'제3회-파일그룹백업' ;
GO
BACKUP LOG testDB2 TO disk = 'c:\testDB2.bak'
WITH NAME=N'제3회-로그백업';
GO
/* 3. 데이터 입력 후 FG2 파일 그룹만 백업 */

USE testDB2;
INSERT INTO tbl1 VALUES(40);
INSERT INTO tbl2 VALUES(41);
INSERT INTO tbl3 VALUES(42);
GO
BACKUP DATABASE testDB2 FILEGROUP = 'FG3'
  TO disk = 'c:\testDB2.bak'
  WITH NAME=N'제4회-파일그룹백업' ;
GO
BACKUP LOG testDB2 TO disk = 'c:\testDB2.bak'
WITH NAME=N'제4회-로그 백업';
GO
/* 4. 데이터 입력 후 FG3 파일 그룹만 백업 */

RESTORE HEADERONLY FROM DISK = 'C:\백업파일.bak';
/* 5. 백업된 내용 확인 */

USE testDB2;
INSERT INTO tbl1 VALUES(50);
INSERT INTO tbl2 VALUES(51);
INSERT INTO tbl3 VALUES(52);
GO
/* 6. 데이터 입력 */

/* 7. 데이터베이스 오프라인 후 FG2-1.ndf 파일 삭제 */
ALTER DATABASE 데이터베이스이름 SET OFFLINE;
GO

USE master
ALTER DATABASE testDB2 SET ONLINE
GO
/* 오류 확인*/

BACKUP LOG testDB2 TO DISK='c:\testDB2.bak'
 WITH NAME='제5회-비상로그백업', NO_TRUNCATE
/* 8. 비상로그백업 */

RESTORE HEADERONLY FROM DISK = 'c:\testDB2.bak'
/* 9. 비상로그백업 확인 */

USE master
RESTORE DATABASE testDB2 FILEGROUP = 'FG2'
   FROM DISK='c:\testDB2.bak'
   WITH FILE = 5, NORECOVERY
GO
RESTORE LOG testDB2 FILEGROUP = 'FG2'
   FROM DISK='c:\testDB2.bak'
   WITH FILE = 6, NORECOVERY
GO
RESTORE LOG testDB2 FILEGROUP = 'FG2'
   FROM DISK='c:\testDB2.bak'
   WITH FILE = 8, NORECOVERY /* 비상로그백업 번호 */
GO
RESTORE LOG testDB2 
   FROM DISK='c:\testDB2.bak'
   WITH FILE = 9       -- 디폴트는 RECOVERY
GO
/* 10. 복원 진행 */

USE testDB2
SELECT * FROM tbl1
SELECT * FROM tbl2
SELECT * FROM tbl3
/* 11. 확인 */

BACKUP DATABASE testDB2 TO disk = 'c:\testDB2-FULL.bak'
WITH NAME = N'전체백업', INIT
GO
/* 12. 다시 전체 백업을 해놓는다..오류가 또 생길지 모르니.. */

 

/*
중요그룹부터 복원
*/

USE master
RESTORE DATABASE testDB2 FROM disk = 'c:\testDB2-FULL.bak'
WITH RECOVERY, REPLACE
/* 강제 복원.. 위의 테스트에서 계속 진행을 위해서니..알아서들 해석하시고..*/

BACKUP DATABASE testDB2 TO backupDevice1, backupDevice2, backupDevice3
WITH NAME = N'전체백업', INIT, FORMAT
GO
/* 1. 전체 백업 */

USE testDB2
INSERT INTO tbl1 VALUES(60)
INSERT INTO tbl2 VALUES(61)
INSERT INTO tbl3 VALUES(62)
GO
/* 2. 데이터 입력 */

USE master
ALTER DATABASE testDB2 SET OFFLINE
/* 3. 오프라인으로 만들고 FG*.* 모두 삭제 */

ALTER DATABASE testDB2 SET ONLINE
/* 4. 온라인으로 고고 */

/* -- 가장 중요한 파일 그룹부터 복원
하지만 Primary 파일 그룹은 꼭 제일 먼저 복원이 되어야한다.
중요도가 tbl1 -> tbl3 -> tbl2 라고 가정한다면
Primary 파일그룹 -> FG3 -> FG2 순으로 복원
*/

BACKUP LOG testDB2 TO DISK='c:\비상로그1.bak'
 WITH NO_TRUNCATE, INIT
/* 5. 비상 로그 백업 */

RESTORE DATABASE testDB2 FILEGROUP='PRIMARY'
FROM backupDevice1, backupDevice2, backupDevice3
WITH PARTIAL, NORECOVERY 
GO
RESTORE LOG testDB2 FROM DISK='c:\비상로그1.bak'
GO
/* 6. Primary 파일 그룹 복원, 임시로그도 복원 */

USE testDB2;
SELECT * FROM tbl1;
/* 7. Primary 파일 그룹 테이블 접근 가능 */

INSERT INTO tbl1 VALUES(70);
/* 8. Primary 파일 그룹에 읽기/쓰기 가능하다는 것이 확인됨 */

SELECT * FROM tbl2;
/* 9. FG2 파일 그룹 접근 불가능이 확인됨 */

BACKUP LOG testDB2 TO DISK='c:\비상로그2.bak'
 WITH NO_TRUNCATE, INIT
GO
/* 10. 비상로그파일 백업 */

USE master
RESTORE DATABASE testDB2 FILEGROUP='FG3'
FROM backupDevice1, backupDevice2, backupDevice3
WITH PARTIAL, NORECOVERY 
GO
RESTORE LOG testDB2  FROM DISK='c:\비상로그2.bak'
GO
/* 11. FG3 파일그룹 복원하면서 비상로그파일도 복원 */

USE testDB2;
SELECT * FROM tbl3;
/* 12. FG3 파일 그룹 접근 가능 확인함 */

BACKUP LOG testDB2 TO DISK='c:\비상로그3.bak'
 WITH NO_TRUNCATE, INIT
GO
USE master
RESTORE DATABASE testDB2 FILEGROUP='FG2'
FROM backupDevice1, backupDevice2, backupDevice3
WITH NORECOVERY 
GO
RESTORE LOG testDB2  FROM DISK='c:\비상로그3.bak'
GO
/* 13. 위와 마찬가지로 FG2 파일그룹도 복원하기 위해
비상로그백업 후 파일 복원 */



-- 출처 : 뇌를 자극하는 SQL SERVER 2008