E-mail :
====================================================================================
-- 1. 먼저 시스템 카탈로그를 수정 할 수 있게 해 줍니다.
Use master
Go
Sp_configure 'allow update', 1
Reconfigure with override
Go
-- 2. 해당 DB를 응급 모드(32768 = emergency mode)로 변경 합니다.
update sysdatabases set STATUS=STATUS|32768 where name = 'db_name'
-- 3. SQL 서비스를 재시작 합니다. 재시작 후 해당 DB를 보면 응급모드로 나오게 된다.
-- 서비스 재시작 작업시 서비스 중지 후 시작 전에 기존 LDF 파일을 리네임 또는 Copy하여 반듯이 Backup
-- 4. 로그 파일 재 생성
dbcc rebuild_log ('db_name' , 'ldf 파일의 물리적 경로')
-- ex) dbcc rebuild_log ('TestDB' , 'D:\MSSQL\DATA\testdb_log.ldf')
-- 이 상태 까지 진행을 하게 되면 DB의 Ldf 파일이 초기화 되면서 DB는 DBO만 사용상태로 활성화 됩니다. 그러면 이제 DB를 보통 모드로 바꾸는 작업을 합니다.
-- 5. DB의 상태를 먼저 확인 합니다.
Select status From sysdatabases
-- 이때 아까 복구한 DB의 상태가 '2048 = dbo use only' 상태로 되어 있을 것 입니다.
-- 6. 복구한 DB의 status 복원.
update sysdatabases set status=STATUS&~32768 where name = 'db_name'
--
EXEC SP_RESETSTATSUS 'DBNAME' -- 주의 대상 모드 해제
EXEC SP_CONFIGURE 'ALLOW UPDATES',0
GO
RECONFIGURE WITH OVERRIDE
GO
====================================================================================
SUSPECT -> EMERGENCY (원인분석 & 복구처리) ->NORMAL
2k5
ALTER DATABASE yourDBname SET EMERGENCY -- 응급상태로 변경 // read-only로 표시,로깅비활성,sysadmin만 액세스
DBCC CheckDB ('yourDBname') -- 문제점 파악
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE -- Repar 하려면 싱글유저 상태여야한다.
DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER
ALTER DATABASE yourDBname SET ONLINE
http://www.codeproject.com/kb/reporting-services/sql_2005_suspect_database.aspx
http://lazydeveloper.net/2567064
-- 2k0의 SP_ATTACH_DB_SINGLE_FILE_DB 와 동일
CREATE DATABASE DB명
ON (NAME='DB명', FILENAME='c:\aa.mdf')
FOR ATTACH_REBUILD_LOG
-- 2k0의 dbcc rebuild_log와 동일
ALTER DATABASE dbname REBUILD LOG ON
(NAME=logname, FILENAME='LDF파일경로\logname.ldf')
2k0
응급모드 변경
USE MASTER
GO
EXEC SP_CONFIGURE 'ALLOW UPDATES',1
GO
RECONFIGURE WITH OVERRIDE
GO
UPDATE SYSDATABASES SET STATUS=STATUS|32768 WHERE NAME ='DBNAME' -- 응급모드 설정
DBCC REBUILD_LOG('DBNAME', 'H:\MSSQL\DATA\DBNAME_LOG2.LDF') -- 로그파일 재생성
DBCC CHECKDB('DBNAME') -- 원인분석
UPDATE SYSDATABASES SET STATUS=STATUS&~32768 WHERE NAME ='DBNAME' -- 응급모드 해제
EXEC SP_RESETSTATSUS 'DBNAME' -- 주의 대상 모드 해제
DBCC DBRECOVER('DBNAME', IGNOREERRORS) -- DB 재시작, 오류발생해도 복원처리
Alter Database TestDB Set single_user with rollback immediate -- 단일 사용자 모드 설정
DBCC CHECKDB('DBNAME', REPAIR_ALLOW_DATA_LOSS) -- 손상된 페이지 복구
DBCC CHECKDB('DBNAME') -- 복구 확인
ALTER DATABASE yourDBname SET MULTI_USER WITH NO_WAIT -- 단일 사용자 모드 해제
EXEC SP_CONFIGURE 'ALLOW UPDATES',0
GO
RECONFIGURE WITH OVERRIDE
GO
create database 디비명 on (filename = 'C:\파일명.mdf') for ATTACH_REBUILD_LOG;
[참고] Rebuild_log 서스펙트 suspect (sqlmvp) |작성자 왕눈봄이
-- 1. 먼저 시스템 카탈로그를 수정 할 수 있게 해 줍니다.
Use master
Go
Sp_configure 'allow update', 1
Reconfigure with override
Go
-- 2. 해당 DB를 응급 모드(32768 = emergency mode)로 변경 합니다.
update sysdatabases set STATUS=STATUS|32768 where name = 'db_name'
-- 3. SQL 서비스를 재시작 합니다. 재시작 후 해당 DB를 보면 응급모드로 나오게 된다.
-- 서비스 재시작 작업시 서비스 중지 후 시작 전에 기존 LDF 파일을 리네임 또는 Copy하여 반듯이 Backup
-- 4. 로그 파일 재 생성
dbcc rebuild_log ('db_name' , 'ldf 파일의 물리적 경로')
-- ex) dbcc rebuild_log ('TestDB' , 'D:\MSSQL\DATA\testdb_log.ldf')
-- 이 상태 까지 진행을 하게 되면 DB의 Ldf 파일이 초기화 되면서 DB는 DBO만 사용상태로 활성화 됩니다. 그러면 이제 DB를 보통 모드로 바꾸는 작업을 합니다.
-- 5. DB의 상태를 먼저 확인 합니다.
Select status From sysdatabases
-- 이때 아까 복구한 DB의 상태가 '2048 = dbo use only' 상태로 되어 있을 것 입니다.
-- 6. 복구한 DB의 status 복원.
update sysdatabases set status=STATUS&~32768 where name = 'db_name'
--?? sp_resetstatus
-- 7. EM을 재연결 하면 DB의 상태가 보통으로 변경 됨을 확인 할 수 있습니다.
-- 8. 이제 DB를 다시 먼저 시스템 카탈로그를 수정 할 수 없게 변경해 줍니다.
Use master
Go
Sp_configure ‘allow update’, 0
Reconfigure with override
Go
-- 여기까지 진행이 되었으면 이제는 DB 일관성 검사를 진행 하여 잘 못된 부분이 없는지 확인 합니다.
-- 9. DBCC Checkdb
-- 일관성 오류가 있는지 검사 합니다.
-- 10. 일관성 오류를 수정하기 위해 user를 싱글 유저로 변경 합니다.
Alter Database TestDB Set single_user with rollback immediate
-- 11. 오류를 수정 합니다.
DBCC CheckDB('db_name', REPAIR_ALLOW_DATA_LOSS)
-- Ex) DBCC CheckDB ('TestDB' , REPAIR_ALLOW_DATA_LOSS)
-- 12. 오류를 수정 하였다면 다시 DBCC Checkdb 를 수행 합니다.
-- 13. 오류가 더 이상 없다면 user모드를 다시 변경 하여 줍니다.
User master
go
Alter Database TestDB Set multi_user
go
-- 작업 완료
http://laigo.kr/27
====================================================================================
[Tip]죽어버린 Sharepoint 팀 사이트를 살려라 1 - Suspect 상태의 DB 회복시키기
죽어버린 Sharepoint 팀 사이트를 살려라. 사건개요 보기
사고 직후, DB를 SQL Management Studio를 열었더니 DB는 오프라인 상태로 되어 있었고, DB 이름 옆에 (Suspect)라고 되어 있었다. DB에 손상이 있었구나라는 짐작을 할 수 있었다.
참고로 Database의 각 State는 다음과 같다.
상태 정의
ONLINE
데이터베이스는 접속이 가능한 상태이다. 비록 리커버리의 Undo가 끝나지 않았어도 , 프라이머리 파일 그룹이 온라인 상태이다.
OFFLINE
데이터베이스는 접속 불가능한 상태이다. 사용자가 명시적으로 offline으로 지정했다면, 그것을 다시 되돌리기 전까지는 데이터베이스는 계속 offline상태로 머무르게 된다. 예를 들면 새로운 디스크로 파일을 옮기기 위해서 offline모드로 바꿀 수 있다. 이 파일 이동 작업이 끝나면 다시 online으로 되돌릴 수 있다.
RESTORING
프라이머리 파일 그룹의 하나 이상의 파일이 복구되고 있을 때, 혹은 세컨더리 파일 그룹의 파일이 복구되고 있는 상태이다. 데이터베이스 접속은 불가능하다.
RECOVERING
데이터베이스가 리커버리되고 있는 상태이다. 리커버리 프로세스는 일시적인 것으로, 리커버리가 성공되면 자동적으로 데이터베이스는 온라인 상태로 복구된다. 하지만 리커버리가 실패하면 데이터베이스는 suspect 상태로 바뀌게 된다. 이 때도 역시 데이터베이스 접속은 불가능하다.
RECOVERY PENDING
리커버리 도중에 리소스 관련 문제가 발생한 상태이다. 데이터베이스가 손상된 것은 아니지만, 파일이 없어졌을 수도 있고 시스템 상의 리소스 제약때문에 문제가 발생했을 수도 있다. 데이터베이스 접속은 불가능하다. 이 문제를 풀기 위해서는 사용자의 행동이 필요하며, 그래야만이 리커버리 프로세스가 완료될 수 있다.
SUSPECT
프라이머리 파일 그룹에 문제가 있거나 손상된 것이다. SQL 서버를 다시 시작시켜도 데이터베이스는 복구되지 않는다. 데이터베이스 접속은 불가능하다. 문제를 해결하기 위해서는 사용자의 행동이 필요하다.
EMERGENCY
이 상태는 사용자가 설정했을 때만 가능하다. 데이터베이스가 single-user 모드일 때에만 데이터베이스는 수리(Repair)와 복원이 가능하다. 이때 데이터베이스는 읽기 전용이며 로깅이 꺼지며 sysadmin 그룹의 사용자만이 접속이 가능하다. 이 Emergency 상태는 장애 복구의 용도로 사용된다. 예를 들면 Suspect 상태의 데이터베이스는 Emergency상태로 설정할 수 있다. 이 때 system administrator는 데이터베이스에 읽기 전용으로 접속할 수 있다. sysadmin그룹의 사용자만이 데이터베이스를 Emergency상태로 설정할 수 있다.
* 죄송하게도 번역이 매끄럽지 못하다. -_-;; 그래도 제 딴에는 최선을 다한 것이니 양해 부탁드린다. 오역이 있다면 덧글로 남겨주시길.. 원본은 SQL 2005 help이다.
가장 내가 우선적으로 해야 할 일은 DB를 살리는 것이었다. suspect 상태의 DB를 살리기 위해서 구글신에게 물어보았더니, 아래와 같은 답을 주셨다.
How to Restore SQL Server 2005 Suspect Database - The Code Project
위 페이지에서 제시하는 해답은 다음과 같다. 아래의 스텝을 순서대로 하면 되겠다.
1. EXEC sp_resetstatus 'yourDBname';
일단, DB에서 Suspect 마크를 없앤다. sp_resetstatus는 바로 그 일을 해주는 내장 스토어드 프로시저이다.
2. ALTER DATABASE yourDBname SET EMERGENCY
데이터베이스 상태를 Emergency로 바꾼다.
3. DBCC checkdb('yourDBname')
Emergency모드이므로, 현재 데이터베이스는 읽기 전용이다. 이 상태에서 먼저 CheckDB를 수행해서 DB문제점을 파악한다.
4. ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Repair를 위해서, DB상태를 Sigle User 모드로 바꾼다.
5. DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
CheckDB를 통해서 Repair를 수행하는데, REPAIR_ALLOW_DATA_LOSS옵션을 사용한다. 위에 링크한 원본 글에도 나와 있지만, 이 옵션외에는 쓸 수가 없다. (위 글의 저자는 자신의 경우 어떤 데이터 LOSS도 없었다고 하는데, 실제로 나는 데이터를 다소 잃었다. -_-;; 이건 팔자려니 생각하자.)
6. ALTER DATABASE yourDBname SET MULTI_USER
Repair가 끝났다면 Database를 다시 Multi User모드로 복원시키면 된다.
위 스텝을 정리하자면 다음과 같다.
EXEC sp_resetstatus 'yourDBname';
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb('yourDBname')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER
이 스텝을 통해서, Suspect 상태의 DB를 거의 복구시킬 수 있었다. 아마 대부분의 경우 위 스텝으로 거의 100% 복구가 가능할 것이라 생각되나.. 내 경우는 조금 달랐다. 문제는 디스크에 오류가 있기 때문에, DB는 복구가 되었지만 Sharepoint 는 문제가 계속 있었던 것이다. 데이터의 읽기는 아무런 문제없이 정상적인데, 문서를 올리거나 게시물을 작성하면 2번에 1번 꼴로 에러가 발생했다. 그래서 어쩔 수 없이 DB를 포함해서 모두를 다른 서버로 옮기기로 했다. 이 이야기는 다음에..
To Be Continued...
2k5
ALTER DATABASE yourDBname SET EMERGENCY -- 응급상태로 변경 // read-only로 표시,로깅비활성,sysadmin만 액세스
DBCC CheckDB ('yourDBname') -- 문제점 파악
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE -- Repar 하려면 싱글유저 상태여야한다.
DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER
ALTER DATABASE yourDBname SET ONLINE
http://www.codeproject.com/kb/reporting-services/sql_2005_suspect_database.aspx
http://lazydeveloper.net/2567064
-- 2k0의 SP_ATTACH_DB_SINGLE_FILE_DB 와 동일
CREATE DATABASE DB명
ON (NAME='DB명', FILENAME='c:\aa.mdf')
FOR ATTACH_REBUILD_LOG
-- 2k0의 dbcc rebuild_log와 동일
ALTER DATABASE dbname REBUILD LOG ON
(NAME=logname, FILENAME='LDF파일경로\logname.ldf')
2k0
응급모드 변경
USE MASTER
GO
EXEC SP_CONFIGURE 'ALLOW UPDATES',1
GO
RECONFIGURE WITH OVERRIDE
GO
UPDATE SYSDATABASES SET STATUS=STATUS|32768 WHERE NAME ='DBNAME' -- 응급모드 설정
DBCC REBUILD_LOG('DBNAME', 'H:\MSSQL\DATA\DBNAME_LOG2.LDF') -- 로그파일 재생성
DBCC CHECKDB('DBNAME') -- 원인분석
UPDATE SYSDATABASES SET STATUS=STATUS&~32768 WHERE NAME ='DBNAME' -- 응급모드 해제
EXEC SP_RESETSTATSUS 'DBNAME' -- 주의 대상 모드 해제
DBCC DBRECOVER('DBNAME', IGNOREERRORS) -- DB 재시작, 오류발생해도 복원처리
Alter Database TestDB Set single_user with rollback immediate -- 단일 사용자 모드 설정
DBCC CHECKDB('DBNAME', REPAIR_ALLOW_DATA_LOSS) -- 손상된 페이지 복구
DBCC CHECKDB('DBNAME') -- 복구 확인
ALTER DATABASE yourDBname SET MULTI_USER WITH NO_WAIT -- 단일 사용자 모드 해제
EXEC SP_CONFIGURE 'ALLOW UPDATES',0
GO
RECONFIGURE WITH OVERRIDE
GO
create database 디비명 on (filename = 'C:\파일명.mdf') for ATTACH_REBUILD_LOG;
[참고] Rebuild_log 서스펙트 suspect (sqlmvp) |작성자 왕눈봄이
-- 1. 먼저 시스템 카탈로그를 수정 할 수 있게 해 줍니다.
Use master
Go
Sp_configure 'allow update', 1
Reconfigure with override
Go
-- 2. 해당 DB를 응급 모드(32768 = emergency mode)로 변경 합니다.
update sysdatabases set STATUS=STATUS|32768 where name = 'db_name'
-- 3. SQL 서비스를 재시작 합니다. 재시작 후 해당 DB를 보면 응급모드로 나오게 된다.
-- 서비스 재시작 작업시 서비스 중지 후 시작 전에 기존 LDF 파일을 리네임 또는 Copy하여 반듯이 Backup
-- 4. 로그 파일 재 생성
dbcc rebuild_log ('db_name' , 'ldf 파일의 물리적 경로')
-- ex) dbcc rebuild_log ('TestDB' , 'D:\MSSQL\DATA\testdb_log.ldf')
-- 이 상태 까지 진행을 하게 되면 DB의 Ldf 파일이 초기화 되면서 DB는 DBO만 사용상태로 활성화 됩니다. 그러면 이제 DB를 보통 모드로 바꾸는 작업을 합니다.
-- 5. DB의 상태를 먼저 확인 합니다.
Select status From sysdatabases
-- 이때 아까 복구한 DB의 상태가 '2048 = dbo use only' 상태로 되어 있을 것 입니다.
-- 6. 복구한 DB의 status 복원.
update sysdatabases set status=STATUS&~32768 where name = 'db_name'
--?? update sysdatabases set status=0 where name='mdo2000db' /* DBO만 사용상태를 정상으로
--?? sp_resetstatus
-- 7. EM을 재연결 하면 DB의 상태가 보통으로 변경 됨을 확인 할 수 있습니다.
-- 8. 이제 DB를 다시 먼저 시스템 카탈로그를 수정 할 수 없게 변경해 줍니다.
Use master
Go
Sp_configure ‘allow update’, 0
Reconfigure with override
Go
-- 여기까지 진행이 되었으면 이제는 DB 일관성 검사를 진행 하여 잘 못된 부분이 없는지 확인 합니다.
-- 9. DBCC Checkdb
-- 일관성 오류가 있는지 검사 합니다.
-- 10. 일관성 오류를 수정하기 위해 user를 싱글 유저로 변경 합니다.
Alter Database TestDB Set single_user with rollback immediate
-- 11. 오류를 수정 합니다.
DBCC CheckDB('db_name', REPAIR_ALLOW_DATA_LOSS)
-- Ex) DBCC CheckDB ('TestDB' , REPAIR_ALLOW_DATA_LOSS)
-- 12. 오류를 수정 하였다면 다시 DBCC Checkdb 를 수행 합니다.
-- 13. 오류가 더 이상 없다면 user모드를 다시 변경 하여 줍니다.
User master
go
Alter Database TestDB Set multi_user
go
-- 작업 완료
http://laigo.kr/27
------------------------------------------------------------------------------------
[해결방법]
해당 데이터베이스에 접근이 불가능하므로 suspect 상태에서는 아무런 작업을 진행할 수 없습니다.
응급 복구 모드로 변경해야 하므로 시스템 테이블을 변경하기 위해 구성 옵션을 변경합니다.
USE MASTER
go
sp_configure 'allow update', 1
RECONFIGURE WITH OVERRIDE
go
해당 데이터베이스 응급 모드(32768 = emergency mode)로 변경합니다.
UPDATE SYSDATABASES SET STATUS = 32768 WHERE NAME = 'laigo'
SQL 서비스를 중지합니다.
기존 LDF 파일의 이름을 변경하거나, 복사해서 다른 곳으로 백업합니다. SQL 서비스를 재시작 합니다.
로그 파일을 재 생성합니다.
DBCC REBUILD_LOG('laigo' , 'D:\DATA\laigo_log.ldf')
DB의 .ldf 파일이 초기화 되면서, DB는 DBO만 사용상태로 활성화 됩니다.
다음 명령으로 복구한 DB의 상태가 ‘2048 = dbo use only’ 상태로 되어 있는지 확인합니다.
SELECT * FROM SYSDATABASES
다음 명령으로 복구한 DB의 모드를 ‘기본모드 = 0’ 변경합니다. 이제 EM을 재연결 하면 DB의 상태가 보통으로
변경 됨을 확인 할 수 있습니다.(사용자 접속이 가능한 상태)
UPDATE SYSDATABASES SET STATUS = 0 WHERE NAME = 'laigo'
일관성 오류를 체크하기 위해 single user 모드로 변경하며 즉시 사용자 접속을 차단합니다.
ALTER DATABASE laigo SET single_user WITH ROLLBACK IMMEDIATE
데이터베이스 손상 여부를 검사하기 위해 일관성 검사를 진행합니다.
명령 실행 후 오류가 발견되지 않았다면 복구가 완료된 상태입니다.
DBCC CHECKDB('laigo')
일관성 오류를 수정하기 위해 다음 명령을 실행합니다.
DBCC CHECKDB('laigo', 옵션)
일관성 오류가 발생되었을 때, 해당하는 적합한 옵션을 부여합니다.
(1) REPAIR_FAST
데이터의 손실 위험 없이 빠른 복구 작업을 수행할 수 있습니다(클러스터되지 않은 인덱스 복구 가능)
(2) REPAIR_REBUILD
REPAIR_FAST이 하는 모든 작업, 인덱스 재생성과 같이 시간이 소요되는 작업이 복구 과정에서 진행됩니다. 역시 데이터의 손실 위험은 없습니다.
(3) REPAIR_ALLOW_DATA_LOSS
REPAIR_REBUILD이 하는 모든 작업, 할당 오류, 구조적 행 오류나 페이지 오류, 손상된 텍스트 개체 삭제를 수정하기 위한 행과 페이징의 할당 및 할당 취소 등의 작업이 행해집니다. 이러한 복구를 할 경우 일부 데이터가 손실될 수 있습니다. 복구를 완료한 후 데이터베이스를 백업합니다.
명령이 완료되면, 다시 한번 DBCC CHECKDB 명령을 수행하여 일관성 오류가 수정되었는지 확인합니다.
DBCC CHECKDB('laigo')
더 이상 오류가 발견되지 않으면, multi_user 모드로 변경하여 사용자 접속이 가능하게 해줍니다.
ALTER DATABASE laigo SET multi_user
사용자 접속이 되고 있는지 모니터링 합니다.
sp_who2
모든 작업이 완료되면, 시스템 카탈로그를 수정 할 수 없게 변경해 줍니다.
sp_configure 'allow update', 0
RECONFIGURE WITH OVERRIDE
go