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
이런 오류를 만나면 아래의 내용을 참고하면 됨. (아래의 링크는 접기 안에 내용을 첨부했습니다.)
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.
Contains one row for each file in a database. This system table is a virtual table; it cannot be updated or modified directly.
Column name
Data type
Description
fileid
smallint
File identification number unique for each database.
groupid
smallint
Filegroup identification number.
size
int
Size of the file (in 8-KB pages).
maxsize
int
Maximum file size (in 8-KB pages). A value of 0 indicates no growth, and a value of -1 indicates that the file should grow until the disk is full.
growth
int
Growth size of the database. A value of 0 indicates no growth. Can be either the number of pages or the percentage of file size, depending on value of status. If status contains 0x100000, then growthis the percentage of file size; otherwise, it is the number of pages.
status
int
Status bits for the growth value in either megabytes (MB) or kilobytes (K).
0x1 = Default device. 0x2 = Disk file. 0x40 = Log device. 0x80 = File has been written to since last backup. 0x4000 = Device created implicitly by the CREATE DATABASE statement. 0x8000 = Device created during database creation. 0x100000 = Growth is in percentage, not pages.
perf
int
Reserved.
name
nchar(128)
Logical name of the file.
filename
nchar(260)
Name of the physical device, including the full path of the file.
