背景:数据库服务器更换,1、用户数据库迁移(BACKUP/RESTORE);2、数据库登录名/用户迁移(注意孤立用户);3、作业迁移
1、数据库迁移,备份数据库、拷贝备份文件到新服务器,还原数据库
--还原示例
USE [master]
RESTORE DATABASE [DBA_Monitor] FROM DISK = N'D:\rsyncroot\PLAY_DBA_Monitor_F_20150630_1.bak'
WITH FILE = 1
, MOVE N'DBA_Monitor' TO N'D:\LoveGame\Data\DBA_Monitor\DBA_Monitor.mdf'
, MOVE N'DBA_Monitor_log' TO N'D:\LoveGame\Data\DBA_Monitor\DBA_Monitor_log.ldf'
, NOUNLOAD, REPLACE, STATS = 5
GO
View Code
2、数据库登录名迁移,新实例中创建同名数据库登录名,待数据库还原后,使用以下语句处理孤立用户
--孤立用户
USE DBA_Monitor
GO
exec sp_change_users_login 'REPORT'
exec sp_change_users_login 'UPDATE_ONE','用户名','登录名'--更新库下用户的sid成登录名的sid
GO
View Code
创建数据库登录名时带上sid可避免孤立用户的产生。可使用下面脚本导出带sid和password的登录名信息
--导出登录脚本
SELECT 'CREATE LOGIN [' + p.name + '] '
+ CASE WHEN p.type IN ( 'U', 'G' ) THEN 'FROM windows '
ELSE ''
END + 'WITH ' + CASE WHEN p.type = 'S'
THEN 'password = '
+ master.sys.fn_varbintohexstr(l.password_hash)
+ ' hashed, ' + 'sid = '
+ master.sys.fn_varbintohexstr(l.sid)
+ ', check_expiration = '
+ CASE WHEN l.is_expiration_checked > 0
THEN 'ON, '
ELSE 'OFF, '
END + 'check_policy = '
+ CASE WHEN l.is_policy_checked > 0
THEN 'ON, '
ELSE 'OFF, '
END
+ CASE WHEN l.credential_id > 0
THEN 'credential = ' + c.name
+ ', '
ELSE ''
END
ELSE ''
END + 'default_database = '
+ p.default_database_name
+ CASE WHEN LEN(p.default_language_name) > 0
THEN ', default_language = ' + p.default_language_name
ELSE ''
END
FROM sys.server_principals p
LEFT JOIN sys.sql_logins l
ON p.principal_id = l.principal_id
LEFT JOIN sys.credentials c
ON l.credential_id = c.credential_id
WHERE p.type IN ( 'S', 'U', 'G' )
AND p.name NOT IN ( 'sa')
AND p.name NOT LIKE '%##%'
AND p.name NOT LIKE '%NT SERVICE%'
AND p.name NOT LIKE '%NT AUTHORITY%'
View Code
3、作业迁移,如果数据库版本与原数据库版本保持一致,则可直接还原msdb数据库;如果数据库版本不一致,且迁移的作业比较多,可使用以下语句进行迁移
/******************************************************************
【概要说明】
<创建时间> 2015/6/8 11:14
<中文名称> 作业迁移
<功能说明> 通过insert、update语句导入作业信息,目的是将实例A中的作业迁移到实例B中
<调用方式> 手动执行
<执行说明> 先决条件:还原前请先备份两边的msdb数据库,将实例A中的msdb还原到实例B的msdbnew
出错处理:如果出错,请还原原备份文件
注意事项:此代码适用用于数据库版本不一致,且迁移的作业比较多的情况;如果版本号相同,可直接还原数据库
【修订记录】
-------------------------------------------------------------------
<2015/6/8 11:14> <Author>: 创建
******************************************************************/
--作业Job
insert into [msdb].[dbo].[sysjobs]
SELECT a.[job_id]
,a.[originating_server_id]
,'109Job_'+a.[name]--作业名称加前缀区分
,a.[enabled]
,a.[description]
,a.[start_step_id]
,a.[category_id]
,suser_sid()--将所有者更新为当前登录用户
,a.[notify_level_eventlog]
,a.[notify_level_email]
,a.[notify_level_netsend]
,a.[notify_level_page]
,a.[notify_email_operator_id]
,a.[notify_netsend_operator_id]
,a.[notify_page_operator_id]
,a.[delete_level]
,a.[date_created]
,a.[date_modified]
,a.[version_number]
FROM [msdbnew].[dbo].[sysjobs] a
left join [msdb].[dbo].[sysjobs] b
on a.job_id=b.job_id
where a.enabled=1
and b.job_id is null
order by date_created
go
--步骤jobsteps
insert into [msdb].[dbo].[sysjobsteps]
SELECT a.[job_id]
,a.[step_id]
,a.[step_name]
,a.[subsystem]
,a.[command]
,a.[flags]
,a.[additional_parameters]
,a.[cmdexec_success_code]
,a.[on_success_action]
,a.[on_success_step_id]
,a.[on_fail_action]
,a.[on_fail_step_id]
,a.[server]
,a.[database_name]
,a.[database_user_name]
,a.[retry_attempts]
,a.[retry_interval]
,a.[os_run_priority]
,a.[output_file_name]
,a.[last_run_outcome]
,a.[last_run_duration]
,a.[last_run_retries]
,a.[last_run_date]
,a.[last_run_time]
,a.[proxy_id]
,a.[step_uid]
FROM [msdbnew].[dbo].[sysjobsteps] a
inner join [msdb].[dbo].[sysjobs] b
on a.job_id=b.job_id
left join [msdb].[dbo].[sysjobsteps] c
on a.job_id=c.job_id
where c.job_id is null
go
--调度schedules
insert into [msdb].[dbo].[sysschedules]
SELECT a.[schedule_uid]
,a.[originating_server_id]
,a.[name]
,suser_sid()--将所有者更新为当前登录用户
,a.[enabled]
,a.[freq_type]
,a.[freq_interval]
,a.[freq_subday_type]
,a.[freq_subday_interval]
,a.[freq_relative_interval]
,a.[freq_recurrence_factor]
,a.[active_start_date]
,a.[active_end_date]
,a.[active_start_time]
,a.[active_end_time]
,a.[date_created]
,a.[date_modified]
,a.[version_number]
FROM [msdbnew].[dbo].[sysschedules] a
inner join [msdbnew].[dbo].[sysjobschedules] b
on a.schedule_id=b.schedule_id
inner join [msdb].[dbo].[sysjobs] c
on b.job_id=c.job_id
left join [msdb].[dbo].[sysschedules] d
on a.schedule_uid=d.schedule_uid
where d.schedule_uid is null
go
--关联job_id和schedule_id
insert into [msdb].[dbo].[sysjobschedules]
SELECT c.schedule_id
,a.[job_id]
,a.[next_run_date]
,a.[next_run_time]
FROM [msdbnew].[dbo].[sysjobschedules] a
inner join [msdbnew].[dbo].[sysschedules] b
on a.schedule_id=b.schedule_id
inner join [msdb].[dbo].[sysschedules] c
on b.schedule_uid=c.schedule_uid
left join [msdb].[dbo].[sysjobschedules] d
on a.job_id=d.job_id
where d.job_id is null
go
--指定目标服务器
insert into [msdb].[dbo].[sysjobservers]
SELECT a.[job_id]
,a.[server_id]
,a.[last_run_outcome]
,a.[last_outcome_message]
,a.[last_run_date]
,a.[last_run_time]
,a.[last_run_duration]
FROM [msdbnew].[dbo].[sysjobservers] a
inner join [msdb].[dbo].[sysjobs] b
on a.job_id=b.job_id
left join [msdb].[dbo].[sysjobservers] c
on a.job_id=c.job_id
where c.job_id is null
go
/********如果对作业的所有者有特殊要求,参考下面代码进行修改********/
--Start
--原实例下获取作业所有者
select sj.name,sj.date_created,sj.date_modified,ssp.name OwnName,sj.owner_sid
from msdb.dbo.sysjobs sj
inner join sys.server_principals ssp
on sj.owner_sid=ssp.sid
where ssp.name='USER_JOB'
order by sj.name
--新实例下更新需要修改的所有者
update msdb.dbo.sysjobs
set owner_sid=suser_sid('user_job')
where job_id in(
select job_id from msdbnew.dbo.sysjobs
where owner_sid=convert(varbinary,0x047EC52E6005D241932C0DC5F0827615))--原作业所有者
update [msdb].[dbo].[sysschedules]
set owner_sid=suser_sid('user_job')
where schedule_id in(
select schedule_id from [msdbnew].[dbo].[sysschedules]
where owner_sid=convert(varbinary,0x047EC52E6005D241932C0DC5F0827615))--原作业所有者
--End
--上面操作完成后,作业并不会按计划执行作业。可用下面方式中的任意一种使作业按计划运行
--1重启代理服务;2禁用作业再启用作业;3修改并保存作业
--应该可以直接执行某个语句(暂时没找到)
View Code
如果创建数据库登录名时指定了sid,则以此登录名为所有者的作业对应的[sysjobs]、[sysschedules]表中的owner_sid可以直接从原表获取。