데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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

 

  • 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

 

 

 

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

 

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
2030 Error 17053 타사 네트워크 장치의 SMB 파일 공유 오류 jevida(강성욱) 2017.01.11 1151
2029 최신 SQL Server Native Client 설치하기 jevida(강성욱) 2017.01.11 1784
2028 Sys.dm_os_waiting_tasks를 활용한 실행중인 병렬쿼리 확인 jevida(강성욱) 2017.01.11 1522
2027 DBCC DROPCLEANBUFFERS가 작동하지 않을 때 jevida(강성욱) 2017.01.11 1679
2026 손상된 부트페이지 복구하기 jevida(강성욱) 2017.01.11 1827
2025 Temp table 객체 생성시 세션간 충돌하지 않는 이유 jevida(강성욱) 2017.01.11 1625
2024 SQL Server 데이터베이스 메일 계정 수정 jevida(강성욱) 2017.01.11 2245
2023 XEvent(확장이벤트)를 활용한 활성 로그 모니터링 하기 jevida(강성욱) 2017.01.11 2217
2022 특정 사용자에 대한 트랜잭션 로그 찾기 jevida(강성욱) 2017.01.11 2235
2021 SQL Server I/O 서브시스템 레이턴시 확인 jevida(강성욱) 2017.01.11 1707
2020 실행계획의 물리 및 논리연산자 설명 jevida(강성욱) 2017.01.11 1802
2019 SQL Server Page Life Expectancy (PLE) jevida(강성욱) 2017.01.11 2335
2018 백업 압축과 추적플래그 3042 jevida(강성욱) 2017.01.11 2071
2017 SQL Server에서 MySQL 링크드서버 연결하기 jevida(강성욱) 2017.01.11 4490
2016 SOS_SCHEDURLER_YIELD 대기와 쿼리 식별 jevida(강성욱) 2017.01.11 3410
2015 랜덤 캐릭터 생성하기 jevida(강성욱) 2017.01.11 2213
» 트랜잭션로그 파일이 손상된 데이터베이스 복원 하기 jevida(강성욱) 2017.01.11 4296
2013 트랜잭션 로그 백업을 읽고 트랜잭션 발생 시간 및 사용자 찾기 jevida(강성욱) 2017.01.11 2716
2012 RESOURCE_GOVERNOR_IDLE과 쿼리 성능 jevida(강성욱) 2017.01.11 2024
2011 TDE 암호화된 데이터베이스 복원 jevida(강성욱) 2017.01.11 2474





XE Login