restore master & msdb database

[arvin]

1. 依照一般的restore database 的方式，且overwrite 原本的master & msdb

代碼: 選擇全部

RESTORE DATABASE [master] FROM  DISK = N'master.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO
RESTORE DATABASE [msdb] FROM  DISK = N'msdb.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO

2. 若新的server 路徑跟舊的不一樣時，修改 syssubsystems 的subsystem 路徑

代碼: 選擇全部

EXEC sp_configure 'allow updates', 1 
reconfigure with override
GO
update msdb.dbo.syssubsystems
set subsystem_dll= replace(subsystem_dll,'D:Program Files','C:Program Files') 
FROM msdb.dbo.syssubsystems
where subsystem_dll like 'D:Program Files%'
GO
EXEC sp_configure 'allow updates', 0
reconfigure with override
GO
-- ensure the change took
select subsystem, subsystem_dll, agent_exe
from msdb.dbo.syssubsystems
where subsystem_dll like 'c:Program Files%'
GO

3.最後變更 SQL Server 執行個體的服務主要金鑰

代碼: 選擇全部

ALTER SERVICE MASTER KEY FORCE REGENERATE
GO