백업 파일 확인

• 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

1. 데이터타입: geography

2. 저장: 

INSERT INTO [테이블명] ([geography컬럼명]) values (geography::Point([위도], [경도], 4326));
-- 4326은 우리가 흔히 사용하는 좌표 CRS임

3. 문자열로 변환 조회
단, 이 경우 "POINT ([경도] [위도])" 형태로 반환되므로, 각각 decimal type의 컬럼으로 저장하는 편이 나았음 (다른 방법이 있을 수 있음)

SELECT convert(nvarchar(50), [geography컬럼명]) as [별칭] FROM [테이블명];
-- POINT ([경도] [위도])

4. 특정 위치를 기준으로 반경 5km 조회하기 - geography 타입을 사용한 이유

-- STDistance는 차이를 미터로 반환함

-- 1. 변수선언이 가능한 경우
DECLARE @Origin GEOGRAPHY
SET @Origin = GEOGRAPHY::Point([위도], [경도])
SELECT * FROM [테이블명] WHERE @Origin.STDistance([geography컬럼명]) <= (5 * 1000);

-- 2. DB에서 바로 조회할 경우.
SELECT * FROM [테이블명] WHERE (SELECT [geography컬럼명] FROM [테이블명] WHERE [관리키컬럼명]=[관리키]).STDistance([geography컬럼명]) <= (5 * 1000);

참고: http://rocabilly.tistory.com/48

use [DB명];

SELECT a.TABLE_NAME, a.COLUMN_NAME, '' as description, 
		case when a.CHARACTER_MAXIMUM_LENGTH IS null then a.DATA_TYPE else a.DATA_TYPE + '(' + ltrim(str(a.CHARACTER_MAXIMUM_LENGTH)) + ')' end as DATATYPE, 
        case when a.column_name=isnull(b.column_name,'') and c.constraint_type='PRIMARY KEY' then 'Y' else ' ' end as PK,
        case when a.column_name=isnull(b.column_name,'') and c.constraint_type='FOREIGN KEY' then 'Y' else ' ' end as FK,
        case when a.IS_NULLABLE='YES' then 'Y' else '' end as NOT_NULL,
        case when a.COLUMN_DEFAULT=isnull(a.COLUMN_DEFAULT,'') then a.COLUMN_DEFAULT else ' ' end as DefaultValue
FROM INFORMATION_SCHEMA.COLUMNS  a
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE  b on (a.table_name+a.column_name=b.table_name+b.column_name)
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c on (b.constraint_name=c.constraint_name)
ORDER BY a.TABLE_NAME, a.ORDINAL_POSITION;

설정하는 방법

SET ANSI_WARNINGS OFF;
SET ARITHIGNORE ON;
SET ARITHABORT OFF;

아래는 msdn에 올라온 각각의 옵션에 대한 설명

ANSI_WARNINGS : http://msdn.microsoft.com/ko-kr/library/ms190368.aspx

ARITHIGNORE : http://msdn.microsoft.com/ko-kr/library/ms184341.aspx

ARITHABORT : http://msdn.microsoft.com/ko-kr/library/ms190306.aspx

1. REPLICATE

SELECT REPLICATE('0',6-LEN(EmployeeId)) + EmployeeId

2. RIGHT

SELECT Right('000000' + CONVERT(NVARCHAR, EmployeeID), 6)

개인적으론 2안이 복잡하지 않고 부하가 적어서 더 나은 것 같다.

UPDATE table1
SET col1 = B.col1
FROM table1 A, table2 B
WHERE A.col2=B.col2 AND A.col3=B.col3

UPDATE table_name 
SET col1=CASE ? 
    WHEN '' THEN col1 
    ELSE ? 
END 
WHERE col2=? and col3=?

IF NOT EXISTS (SELECT col1 FROM table_name WHERE col2=? and col3=?) 
    INSERT INTO table_name (col1, col2, col3) VALUES (?, ?, ?) 
ELSE
    UPDATE table_name SET col1=? WHERE col2=? and col3=?