SQL 명령어를 통한 디비 작업

1. 데이타 베이스 생성
- 하나의 데이타 파일과 하나의 트랜젝션 로그 파일
USE master
GO

CREATE DATABASE Sales
ON
( NAME=Sales_data,
  FILENAME='C:\temp\saledata.mdf',
  SIZE=10MB,
  MAXSIZE=50MB,
  FILEGROWTH=5
)
LOG ON
(
 NAME='Sales_log',
 FILENAME='D:\Log\salelog.ldf',
 SIZE=5MB,
 MAXSIZE=25MB,
 FILEGROWTH=5MB
)
GO

- 두개 이상의 데이타 파일과 하나의 트랜젝션 로그 파일, 파일 그룹
USE master
GO

CREATE DATABASE Sales
-- 주 파일 그룹 지정
ON PRIMARY
( NAME=Sales_data1,
  FILENAME='C:\temp\saledata1.mdf',
  SIZE=10MB,
  MAXSIZE=50MB,
  FILEGROWTH=15%),
 -- 파일 그룹 지정
 FILEGROUP SalesGR1
( NAME=Sales_data2,
  FILENAME='C:\temp\saledata2.ndf',
  SIZE=10MB,
  MAXSIZE=50MB,
  FILEGROWTH=15%),
( NAME=Sales_data3,
  FILENAME='C:\temp\saledata3.ndf',
  SIZE=10MB,
  MAXSIZE=50MB,
  FILEGROWTH=15%),
 -- 파일 그룹 지정
 FILEGROUP SalesGR2
( NAME=Sales_data4,
  FILENAME='C:\temp\saledata4.ndf',
  SIZE=10MB,
  MAXSIZE=50MB,
  FILEGROWTH=10%
)
LOG ON
(
 NAME='Sales_log1',
 FILENAME='D:\Log\salelog1.ldf',
 SIZE=5MB,
 MAXSIZE=25MB,
 FILEGROWTH=5MB
),
(
 NAME='Sales_log2',
 FILENAME='D:\Log\salelog2.ldf',
 SIZE=5MB,
 MAXSIZE=25MB,
 FILEGROWTH=5MB
)
GO

2. 데이타 베이스 내용 확인 프로시저
EXEC sp_helpdb Sales

3. 데이타 베이스 분리
EXEC sp_detach_db db_name

4. 데이타 베이스 붙이기
- 주의
다른 서버에 있는 데이타 베이스 정보를 붙이기 위해서는
서버의 문자셋이나 소트방식을 잘 맞추셔야합니다.
이전에서 사용하던 DB의 소트 방식등을 먼저 확인하신후 다른 서버로
mdf화일 등을 복사해 오셔서 붙여야함.

- 방법 1
CREATE DATABASE db_name
ON PRIMARY (FILENAME='C:\temp\db_name.mdf')
FOR ATTACH
GO

- 방법 2
EXEC sp_attach_db @dbname='db_name',
@filename1='c:\temp\db_name.mdf',
@filename2='c:\temp\db_name.ldf'

- 방법 3
EXEC sp_attach_single_file_db 'pubs'
, 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'

5. 데이타 베이스 파일 증가 시키기
- 파일 사이즈 증가
USE master
GO
ALTER DATABASE db_name
MODIFY FILE
(
 NAME=db_name_data,
 SIZE=20MB
)
GO

- 파일 개수 증가
ALTER DATABASE db_name
ADD FILE
(
 NAME=db_name_data,
 FILENAME='d:\temp\db_name_data.ndf',
 SIZE=10MB,
 MAXSIZE=100MB,
 FILEGROWTH=15%
)
GO

6. 데이타 베이스 파일 그룹 추가
--데이타 베이스 TEST에 FileGroup1을 추가한다.
ALTER DATABASE TEST
ADD FILEGROUP FileGroup1
GO

--데이타베이스 FileGroup1에 파일 TEST_Data1_FG3을 추가하기
ALTER DATABASE TEST
ADD FILE
(
 NAME=TEST_Data1_FG3,
 FILENAME='d:\temp\TEST_Data1_fg3.ndf',
 SIZE=10MB,
 MAXSIZE=100MB,
 FILEGROWTH=10%
)
TO FILEGROUP FileGroup3

--TEST 데이터베이스의 FileGroup3을 기본 파일 그룹으로 지정
ALTER DATABASE TEST
MODIFY FILEGROUP FileGroup3 DEFAULT

)

7. 데이타 베이스 로그 파일 추가
USE Master
GO
ALTER DATABASE TEST
ADD LOG FILE
(
 NAME=testlog,
 FILENAME='d:\temp\testlog.ldf',
 SIZE=10MB,
 MAXSIZE=100MB,
 FILEGROWTH=5MB
)
GO

8. 데이타 베이스 데이타 파일 삭제
-- 데이타 파일을 비운후
USE TEST
GO
DBCC SHRINKFILE(Test_data1,emptyfile)
GO
-- 데이타 파일을 삭제한다.
USE master
GO
ALTER DATABASE TEST
REMOVE FILE Test_data1
GO

-- 로그 파일 비우기
USE pubs
GO
DBCC SHRINKFILE (pubs_log, TRUNCATEONLY)

OR

backup log database_name with no_log

OR

EXEC sp_dboption '디비명','trunc. log on chkpt.','true'

OR
데이타베이스의 복구 모델을 simple(단순)모델로 바꿔도 동일함
ALTER DATABASE 디비명 SET recovery simple

--작업을 수행하기전 만약을 위해 풀백업을 받는다.
--해당하는 DB를 detach로 떼어낸다.
EXEC sp_detach_db 'pubs2', 'true'
--잠시 ldf 화일의 이름을 바꿔 두자. - 만약을 위해 지우지 않는다.
--mdf 화일만으로 다시 붙인다.
--이때 ldf(로그 화일)화일은 최소크기인 1M로 재생성된다.
EXEC sp_attach_single_file_db 'pubs'
, 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'

sp_dboption '디비명', 'trunc. log on chkpt.', 'true'
방식으로 설정해 놓으면 로그 백업을 할 필요가 없다. 그러나.. 이렇게 할 경우 복구가 불가능

9. 데이타 베이스 파일 축소
USE master
GO
-- 10% 데이타 파일 축소 [많이 사용하지 않음]
DBCC SHRINKDATABASE(TEST,10)
GO

USE TEST
GO
-- Test_data1 파일 사이즈를 5M로 축소
DBCC SHRINKFILETest_data1,5)
GO

10. 데이터 베이스 파일 및 파일 그룹 정보 보기
USE Test
GO
-- 파일 정보
EXEC sp_helpfile
GO

-- 파일 그룹 정보
EXEC sp_helpfilegroup
GO

11. 데이터 베이스 이름 변경하기
use master
GO

--먼저 single 사용자 모드로 바꿔야 합니다.
sp_dboption 'TEST', 'single user', 'TRUE'

--sp_renamedb '이전디비명', '바꿀DB명' 으로 지정합니다.
EXEC sp_renamedb 'TEST', 'KONAN_TEST'

--다음 single 사용자 모드를 다시 원상태로 바꿉니다.
sp_dboption 'KONAN_TEST', 'single user', 'FLASE'

12. OSQL를 이용한 데이타 베이스 정보 확인
OSQL -Usa -PXXX -S192.168.20.11
USE MyDB
GO
sp_helpfile
GO
sp_helpdb MyDB
GO
sp_helpfilegroup
GO
sp_helpdb
GO

12. 테이블 정보 확인 프로시저
sp_help table_name

13. 테이블 변경
-- 컬럼 변경
ALTER TABLE TEST
ALTER COLUMN name varchar(20) NOT NULL

-- 컬럼 추가
ALTER TABLE TEST
ADD gender bit NOT NULL

-- 컬럼 삭제
ALTER TABLE Test
DROP COLUMN birthday

13. 데이터베이스를 파일 그룹, 파일, 각 파일 그룹별로 테이블 생성
--master database를 사용합니다.
USE master
GO

--만약 FG_sales라는 데이터베이스가 존재하면 지웁니다.
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'FG_sales')
DROP DATABASE [FG_sales]
GO

--FG_sales 데이터베이스를 세개의 화일그룹 을 이용해 생성합니다.
CREATE DATABASE FG_Sales
ON PRIMARY
( NAME = SPri1_dat,
FILENAME = 'c:\temp\FG_SPri1dat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
( NAME = SPri2_dat,
FILENAME = 'c:\temp\FG_SPri2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
FILEGROUP SalesGroup1
( NAME = SGrp1Fi1_dat,
FILENAME = 'c:\temp\FG_SG1Fi1dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
( NAME = SGrp1Fi2_dat,
FILENAME = 'c:\temp\FG_SG1Fi2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
FILEGROUP SalesGroup2
( NAME = SGrp2Fi1_dat,
FILENAME = 'c:\temp\FG_SG2Fi1dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
( NAME = SGrp2Fi2_dat,
FILENAME = 'c:\temp\FG_SG2Fi2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
FILENAME = 'c:\temp\FG_salelog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO

--FG_sales데이터베이스를 사용합니다.
USE FG_sales
GO

--FG_sales데이터베이스의 화일그룹 정보를 봅니다.
EXEC sp_helpfilegroup

--PRIMARY 화일 그룹에 TBL_konan_1을 생성합니다.
CREATE TABLE TBL_konan_1(
konan_id int
)
ON [PRIMARY]
GO

--SalesGroup1에 화일 그룹에 TBL_konan_2 테이블을 생성
CREATE TABLE TBL_konan_2(
konan_id int
)
ON [SalesGroup1]
GO

--SalesGroup2에 화일 그룹에 생성 TBL_konan_3테이블을 생성합니다.
CREATE TABLE TBL_konan_3(
konan_id int
)
ON SalesGroup2
GO

--각각의 테이블의 정보들중 화일그룹 정보를 봅니다.
sp_help TBL_konan_1
sp_help TBL_konan_2
sp_help TBL_konan_3

14. 테이블 생성,primary,foreign key

CREATE TABLE TEST2
(
aaa varchar(10) not null,
vvv varchar(10) not null
)
GO

CREATE TABLE TEST1
(
 aaa varchar(5) primary key,
 age tinyint null CHECK(age >= 10 AND age <=30),
 bbb varchar(10) not null FOREIGN KEY REFERENCES TEST2(aaa)
 ON UPDATE CASCADE
 ON DELETE NO ACTION
)
GO
--10~30 사이의 나이인 사람만 받는 Check 제약 추가. NOCHECK 옵션 설정
ALTER TABLE konan_test4 WITH NOCHECK
ADD CONSTRAINT konan_chk_test CHECK (나이 >= 10 AND 나이 <=30)

15. 재퀴 트리거 사용 가능하게 설정
--재귀트리거 옵션 지정 sp_dboption을 이용한 방법
sp_dboption 'pubs', 'recursive triggers', 'true'

--SQL2000의 ALTER구문을 이용한 방법
ALTER DATABASE pubs
SET RECURSIVE_TRIGGERS ON

16. 인덱스 생성 및 정보 보기

--간단한 인덱스 생성
CREATE INDEX idx ON konan_test_table (konan_id)
GO
--유니크 인덱스 생성
CREATE UNIQUE INDEX idx ON konan_test_table (konan_data)
GO
--유니크 인덱스 생성 - WITH IGNORE_DUP_KEY(중복키 무시)
CREATE UNIQUE INDEX idx ON konan_test_table (konan_data)
WITH IGNORE_DUP_KEY
GO
--인덱스 정보 조회
sp_helpindex konan_test_table

--인덱스 제거
DROP INDEX konan_test_table.idx

--복합 인덱스 생성
CREATE INDEX 인덱스명 ON 테이블 (컬럼A, 컬럼B)

--인덱스 재생성
CREATE CLUSTERED INDEX idx ON konan_test_table (konan_id)
WITH FILLFACTOR = 80, PAD_INDEX, DROP_EXISTING

--DBCC DBREINDEX - 1개 인덱스인 idx인덱스를 fillfactor 90으로 재생성
DBCC DBREINDEX (konan_test_table, 'idx', 90)

--테이블내 모든 인덱스를 fillfactor 90으로 재생성
DBCC DBREINDEX (konan_test_table, '', 90)
DBCC DBREINDEX는 물리적으로 완전히 모든 행을 재배열 하며 인덱스까지 재구성
합니다. 비록 SQL2000부터는 이 작업이 빨라 지고 온라인 상에서 할 수 있다고
하지만.. 실제 대단한 부하가 걸리는 작업입니다.
이때 물리적으로 모든 행을 재배열 하지 않고 인덱스만 재구성하는 명령이 있으니
바로 DBCC INDEXDEFRAG 입니다. SQL2000만의 기능이기도 하지요.

--DBCC INDEXDEFRAG
DBCC INDEXDEFRAG (konanTestDB, konan_test_table, idx)

이런 식으로 사용 가능하며 비교적 좋은 성능을 보여 줍니다.

* 인덱스 분포도 정보 확인
USE pubs
DBCC SHOW_STATISTICS (authors, UPKCL_auidind)
GO

17. 클러스터 인덱스 생성 및 정보 보기

--클러스터드 인덱스 생성
CREATE CLUSTERED INDEX idx ON konan_test_table (konan_id)
GO
클러스터드 인덱스는 비교적 넌클러스터드 인덱스보다 크기가 작습니다.
아울러 클러스터드 인덱스는 크기를 대략적으로 예측할 수 있는 인덱스이기도 하지요.

구분  클러스터드 인덱스  넌클러스터드 인덱스
차이  물리적으로 행을 재배열  물리적으로 행을 재배열 하지 않음.
크기  비교적 작다.   클러스터드 인덱스보다 크다.
선택도  30% 정도면 사용한다.  3%이내면 사용한다.
최대갯수 테이블당 1개   테이블당 249개

18. 쿼리 실행시 IO 비용 확인

--IO통계 보기
SET STATISTICS IO ON

SELECT * FROM konan_test_table WHERE konan_id = 5000
'konan_test_table' 테이블. 스캔 수 1, 논리적 읽기 수 3, 물리적 읽기 수 0, 미리 읽기 수 0.

--실행계획
--Index Seek and Table Scan
SELECT * FROM konan_test_table WHERE konan_id < 10
--인덱스 서치
'konan_test_table' 테이블. 스캔 수 1, 논리적 읽기 수 11, 물리적 읽기 수 0, 미리 읽기 수 0.
SELECT * FROM konan_test_table WHERE konan_id < 30
--풀스캔
'konan_test_table' 테이블. 스캔 수 1, 논리적 읽기 수 145, 물리적 읽기 수 0, 미리 읽기 수 0.
SELECT * FROM konan_test_table WHERE konan_id < 1000
--풀스캔
'konan_test_table' 테이블. 스캔 수 1, 논리적 읽기 수 145, 물리적 읽기 수 0, 미리 읽기 수 0.
--IO통계 끄기
SET STATISTICS IO OFF

19. 테이블에 대한 평균 밀도 확인
--평균 페이지 밀도 판단.
DBCC SHOWCONTIG(konan_test_table)

DBCC SHOWCONTIG이(가) 'konan_test_table' 테이블을 스캔하는 중...
테이블: 'konan_test_table' (1977058079); 인덱스 ID: 0, 데이터베이스 ID: 18
TABLE 수준 스캔이 수행되었습니다.
- 스캔한 페이지................................: 141
- 스캔한 익스텐트..............................: 18
- 전환된 익스텐트..............................: 17
- 익스텐트 당 평균 페이지 수........................: 7.8
- 스캔 밀도[최적:실제].......: 100.00% [18:18]
- 익스텐트 스캔 조각화 상태 ...................: 0.00%
- 페이지 당 사용 가능한 평균 바이트 수.....................: 81.8
- 평균 페이지 밀도(전체).....................: 98.99%
DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

--하나건너 하나씩 데이터 삭제
BEGIN TRAN
DELETE FROM konan_test_table WHERE konan_id % 2 = 0
COMMIT TRAN

--평균 페이지 밀도 판단.
DBCC SHOWCONTIG(konan_test_table)

DBCC SHOWCONTIG이(가) 'konan_test_table' 테이블을 스캔하는 중...
테이블: 'konan_test_table' (1977058079); 인덱스 ID: 0, 데이터베이스 ID: 18
TABLE 수준 스캔이 수행되었습니다.
- 스캔한 페이지................................: 141
- 스캔한 익스텐트..............................: 18
- 전환된 익스텐트..............................: 17
- 익스텐트 당 평균 페이지 수........................: 7.8
- 스캔 밀도[최적:실제].......: 100.00% [18:18]
- 익스텐트 스캔 조각화 상태 ...................: 0.00%
- 페이지 당 사용 가능한 평균 바이트 수.....................: 4018.8
- 평균 페이지 밀도(전체).....................: 50.35%
DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

--인덱스 재생성
CREATE CLUSTERED INDEX idx ON konan_test_table (konan_id)
WITH FILLFACTOR = 80, DROP_EXISTING

--평균 페이지 밀도 판단.
DBCC SHOWCONTIG(konan_test_table)

DBCC SHOWCONTIG이(가) 'konan_test_table' 테이블을 스캔하는 중...
테이블: 'konan_test_table' (1977058079); 인덱스 ID: 1, 데이터베이스 ID: 18
TABLE 수준 스캔이 수행되었습니다.
- 스캔한 페이지................................: 87
- 스캔한 익스텐트..............................: 11
- 전환된 익스텐트..............................: 10
- 익스텐트 당 평균 페이지 수........................: 7.9
- 스캔 밀도[최적:실제].......: 100.00% [11:11]
- 논리 스캔 조각화 상태 ..................: 11.49%
- 익스텐트 스캔 조각화 상태 ...................: 9.09%
- 페이지 당 사용 가능한 평균 바이트 수.....................: 1601.7
- 평균 페이지 밀도(전체).....................: 80.21%
DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

20. 통계데이타 보기
선택도에 따라서 쿼리 최적화기가 인덱스를 타게할지 타게하지 않을지를 결정한다고 말씀 드렸지요.
이때 이 근거가 되는것이 바로 통계 데이터 입니다.
SQL서버2000은 자동으로 통계데이터가 해당하는 인덱스에 대해서 생성되며 자동으로 유지 됩니다.

DBCC SHOW_STATISTICS(konan_test_table,idx)
--통계데이터 UPDATE
UPDATE STATISTICS konan_test_table(idx)

이런 식으로 update가 가능합니다.
튜닝적인 요소로 볼때 이는 수동으로 하지 않는것이 거의 대부분 좋으며 수동으로 할 경우는 대단히 많은 모니터링이 필요 합니다.

21. 데이터 베이스 백업
--화일로 백업
BACKUP DATABASE bkupTest TO DISK='c:\bkupTest_full_bkup' WITH INIT
GO
--복구
restore database bkupTest from disk ='c:\bkupTest_full_bkup'
GO
--영구 백업 디바이스 생성
sp_addumpdevice 'disk', 'bkupTestDevice', 'c:\bkupTestDevice_full_bkup'
GO
--디바이스로 백업 할때는?
BACKUP DATABASE bkupTest TO bkupTestDevice
--복구
restore database bkupTest from bkupTestDevice
GO
* 단순 복구 모델
USE pubs
GO

--pubsDB의 복구 모델을 단순으로 한다.
ALTER DATABASE pubs SET RECOVERY simple

--풀 백업 수행
BACKUP DATABASE pubs TO DISK = 'c:\pubsFull' WITH INIT

--데이터 조회
SELECT TOP 1 title_id, price FROM titles

--조회된 값은? - 꼭 적으세요.

--데이터 수정
UPDATE titles SET price = price * 2

--데이터 조회 - 조회된값 꼭 적으세요.
SELECT TOP 1 title_id, price FROM titles

--복구 모델이 단순일 경우 로그 백업이 가능한가?
BACKUP LOG pubs TO DISK = 'c:\pubsLog' WITH INIT
--불가하다.

--SQL서버 시스템 종료SHUTDOWN

--탐색기등으로 pubs의 데이터 화일인
--mdf 화일을 지우자.
--C:\Program Files\Microsoft SQL Server\MSSQL\Data
--이 기본 경로이다.

--다시 SQL서버를 시작한다.

--master DB 에서 작업한다.
USE master
GO

--데이터 조회가 가능한가?
SELECT TOP 1 title_id, price FROM pubs..titles
--불가하다.

--로그 백업이 가능한가? (이후 풀모델과 비교를 위해서 입니다.)
BACKUP LOG pubs TO DISK = 'c:\pubsLogNoTrunc' WITH NO_TRUNCATE, INIT
--불가하다.

--복구를 진행하자.
RESTORE DATABASE pubs FROM DISK = 'c:\pubsFull'

--데이터를 조회해 보자.
SELECT TOP 1 title_id, price FROM pubs..titles

* 대량 로그 모델
--pubs DB를 대량 로그 모델로 바꾼다.
ALTER DATABASE pubs SET RECOVERY bulk_logged

--데이터베이스를 풀 백업 한다.
BACKUP DATABASE pubs to DISK = 'c:\pubsFull' WITH INIT

--데이터를 조회한다.
SELECT TOP 1 title_id, price FROM pubs..titles
--조회된 값은?

--가격을 *2 한다.
UPDATE pubs..titles SET price = price * 2

--데이터를 조회한다.
SELECT TOP 1 title_id, price FROM pubs..titles
--조회된 값은?

--BULK 작업을 한다.
--다운로드 받은 titles.txt 화일을 c:\에 둔다. - SQL7에서
--BULK 작업은 로그에 남지 않았으나 SQL2000에서는 남는다.
--일반적인 INSERT, UPDATE, DELETE는 기본적으로 로그에 남는다.
--그리고 작업한다.
BULK INSERT pubs..titles FROM 'c:\titles.txt'

--데이터를 조회한다.
SELECT top 2 title_id, price FROM pubs..titles
--Bulk INSERT로 삽입된 값은 BU1001 에 39.98이다.
--SQL7과 틀리게 sp_dboption없이 BULK INSERT가 가능하다.

--로그를 백업하자. - BULK INSERT가 로그에 남을까 남지 않을까?
--SQL7까지는 LOG에 남지 않는 작업이었다.
BACKUP LOG pubs TO DISK = 'c:\pubsLog' WITH INIT

--다시 하나의 데이터를 BULK INSERT로 넣자.
--지금 넣는 값은 title_id가 BU1002이며 가격은 23.9 이다.
BULK INSERT pubs..titles FROM 'c:\titles2.txt'

--데이터를 조회해 보자.
SELECT top 3 title_id, price FROM pubs..titles
--조회된 값은?

--역시나 시스템을 내리고 pubs의 mdf 화일을 지우자.
SHUTDOWN

--다시 SQL서버를 시작 시키자.
USE master
GO

--데이터가 조회 가능한가? - 불가하다.
SELECT TOP 3 title_id, price FROM pubs..titles

--데이터베이스를 복구하자.
--현재 풀백업본을 복구하는 것이다.
--이후 LOG를 복구할게 더 있으므로 옵션으로
--WITH NORECOVERY를 붙인다.
RESTORE DATABASE pubs FROM DISK = 'c:\pubsFull'
WITH NORECOVERY

--로그를 복구하자.
--이때 마지막 복구본 이므로
--WITH RECOVERY 옵션을 준다.
RESTORE LOG pubs FROM DISK = 'c:\pubsLog'
WITH RECOVERY

--데이터를 조회해 보자.
SELECT TOP 3 title_id, price FROM pubs..titles

* 전체 모델
--사용된 데이터베이스를 최초 상태로 복구 합니다.
--맨처음 단순모델할때 사용했던 백업본입니다.
RESTORE DATABASE pubs FROM DISK = 'c:\pubsFull'

--다음 풀모델을 위해 풀백업을 진행합니다.
BACKUP DATABASE pubs TO DISK = 'c:\pubs1' WITH INIT

--데이터를 조회해 보면?
SELECT TOP 1 title_id, price FROM pubs..title
--조회된 값은?

--price를 *2 합니다.
UPDATE pubs..titles SET price = price * 2

--BULK INSERT로 데이터를 넣습니다. 값은 BU1001입니다.
BULK INSERT pubs..titles FROM 'c:\titles.txt'

--데이터를 조회하면?
SELECT TOP 2 title_id, price FROM pubs..titles
--조회된 값은?

--로그를 백업 합니다.
BACKUP LOG pubs TO DISK = 'c:\pubsLog' WITH INIT

--또다시 BULK INSERT를 합니다.
BULK INSERT pubs..titles FROM 'c:\titles2.txt'

--데이터를 조회하면?
SELECT TOP 3 title_id, price FROM pubs..titles
--조회된 값은?

--시스템 셧다운
SHUTDOWN

--pubs의 mdf 화일을 삭제 합니다. 단!
--ldf 화일은 건드리지 마세요.
--복구를 진행합니다.
USE master
GO

--데이터를 조회가 가능한가요? - 불가합니다.
SELECT TOP 2 title_id, price FROM pubs..titles

--로그를 백업합니다.
--바로 풀백업의 중요한 부분으로
--문제가 발생해도 로그가 깨지지 않았다면?
--로그가 백업이 됩니다.
--이때 반드시 WITH NO_TRUNCATE 옵션이 필요합니다.
--INIT는 초기화 시켜 백업하라는 옵션이지요.
BACKUP LOG pubs TO DISK = 'c:\pubsLogNoTrunc' WITH NO_TRUNCATE, INIT
--로그 백업이 성공합니다.

--데이터베이스를 복구해 보도록 하지요.
--복구할게 더 있으니 NORECOVERY로 합니다.
RESTORE DATABASE pubs FROM DISK = 'c:\pubsFull'
WITH NORECOVERY

--로그를 복구해 보도록 하지요.
--복구할게 더 있으니 NORECOVERY로 합니다.
RESTORE LOG pubs FROM DISK = 'c:\pubsLog'
WITH NORECOVERY

--로그를 복구해 보도록 하지요.
--이 로그는? pubs가 깨진후 로그만 백업한것이죠?
--위에서 옵션인 WITH NO_TRUNCATE옵션으로 백업한 로그 입니다.
--복구할게 더이상 없으니 RECOVERY로 합니다.
RESTORE LOG pubs FROM DISK = 'c:\pubsLogNoTrunc'

--데이터를 조회해 보면?
SELECT TOP 3 title_id, price FROM pubs..titles
--어디까지 복구가 되었나요?

* STOPAT을 이용한 복구
이때는 반드시 모델이 전체 모델 또는 대량 로그 이어야 합니다.
--데이터베이스를 초기화 시키기 위해 리스토어 합니다.
RESTORE DATABASE pubs FROM DISK = 'c:\pubsFull'

--데이터베이스를 풀 백업 합니다.
BACKUP DATABASE pubs TO DISK = 'c:\pubs1' WITH INIT

--데이터를 조회합니다.
SELECT TOP 1 title_id, price FROM pubs..titles
--초기화가 잘 되었는지 확인하는 겁니다.

--데이터를 하나 삽입 합니다.
BULK INSERT pubs..titles FROM 'c:\titles.txt'

--데이터를 조회하면?
SELECT TOP 2 title_id, price FROM pubs..titles
--조회된 값은?

--로그를 백업해 보도록 하지요.
BACKUP LOG pubs TO DISK = 'c:\pubsLog' WITH INIT

--역시나 데이터를 하나 더 넣도록 합니다.
BULK INSERT pubs..titles FROM 'c:\titles2.txt'

--데이터를 조회하면?
SELECT TOP 3 title_id, price FROM pubs..titles
--조회된 값은?

--!!자 이제 문제 상황입니다.
--개발자의 실수로 UPDATE를 WHERE절 없이 쳐버렸습니다.
UPDATE pubs..titles SET price = 0

--컨트롤 + Z는 아무리 눌러도 데이터는 복구 안됩니다.
SELECT title_id, price FROM pubs..titles

--바로 문제가 발생한 시각을 알아야만 합니다.
SELECT GETDATE()
--결과 시각은?
--코난이의 경우 아래와 같은 시각 입니다.
2001-08-23 16:06:14.320

--어떻게 해야 할까요?
--1. 즉시 로그를 백업 합니다.
--2. STOP AT 구문으로 복구 합니다.

--로그를 백업 합니다.
BACKUP LOG pubs TO DISK = 'c:\pubsLogNoTrunc' WITH NO_TRUNCATE, INIT

--데이터베이스를 복구합니다.
--이때 첫번째 로그 백업과.. 문제가 발생한후 로그 백업 사이로
--복구를 진행해야 합니다. 그렇지요?
--STOP AT을 적절히 사용하도록 하지요.
--풀 백업본을 리스토어 합니다.
RESTORE DATABASE pubs FROM DISK = 'c:\pubsFull'
WITH NORECOVERY

--첫번째 로그 백업본을 복구 합니다.
RESTORE LOG pubs FROM DISK = 'c:\pubsLog'
WITH NORECOVERY

--문제가 발생한후 로그를 백업한 것입니다.
--약간 특이하게 STOP AT이 있습니다.
--시각 지정은.. 문제가 발생한 시각 - 1분 정도로 하겠습니다.
--2001-08-23 16:06:14.320 빼기 1분
--그래야만.. 문제가 발생하기 바로 전 시각으로 되돌릴 수 있겠지요?
--당연히 문제가 발생한후 즉시 알아야만 데이터 손실을 그만큼 줄일수 있겠죠
--한 일주일 정도 지난후 문제가 발생한걸 알고.. 또한
--중요한 회원 데이터였다면? 생각하기도 끔찍해 지겠지요.
RESTORE LOG pubs FROM DISK = 'c:\pubsLogNoTrunc'
WITH STOPAT = '2001-08-23 16:05:14.320', RECOVERY

--데이터를 조회해 보면?
SELECT TOP 3 title_id, price FROM pubs..titles
--원하는 데이터인 BU1001, BU1002, BU1032가 맞습니까?

22. Checkpoint 발생 주기 변경
--옵션 recovery interval (min)이 안보일 경우
sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE

sp_configure

23. 잠금(블러킹) 확인
EXEC sp_who OR sp_who2를 이용하여 blkBy값을 확인한다. blk 컬럼을 이용하여 차단 주체를 확인할 수 있다.
확인된 SPID 값을 가지고 이 프로세스가 어떤 쿼리를 실행하고 있는지 확인한다.

--DBCC INPUTBUFFER SPID 이렇게 수행합니다.
DBCC INPUTBUFFER (51)

--결과
EventType Parameters EventInfo
---------- ---------- -------------------------------------
Language Event 0        begin tran
                        update titles set price = price * 2

24. 락 타임아웃 시간 조절
--락 타임아웃 시간 조사
select @@lock_timeout

--락 타임아웃 설정 : 단위는 미리초
SET LOCK_TIMEOUT 1800
GO

select @@lock_timeout

[출처] SQL 명령어를 통한 디비 작업|작성자 webman21