백업 파일 확인
- RESTORE HEADERONLY FROM DISK='C:\Backup\A.bak';
→ 백업된 SQL Server 버전, 날짜, 종류(FULL/DIFF/LOG) 확인 - RESTORE FILELISTONLY FROM DISK='C:\Backup\A.bak';
→ 데이터/로그 파일 논리명 확인
📌 기본 FULL 백업 복원
sqlcmd -S 서버명\인스턴스명 -E -Q " RESTORE DATABASE B FROM DISK = 'C:\Backup\A.bak' WITH MOVE 'A_Data' TO 'C:\Data\B.mdf', MOVE 'A_Log' TO 'C:\Data\B_log.ldf', REPLACE, STATS=10;"- -S : 서버명\인스턴스명 (로컬 기본 인스턴스면 -S . 가능)
- -E : Windows 인증 (SQL 로그인 쓰려면 -U 사용자 -P 비밀번호)
- MOVE : 논리 파일명을 새 물리 경로로 지정
- REPLACE : 기존 DB 덮어쓰기
- STATS=10 : 진행률 10% 단위 출력
📌 DIFF 백업 복원 (FULL 복원 후)
sqlcmd -S 서버명\인스턴스명 -E -Q " RESTORE DATABASE B FROM DISK = 'C:\Backup\A_diff.bak' WITH NORECOVERY, STATS=10;"- NORECOVERY : 이후 로그 복원을 위해 DB를 복원 모드로 유지
📌 LOG 백업 복원 (DIFF 후 이어서)
sqlcmd -S 서버명\인스턴스명 -E -Q " RESTORE LOG B FROM DISK = 'C:\Backup\A_log.trn' WITH RECOVERY, STATS=10;"- RECOVERY : 마지막 로그 복원 후 DB를 ONLINE으로 전환
📌 복원 후 무결성 검사
sqlcmd -S 서버명\인스턴스명 -E -Q " DBCC CHECKDB ('B') WITH NO_INFOMSGS, ALL_ERRORMSGS;"📌 자주 쓰는 팁
- 헤더 확인 : RESTORE HEADERONLY FROM DISK='A.bak'
- 파일 목록 확인 : RESTORE FILELISTONLY FROM DISK='A.bak'
- 복원 진행률 확인 : 다른 세션에서
SELECT percent_complete, estimated_completion_time FROM sys.dm_exec_requests WHERE command = 'RESTORE DATABASE';
SSMS 에서 잘 안될때는 sqlcmd로 하면 잘 된다.
추가 점검/최적화 (선택)
- sp_updatestats 실행 → 통계 갱신
- 인덱스 재구성/재빌드 → 성능 최적화
- 새 FULL 백업 생성 → 이후 백업 체인 새로 시작
🔧 sp_updatestats 실행
USE DB명;
EXEC sp_updatestats;
🔧 인덱스 재구성 (Reorganize)
- 특징: 조각난 인덱스를 정리해서 페이지를 압축하고, 구조를 최적화
- 장점: 빠르고 온라인으로 실행 가능 (대부분의 경우 DB 사용 중에도 가능)
- 예시:
USE B; ALTER INDEX ALL ON dbo.테이블명 REORGANIZE;
🔧 인덱스 재빌드 (Rebuild)
- 특징: 인덱스를 완전히 새로 만드는 작업
- 장점: 조각난 정도가 심할 때 가장 효과적
- 단점: 시간이 오래 걸리고, 기본적으로 오프라인 작업 (Enterprise Edition은 ONLINE 옵션 가능)
- 예시:
USE B; ALTER INDEX ALL ON dbo.테이블명 REBUILD WITH (FILLFACTOR = 90, ONLINE = ON);
- FILLFACTOR : 페이지에 여유 공간을 남겨둬서 이후 삽입 성능 개선
- ONLINE = ON : Enterprise Edition에서만 지원, 작업 중에도 테이블 사용 가능
🔧 전체 DB 인덱스 최적화
- 모든 테이블에 대해 인덱스를 재구성/재빌드하려면:
USE B; EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REBUILD';
- 또는
USE B; EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REORGANIZE';
📌 선택 기준
- 조각난 정도가 10~30% → REORGANIZE
- 조각난 정도가 30% 이상 → REBUILD
- 조각난 정도 확인:
SELECT
dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID('B'), NULL, NULL, NULL, 'LIMITED') AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID('B');
📌 자동 인덱스 유지보수 스크립트 (Enterprise Edition용)
USE B;
GO
DECLARE @TableName NVARCHAR(128);
DECLARE @IndexName NVARCHAR(128);
DECLARE @SQL NVARCHAR(MAX);
-- 인덱스 조각난 정도 확인 후 자동 처리
DECLARE cur CURSOR FOR
SELECT
t.name AS TableName,
i.name AS IndexName,
ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
JOIN sys.tables t ON ips.object_id = t.object_id
WHERE i.type_desc IN ('CLUSTERED', 'NONCLUSTERED')
AND ips.page_count > 100; -- 작은 인덱스는 제외
OPEN cur;
FETCH NEXT FROM cur INTO @TableName, @IndexName, @SQL;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @SQL BETWEEN 10 AND 30
SET @SQL = 'ALTER INDEX [' + @IndexName + '] ON [' + @TableName + '] REORGANIZE;';
ELSE IF @SQL > 30
SET @SQL = 'ALTER INDEX [' + @IndexName + '] ON [' + @TableName + '] REBUILD WITH (ONLINE = ON);';
ELSE
SET @SQL = NULL;
IF @SQL IS NOT NULL
BEGIN
PRINT '실행: ' + @SQL;
EXEC (@SQL);
END
FETCH NEXT FROM cur INTO @TableName, @IndexName, @SQL;
END
CLOSE cur;
DEALLOCATE cur;
GO📌 설명
- sys.dm_db_index_physical_stats: 인덱스 조각난 정도 확인
- 조건:
- page_count > 100: 너무 작은 인덱스는 제외 (효과 없음)
- ONLINE 옵션: Enterprise Edition에서만 지원, Standard Edition은 제거해야 함
📌 실행 팁
- 복원 직후나 주기적인 유지보수 작업에 사용
- 실행 전 반드시 DB를 사용하지 않는 시간대에 돌리는 게 안전
- 로그가 많이 쌓일 수 있으니, 필요하면 로그 백업도 고려
📌 자동 인덱스 유지보수 스크립트 (Standard Edition용)
USE B;
GO
DECLARE @TableName NVARCHAR(128);
DECLARE @IndexName NVARCHAR(128);
DECLARE @Frag FLOAT;
DECLARE @SQL NVARCHAR(MAX);
DECLARE cur CURSOR FOR
SELECT
t.name AS TableName,
i.name AS IndexName,
ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
JOIN sys.tables t ON ips.object_id = t.object_id
WHERE i.type_desc IN ('CLUSTERED', 'NONCLUSTERED')
AND ips.page_count > 100; -- 작은 인덱스 제외
OPEN cur;
FETCH NEXT FROM cur INTO @TableName, @IndexName, @Frag;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Frag BETWEEN 10 AND 30
SET @SQL = 'ALTER INDEX [' + @IndexName + '] ON [' + @TableName + '] REORGANIZE;';
ELSE IF @Frag > 30
SET @SQL = 'ALTER INDEX [' + @IndexName + '] ON [' + @TableName + '] REBUILD;';
ELSE
SET @SQL = NULL;
IF @SQL IS NOT NULL
BEGIN
PRINT '실행: ' + @SQL;
EXEC (@SQL);
END
FETCH NEXT FROM cur INTO @TableName, @IndexName, @Frag;
END
CLOSE cur;
DEALLOCATE cur;
GO
'DB > MSSQL' 카테고리의 다른 글
| SQL Server에서 지도의 위도, 경도 저장 및 초간단 사용법 정리 (1) | 2024.09.05 |
|---|---|
| 트리거 소스보기 (0) | 2016.10.31 |
| 초간단 트리거 문법 (0) | 2016.08.24 |
| 테이블 명세서 쿼리문 (0) | 2014.09.23 |
| 0을 나누기 에러 대신 null 반환되도록 하기 (0) | 2014.09.19 |
