日常监控涉及的数据表、存储过程抽离到单独用户数据库,新环境中可以通过还原此库及相关作业即可快速完成部署~
1、获取原备份文件
从已部署的实例上备份数据库
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
--备份数据库 BACKUP DATABASE [DBA_Monitor] TO DISK = N'D:\Perfmon\DBA_Monitor_F_20160308.bak' WITH NOFORMAT, NOINIT, NAME = N'DBA_Monitor-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO BACKUP DATABASE [msdb] TO DISK = N'D:\Perfmon\msdb_F_20160308.bak' WITH NOFORMAT, NOINIT, NAME = N'msdb-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO
2、还原数据库
还原DBA_Monitor库及msdb库
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
--监控使用的表、存储过程所在数据库 restore headeronly from DISK = N'F:\TroubleShooting\DBA_Monitor_F_20160308.bak' RESTORE DATABASE [DBA_Monitor] FROM DISK='F:\TroubleShooting\DBA_Monitor_F_20160308.bak' WITH FILE=1, MOVE 'DBA_Monitor' TO 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL08R2\MSSQL\DATA\DBA_Monitor.mdf', MOVE 'DBA_Monitor_log' TO 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL08R2\MSSQL\DATA\DBA_Monitor_log.ldf', NOUNLOAD, REPLACE, STATS=10 GO --包含监控作业的msdb库还原到msdbnew RESTORE DATABASE [msdbnew] FROM DISK = N'F:\TroubleShooting\msdb_F_20160308.bak' WITH FILE = 1, MOVE N'MSDBData' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL08R2\MSSQL\DATA\msdbnew.mdf', MOVE N'MSDBLog' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL08R2\MSSQL\DATA\msdbnew_log.ldf', NOUNLOAD, REPLACE, STATS = 10 GO
3、利用作业迁移的脚本将需要的作业迁移
在where条件增加筛选,迁移相关的作业。有时可能需修改作业步骤,点击编辑作业报错,从ICassFactory为CLSID为{AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2}的COM组件创建实例失败,原因是出现以下错误:c001f011.(Microsoft.Server.manageDTS)
参考链接修改
cd c:\windows\system32 regsvr32 "D:\Program Files\Microsoft SQL Server\100\DTS\Binn\dts.dll"
dts.dll要指定到SQL Server的安装路径,后期查看编辑作业是否还会报错。
也可以参考"编写作业脚本为"中的job存储过程,对作业进行修改
--修改作业步骤对应的数据库 SELECT * FROM msdb.dbo.sysjobs WHERE NAME LIKE '%表大小%' EXEC msdb.dbo.sp_update_jobstep @job_id = '2C4D4932-E019-43BE-8BDE-0A13B2CD158F',@step_id=1, @database_name=N'DBA_Monitor' EXEC msdb.dbo.sp_update_jobstep @job_id = '2C4D4932-E019-43BE-8BDE-0A13B2CD158F',@step_id=2, @database_name=N'DBA_Monitor'
4、运行各迁移作业,检查运行是否正常
运行作业之前,可以将原数据清空
USE DBA_Monitor GO sp_MSforeachtable @command1 = "TRUNCATE TABLE ?"
在运行迁移作业时,发现其中一作业出错,已以用户 NT AUTHORITY\SYSTEM 的身份执行。 无法解决 equal to 运算中 "SQL_Latin1_General_CP1_CI_AS" 和 "Chinese_PRC_CI_AS" 之间的排序规则冲突。 [SQLSTATE 42000] (错误 468). 该步骤失败。
检查对应的存储过程发现是在执行以下语句时报错
FROM #temp a INNER JOIN cte b ON a.jobname=b.jobname AND a.step_id=b.step_id
#temp是通过create table创建的,没有特别设置。cte是从sysjobs、sysjobhistory获取的作业信息。以前也是按这样的步骤操作,并没有报排序规则的错误。
检查msdb、DBA_Monitor数据库排序,发现msdb对应SQL_Latin1_General_CP1_CI_AS,DBA_Monitor对应Chinese_PRC_CI_AS,实例级别对应Chinese_PRC_CI_AS排序规则。查看相关数据库服务器的排序规则都是Chinese_PRC_CI_AS,那自己本地的msdb排序规则怎么变成Latin1?后来想起有一次测试迁移作业,将msdb数据库用X\MSSQL\Binn\Templates\下的msdbdata、msdblog覆盖了!导致msdb库的排序规则与安装时不一致!
解决方案:1、修改数据库排序规则(无效)
USE [master] GO ALTER DATABASE [msdb] COLLATE Chinese_PRC_CI_AS GO
无法对 数据库'msdb' 执行 更改,因为它是系统 数据库。
2、创建#temp表时指定jobname字段的排序规则(有效)
create table #JobRunLongtime ( --jobname varchar(128) not null, jobname varchar(128) COLLATE SQL_Latin1_General_CP1_CI_AS not null,
3、在关联字段后面加上排序规则(有效)
FROM #temp a INNER JOIN cte b ON a.jobname collate SQL_Latin1_General_CP1_CI_AS=b.jobname AND a.step_id=b.step_id
字段step_id为int型,不需要指定为Latin1排序规则。按照方法2或方法3修改后,这个问题被解决,但是作业依旧报错,已以用户 NT AUTHORITY\SYSTEM 的身份执行。 未在此数据库中启用 Service Broker 消息传递功能。请使用 ALTER DATABASE 语句启用 Service Broker 消息传递功能。 [SQLSTATE 42000] (错误 14650). 该步骤失败。
根据提示可知是发送邮件时出错,可参考启用Service Broker;其他数据库邮件错误请参考代理系列中配置数据库邮件。