[MSSQL] 트랜잭션 로그 파일이 손상되었을 때 데이터베이스 복원하는 방법

데이터베이스 운용을 위해 필요한 파일은?

---

DB 를 운영하기 위해서는 데이터파일과 트랜잭션로그파일이 필요합니다.

데이터파일은 말그대로 데이터가 저장된 파일을 의미하고

트랜잭션 로그파일은 수행한 쿼리를 기록합니다.

만약 데이터파일이 유실되었고 백업파일이 없다면 복원이 불가능하지만

트랜잭션파일이 유실된경우에는  현재 데이터파일에 있는 데이터만을 살릴 수 있습니다.

* 커밋되지 않은 트랜잭션은 유실됩니다.

트랜잭션 로그파일이 손상된 경우 복원하는 방법

---

1. 테스트 데이터베이스와 테이블 생성

TEST 데이터베이스가 있고, 테스트를 위해 데이터를 저장할 샘플 테이블을 하나 생성합니다.

CREATE DATABASE TEST
ON PRIMARY ( NAME = N'Test', FileName = N'C:\SQL Server\Test.mdf' )
LOG ON     ( NAME = N'Test_Log', FileName = N'C:\SQL Server\Test_Log.ldf' )
 
ALTER DATABASE TEST SET RECOVERY FULL
 
 
SELECT * INTO TEST.dbo.T1 FROM sys.columns

2. 데이터 적재하는 동안 트랜잭션 로그파일 유실

데이터를 적재하는 동안, 트랜잭션 로그파일이 유실되었음을 가정해 봅니다.

INSERT INTO TEST.dbo.T1 SELECT t.* FROM TEST.dbo.T1 t
                                   CROSS JOIN sys.columns a
                                   CROSS JOIN sys.columns b
                                   CROSS JOIN sys.columns c

로그파일이 사용되고 있는 동안에는 파일 강제 삭제가 불가능하기 때문에

다른세션에서 DB 를 강제 셧다운 한 후 로그파일을 삭제해 봅니다. ( 운영체제에서 Del 키로 삭제 )

SHUTDOWN WITH NOWAIT

데이터를 적재하던 쿼리는 아래와 같은 오류와 함께 적재가 실패합니다.

메시지 64, 수준 20, 상태 0, 줄 23 서버로부터 결과를 수신하는 동안 전송 수준 오류가 발생했습니다. (provider: TCP Provider, error: 0 - 지정된 네트워크 이름을 더 이상 사용할 수 없습니다.)

3. SQL Server 재 구동 및 DB 상태 확인

SQL Server 를 다시 구동해 봅니다.

TEST DB 가 '복구 보류 중' 상태로 변경되었습니다.

4. 트랜잭션 로그파일이 유실된 데이터베이스 DROP

복구 보류 중인 데이터베이스를 DROP 합니다.

OFFLINE 을 먼저하는 이유는

'복구 보류 중' 인 상태에서 DROP 하는 경우 mdf 파일이 삭제되고 이 경우 복구가 불가능하기 떄문입니다.

ALTER DATABASE TEST SET OFFLINE
 
DROP DATABASE TEST

5. 복구용 DB 생성, mdf 경로 변경, 로그파일 유실로 DB 복구

복구를 위해 임시 데이터베이스를 생성합니다.

그 후 새로 생성된 데이터베이스에 강제로 mdf 를 연결하여 데이터베이스를 복원합니다.

이 때 REPAIR_ALLOW_DATA_LOSS 를 사용합니다.

CREATE DATABASE TEST_RECOVERY
ON PRIMARY ( NAME = N'Test_RECOVERY', FileName = N'C:\SQL Server\Test_RECOVERY.mdf' )
LOG ON     ( NAME = N'Test_RECOVERY_Log', FileName = N'C:\SQL Server\Test_RECOVERY_Log.ldf' )
 
ALTER DATABASE TEST_RECOVERY SET RECOVERY FULL
 
 
ALTER DATABASE TEST_RECOVERY SET OFFLINE
 
ALTER DATABASE TEST_RECOVERY MODIFY FILE(NAME=N'Test_RECOVERY', FILENAME=N'C:\SQL Server\Test.mdf')
ALTER DATABASE TEST_RECOVERY MODIFY FILE(NAME=N'Test_RECOVERY_Log', FILENAME=N'C:\SQL Server\Test_RECOVERY_Log.ldf' )
 
 
ALTER DATABASE TEST_RECOVERY SET EMERGENCY
 
ALTER DATABASE TEST_RECOVERY SET SINGLE_USER
 
DBCC CHECKDB('TEST_RECOVERY', REPAIR_ALLOW_DATA_LOSS)
 
ALTER DATABASE TEST_RECOVERY SET MULTI_USER

6. 복구용 DB 에서 데이터 확인

USE TEST_RECOVERY
 
sp_spaceused 'T1'