SQL Server/SQL Server Tip

트랜잭션로그 파일이 손상된 데이터베이스 복원 하기

SungWookKang 2015. 7. 23. 10:39
반응형

트랜잭션로그 파일이 손상된 데이터베이스 복원 하기

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012, 2014

 

SQL Server에서 데이터베이스를 연결할 때 sp_attach_db명령을 사용한다. 하나 이상의 로그파일이있는 경우 여러 로그파일을 다시 작성하려면 CREATE DATABASE … FOR ATTACH_REBUILD_LOG를 사용한다. 아래 스크립트는 AdventureWorks2012의 파일을 현재 데이터베이스 연결한다.

EXEC sp_attach_db @dbname = N'AdventureWorks2012',

@filename1 =

N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\AdventureWorks2012_Data.mdf',

@filename2 =

N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\AdventureWorks2012_log.ldf';

 

트랜잭션 로그 파일이 없는 경우에는 SP_ATTACH_SINGLE_FILE_DB 명령을 사용한다.

 

MDF만으로 복원작업을 진행 하였을 때 SQL Server 엔진이 트랜잭션로그 누락과 함께 연결실패를 반환하는 경우의 복원 방법에 대해서 알아보자. 실습을 위해 TestDB 데이터베이스를 생성하고 TestTable을 생성 한다.

USE [master]

GO

 

CREATE DATABASE [TestDB]

CONTAINMENT = NONE

ON PRIMARY

( NAME = N'TestDB_file1',

            FILENAME = N'C:\SQL_Data\TestDB_1.mdf',

            SIZE = 128MB ,

            MAXSIZE = UNLIMITED,

            FILEGROWTH = 64MB)

LOG ON

( NAME = N'TestDB_log_file1',

             FILENAME = N'C:\SQL_Data\TestDB_1.ldf',

             SIZE = 8MB,

             MAXSIZE = 2048GB,

             FILEGROWTH = 8MB)

GO

ALTER DATABASE TestDB SET RECOVERY FULL

GO

 

USE TestDB

GO

 

SELECT * INTO TestTable

FROM sys.objects

 

 

트랜잭션이 실행되는 동안 데이터베이스를 셧다운 할 수 있는 시간을 충분히 만들기 위해 다음 스크립트를 실행 한다.

USE TestDB;

GO

 

INSERT INTO dbo.TestTable

SELECT a.*

FROM TestTable a

CROSS JOIN sys.objects b

CROSS JOIN sys.objects c

CROSS JOIN sys.objects d

 

 

다른 SSMS를 실행하여 다음의 스크립트를 실행하여 SQL Server를 중지 한다.

SHUTDOWN WITH NOWAIT

 

 

 

SQL Server가 중지되면 기존에 실행되던 TestTable 인서트 작업은 취소 된다.

 

 

SQL Server 서비스가 중지 되었을 때 TestDB의 트랜잭션 로그를 삭제 한다. 그리고 SQL Server 서비스를 시작한다. 다음과 같은 복구 보류(Recovery Pending)을 확인 할 수 있다.

 

 

이 시점에서 우리는 일치하지 않는 데이터베이스 파일이 있음을 확인 할 수 있다. 먼저 데이터베이스를 삭제하여 시스템 카탈로그를 정리할 수 있다. 우리는 데이터베이스를 복사 또는 이름을 변경하기 위해 데이터베이스를 오프라인 한다.

USE master

GO

 

ALTER DATABASE TestDB SET OFFLINE

GO

 

그리고 데이터베이스를 삭제하여 시스템 카탈로그의 메타데이터를 정리 한다.

USE master

GO

 

DROP DATABASE TestDB

GO

 

 

현재 트랜잭션로그 파일이 없으므로 sp_attach_single_file_db를 사용하여 데이터베이스를 연결한다. 하지만 다음과 같은 에러를 확인 할 수 있다.

USE master

GO

 

EXEC sys.sp_attach_single_file_db @dbname = 'TestDB',

@physname = N'C:\SQL_Data\TestDB_1.mdf'

GO

 

파일 활성화 오류입니다. 물리적 파일 이름 "C:\SQL_Data\TestDB_1.ldf"이(가) 잘못된 것 같습니다.

데이터베이스를 종료할 때 열려 있는 트랜잭션/사용자가 있거나, 데이터베이스에 검사점이 없거나, 데이터베이스가 읽기 전용이므로 로그를 다시 작성할 수 없습니다. 이 오류는 트랜잭션 로그 파일을 수동으로 삭제했거나 하드웨어 또는 외부 오류로 인해 손상된 경우 발생할 수 있습니다.

메시지 1813, 수준 16, 상태 2, 줄 1

새 데이터베이스 'TestDB'을(를) 열 수 없습니다. CREATE DATABASE가 중단됩니다.

 

 

 

CREATE DATABASE..FOR ATTACH를 시도하였지만 다음과 같은 동일한 오류를 확인 할 수 있다.

USE [master]

GO

CREATE DATABASE [TestDB] ON

( FILENAME = N'C:\SQL_Data\TestDB_1.mdf' )

FOR ATTACH_REBUILD_LOG

GO

 

파일 활성화 오류입니다. 물리적 파일 이름 "C:\SQL_Data\TestDB_1.ldf"이(가) 잘못된 것 같습니다.

데이터베이스를 종료할 때 열려 있는 트랜잭션/사용자가 있거나, 데이터베이스에 검사점이 없거나, 데이터베이스가 읽기 전용이므로 로그를 다시 작성할 수 없습니다. 이 오류는 트랜잭션 로그 파일을 수동으로 삭제했거나 하드웨어 또는 외부 오류로 인해 손상된 경우 발생할 수 있습니다.

메시지 1813, 수준 16, 상태 2, 줄 1

새 데이터베이스 'TestDB'을(를) 열 수 없습니다. CREATE DATABASE가 중단됩니다.

 

 

 

다른 방법으로 우선 복구할 더미 데이터베이스를 생성하고 새로 생성된 데이터베이스에 강제로 MDF와 LDF를 연결하여 데이터베이스를 복원한다. 우선 TestDB_Repair이라는 데이터베이스를 생성한다.

USE [master]

GO

 

CREATE DATABASE [TestDB_Repair]

CONTAINMENT = NONE

ON PRIMARY

( NAME = N'TestDB_Repair_file1',

            FILENAME = N'C:\SQL_Data\TestDB_Repair_1.mdf',

            SIZE = 8MB ,

            MAXSIZE = UNLIMITED,

            FILEGROWTH = 64MB)

LOG ON

( NAME = N'TestDB_Repair_log_file1',

             FILENAME = N'C:\SQL_Data\TestDB_Repair_1.ldf',

             SIZE = 8MB,

             MAXSIZE = 2048GB,

             FILEGROWTH = 32MB)

GO

 

그리고 TestDB_Repair 데이터베이스를 오프라인 한다.

USE master

GO

 

ALTER DATABASE [TestDB_Repair] SET OFFLINE WITH ROLLBACK IMMEDIATE

GO

 

분리된 TestDB_Repair 데이터베이스에 기존의 TestDB의 데이터베이스 MDF를 연결하고 LDF는 TestDB_Repair로 연결한다.

USE master

GO

 

ALTER DATABASE [TestDB_Repair] MODIFY FILE(NAME='TestDB_Repair_file1', FILENAME= 'C:\SQL_Data\TestDB_1.mdf')

ALTER DATABASE [TestDB_Repair] MODIFY FILE(NAME='TestDB_Repair_log_file1', FILENAME= 'C:\SQL_Data\TestDB_Repair_1.ldf')

GO

 

데이터베이스를 온라인 하면 다음과 같은 에러를 확인 할 수 있다.

USE master

GO

 

ALTER DATABASE [TestDB_Repair] SET ONLINE

GO

 

메시지 5173, 수준 16, 상태 1, 줄 2

하나 이상의 파일이 데이터베이스의 주 파일과 일치하지 않습니다. 데이터베이스를 연결하려면 올바른 파일 이름으로 작업을 다시 시도하십시오. 이 데이터베이스가 기존 데이터베이스인 경우에는 파일이 손상되었을 수 있으므로 백업에서 파일을 복원해야 합니다.

로그 파일 'C:\SQL_Data\TestDB_Repair_1.ldf'이(가) 주 파일과 일치하지 않습니다. 이 파일이 다른 데이터베이스에서 작성되었거나 로그가 이전에 다시 작성되었을 수 있습니다.

메시지 5181, 수준 16, 상태 5, 줄 2

데이터베이스 "TestDB_Repair"을(를) 다시 시작할 수 없습니다. 이전 상태로 돌아갑니다.

메시지 5069, 수준 16, 상태 1, 줄 2

ALTER DATABASE 문이 실패했습니다.

 

 

 

데이터베이스를 복원하기 위해 TestDB_Repair 데이터베이스를 비상 모드 및 단일 사용자 모드로 변경한다. DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS 옵션을 실행하여 손실된 데이터는 삭제하고 데이터베이스 복원 작업을 진행 한다. 이때 TRACEON(3604)를 실행하여 모든 로그를 출력하여 확인 할 수 있도록 한다. 실행한다. DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS 작업이 완료 되었으면 다중 사용자 모드로 만들어서 서비스를 사용한다.

USE master

GO

 

DBCC TRACEON(3604)

GO

 

ALTER DATABASE TestDB_Repair SET EMERGENCY

GO

 

ALTER DATABASE TestDB_Repair SET SINGLE_USER

GO

 

DBCC CHECKDB('TestDB_Repair', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS

GO

 

ALTER DATABASE TestDB_Repair SET MULTI_USER

GO

 

 

 

이렇게 해서 트랜잭션 로그가 손상된 데이터베이스에서 일부 데이터 손실을 감안하고(커밋 되지않은 데이터) 복구하는 방법에 대해서 알아 보았다. 하지만 모든 상황에서 복구가 가능한 것은 아니다. 다음의 경우에는 복구 실패한 사례도 있다.

 

 

2015-04-27 / 강성욱 / http://sqlmvp.kr

 

SQLSERVER, MSSQLSERVER, DB복구, MDF복구,트랜잭션로그 손상, DBCC CHECKDB, SP_ATTACH_DB, SP_ATTACH_SINGLE_FILE_DB, RESTORE DATBASE, DB복원

반응형

'SQL Server > SQL Server Tip' 카테고리의 다른 글

SOS_SCHEDURLER_YIELD 대기와 쿼리 식별  (0) 2015.07.23
랜덤 캐릭터 생성하기  (0) 2015.07.23
트랜잭션 로그 백업을 읽고 트랜잭션 발생 시간 및 사용자 찾기  (0) 2015.07.23
RESOURCE_GOVERNOR_IDLE과 쿼리 성능  (0) 2015.07.23
TDE 암호화된 데이터베이스 복원  (0) 2015.07.23

'SQL Server/SQL Server Tip'의 다른글

  • 현재글트랜잭션로그 파일이 손상된 데이터베이스 복원 하기

관련글

티스토리툴바