SQL 트랜잭션로그백업에서 복구모델이 대량로그 상태 변경여부 확인하기

SQL Server에서 대량로그작업을 하게되면 LOG파일에 모두 기록을 하게 되고 해당 로그파일이 증가하게 된다.

로그파일이 증가하는 것을 막기 위해서 SQL에서는 복구모델인 [대량로그] 옵션이 있다.

대량로그 백업을 할 경우에 복구모델을 변경하고 작업한 후 다시 원복하는 작업을 했는지 확인해보자.

   

[시나리오]

해당 로그백업 파일에 복구모델을 변경해서 대량로그작업을 하고 다시 복구모델을 변경을 했을 경우에 그 내역을 확인하는 방법이 있을까?

   

[방법]

결론적으로 복구모델을 변경했다면 아래와 같이 확인이 가능하다.

   

CREATE DATABASE BulkLogDB

GO

 

BACKUP DATABASE BulkLogDB TO DISK = N'nul'

GO

 

-- 복구모델 변경 : 대량로그 옵션

USE [master]

GO

ALTER DATABASE BulkLogDB SET RECOVERY BULK_LOGGED WITH NO_WAIT

GO

 

 

-- 대량로그 작업을 위한 복구모델 변경 후 아래와 같이 Bulk 작업을 한다.

USE BulkLogDB

GO

 

    CREATE TABLE test (id int)

    GO

    INSERT INTO test values (1)

    GO

    SELECT * INTO test2

    FROM test

    GO

 

 

-- 대량로그 작업이 완료되었으므로 복구모델을 전체로 변경한다.

USE [master]

GO

ALTER DATABASE BulkLogDB SET RECOVERY FULL WITH NO_WAIT

GO

 

 

-- 로그백업을 하자.

BACKUP LOG [BulkLogDB] TO DISK = N'E:\SQLData9\BulkLogDB1.trn'

WITH NOFORMAT, NOINIT, NAME = N'BulkLogDB-Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

 

-- 로그백업 정보를 확인하자.

RESTORE HEADERONLY

FROM DISK = N'E:\SQLData9\BulkLogDB1.trn'

WITH NOUNLOAD;

GO

   

-- 일반 Insert 후에 다시 백업해보자

USE BulkLogDB

GO

INSERT INTO test values (1)

GO

 

-- 로그백업

BACKUP LOG [BulkLogDB] TO DISK = N'E:\SQLData9\BulkLogDB2.trn'

WITH NOFORMAT, NOINIT, NAME = N'BulkLogDB-Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

 

-- 일반적인 상황에서 로그백업 정보를 확인한다.

RESTORE HEADERONLY

FROM DISK = N'E:\SQLData9\BulkLogDB2.trn'

WITH NOUNLOAD;

GO

   

-- 복구모델 전체에서 Bulk 작업 후의 백업로그를 확인해보자.

USE BulkLogDB

GO

SELECT * INTO test3

FROM test

GO

 

BACKUP LOG [BulkLogDB] TO DISK = N'E:\SQLData9\BulkLogDB3.trn'

WITH NOFORMAT, NOINIT, NAME = N'BulkLogDB-Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

 

-- 복구모델이 전체일때 대량로그 작업을 한 후 로그백업 정보를 확인한다.

RESTORE HEADERONLY

FROM DISK = N'E:\SQLData9\BulkLogDB3.trn'

WITH NOUNLOAD;

GO

   

[참고문서]

RESTORE HEADERONLY(Transact-SQL)

https://msdn.microsoft.com/ko-kr/library/ms178536(v=sql.120).aspx

키워드 : 대량로그백업, 대량로그백업여부