sqlcmd 복원 스크립트 템플릿 및 복원 후처리 스크립트 정리

 백업 파일 확인

• 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