背景：数据库服务器更换，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
2、数据库登录名迁移，新实例中创建同名数据库登录名，待数据库还原后，使用以下语句处理孤立用户
--孤立用户 USE DBA_Monitor GO exec sp_change_users_login 'REPORT' exec sp_change_users_login 'UPDATE_ONE','用户名','登录名'--更新库下用户的sid成登录名的sid GO
创建数据库登录名时带上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%'
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修改并保存作业 --应该可以直接执行某个语句(暂时没找到)
如果创建数据库登录名时指定了sid，则以此登录名为所有者的作业对应的[sysjobs]、[sysschedules]表中的owner_sid可以直接从原表获取。