[MSSQL] Database 서버 전체 복구

DB 서버 전체 간단한 복구 방법

• MSSQL 을 기존 서버와 동일한 위치에 설치 (드라이브 폴더 모두)
  - master DB에는 각 DB들의 기존 정보가 들어 있기 때문에 Restore 하고 난 이후에 서비스를 올리면 기존 위치에서 DB들을 찾게 됨.
  - 새로 설치되는 DB는 기존 서버의 버전과 동일해야 master 가 복원됨
• SQL Server 서비스를 Single 모드로 실행 -m 옵션
• Restore Commander 로 Master DB를 복원 (미리 동일한 드라이브 및 위치에 MDF, LDF 들이 있으면 master 복구후에 바로 DB들을 서비스 할 수 있음)
• SQL Server를 정상시작
• msdb 복원.
• 사용자 DB파일들이 만일 기존 서버와 동일한 위치라면 별도 작업 필요 없음.

msdb 를 복원하고 나서 이런 오류가 뜬다면.. 

Service Broker is disabled in MSDB or MSDB failed to start

이런 오류를 만나면 아래의 내용을 참고하면 됨. (아래의 링크는 접기 안에 내용을 첨부했습니다.)

출처 : http://www.lifeasbob.com/code/kb_articles.aspx?article_Id=60&CatId=39

This is the resolution to a problem we encountered on the server VsqlSVC1\SVCCTR1

Problem:

After each restart of the SQL Server services we got the following error messages in the SQL Server error log:

Server-level event notifications can not be delivered. Either Service Broker is disabled in msdb, or msdsb failed to start. Event notifications in other databases could be affected as well. Bring msdb online, or enable Service Broker.

Error: 8355, Severity: 16, State: 1.

Cause:

It seems that Service Broker is enabled by default in MSDB and even though we don’t use service broker directly I assume that SQL Server and MSDB do use it for internal processes.

I ran the following query to identify if MSDB had Service Broker enabled

SELECT is_broker_enabled FROM sys.databases WHERE name ='MSDB'

This returned a value of 0. Service Broker was not enabled.

I then ran the following script to enable service broker.

ALTER DATABASE [MSDB] SET ENABLE_BROKER

The script hung and never completed. Some further investigation showed that the process was being blocked by the MSDB database.

It appears that this usually happens when the MSDB database has been restored from another server, like when you move an instance and want to save some time and the trouble of re-creating the Jobs and schedules.

The issue relates to the service_broker_guid being the same for the msdb database on the new instance and the on the old instance. It is necessary for service broker to have a unique GUID. So we need to create a new broker for the MSDB database on the new instance.

Solution:

To create a new broker, and thus a new GUID for the msdb database we run the following script:

ALTER DATABASE [MSDB] SET NEW_BROKER WITH ROLLBACK IMMEDIATE

ALTER DATABASE [MSDB] SET NEW_BROKER

ALTER DATABASE [MSDB] SET ENABLE_BROKER

This created a new service broker guid and enabled service broker on msdb.