合 MSSQL手动迁移Login账号、DBLink、AgentJOB等信息
Tags： MSSQLSQL ServerDBLinkMSSQL2000用户迁移代理迁移元数据迁移Login账号AgentJOB迁移
👉 本文共约3290个字，系统预计阅读时间或需13分钟。
目录
MSSQL迁移方案概览
云数据库SQL Server提供了多种数据迁移方案，可满足不同业务需求，使您可以在不影响业务的情况下平滑将数据库迁移至云数据库SQL Server。
| 迁移场景 | 文档链接 |
|---|---|
| 使用界面方式迁移SQL Server库表数据 | 通过数据管理服务导出SQL Server库表数据 |
| 云数据库备份文件迁移到云数据库SQL Server | 云数据库SQL Server全量备份迁移到云数据库SQL Server |
| 本地备份文件迁移到云数据库SQL Server | 本地SQL Server备份文件迁移到云数据库SQL Server |
操作场景
1、从本地或虚拟机通过DRS备份迁移功能直接迁移到本云RDS for SQL Server实例上，在迁移完成后还需要针对Login账号，DBLink，AgentJOB，关键配置进行识别，并手动完成相关同步工作。
2、通过备份恢复迁移SQL Server数据库到异机环境。
Login账号及其密码迁移
Login账号即SQL Server的实例级账号，主要用于用户管理用户服务器权限与数据库权限。一个用户通常会有多个该类型账号，用户迁移到RDS for SQL Server实例后，需要手动将自己本地的Login账号同步在实例上进行创建，以下方法将介绍如何在本云RDS for SQL Server实例上创建同名，同密码的Login账号，并进行授权操作。
SQL Server 2012
通过以下脚本获取本地实例Login账号创建脚本，获取到的脚本可以直接在目标端上执行，以创建同名，同密码的Login账号。
123456789101112131415161718192021222324252627282930313233343536373839404142-- 基本语句create login 登录名 with password = '密码',CHECK_EXPIRATION = OFF, CHECK_POLICY = ON, DEFAULT_DATABASE=[master];create user 用户名 for login 登录名 with default_schema=dbo;exec sp_addsrvrolemember @loginame = N'登录名', @rolename = N'sysadmin'（角色）;-- 迁移login和用户SELECT 'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+CASEWHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' + CONVERT(NVARCHAR(MAX),SL.password_hash,1)+ ' HASHED,SID=' +CONVERT(NVARCHAR(MAX),SP.SID,1)+',CHECK_EXPIRATION = '+ CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END +', CHECK_POLICY = ' +CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' ENDELSE ' FROM WINDOWS WITH'END+' DEFAULT_DATABASE=[' +SP.default_database_name+ '], DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] END;' as CreateLoginFROM sys.server_principals AS SP LEFT JOIN sys.sql_logins AS SLON SP.principal_id = SL.principal_idWHERE SP.type ='S'AND SP.name NOT LIKE '##%##'AND SP.name NOT LIKE 'NT AUTHORITY%'AND SP.name NOT LIKE 'NT SERVICE%'AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public')UNION ALLSELECT 'create user '+SL.name+' for login '+SL.name+' with default_schema=dbo;' as CreateuserFROM sys.sql_logins AS SLWHERE SL.name NOT LIKE '##%##'AND SL.name NOT LIKE 'NT AUTHORITY%'AND SL.name NOT LIKE 'NT SERVICE%'AND SL.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public')UNION ALLSELECT 'exec sp_addsrvrolemember @loginame = N'''+SL.name+''', @rolename = N''sysadmin'';' as rolememberFROM sys.sql_logins AS SLWHERE SL.name NOT LIKE '##%##'AND SL.name NOT LIKE 'NT AUTHORITY%'AND SL.name NOT LIKE 'NT SERVICE%'AND SL.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public');-- 获取密码hash值SELECT loginname,CONVERT(VARBINARY(MAX), password),LOGINPROPERTY(loginname,'PASSWORDHASH')FROM sysloginsWHERE name = 'LHR';将新建的Login账号跟用户当前RDS SQL Server实例上的迁移过来的数据库用户权限进行映射（mapping），以保证该账号在当前实例上的权限一致性，执行脚本如下。
123456789101112131415161718192021222324declare @DBName nvarchar(200)declare @Login_name nvarchar(200)declare @SQL nvarchar(MAX)set @Login_name = 'TestLogin7' //输入Login名称逐个执行declare DBName_Cursor cursor forselect quotename(name)from sys.databases where database_id > 4 and state = 0and name not like '%$%'and name <> 'rdsadmin'open DBName_Cursorfetch next from DBName_Cursor into @DBNameWHILE @@FETCH_STATUS= 0beginSET @SQL=' USE '+ (@DBName)+ 'if exists(select top 1 1 from sys.sysusers where name = '''+ @Login_Name +''')beginALTER USER '+@Login_name+' with login = '+@Login_name+';end'print @SQLEXEC (@SQL)fetch next from DBName_Cursor into @DBNameendclose DBName_Cursordeallocate DBName_Cursor说明：
以上脚本执行完成后，用户即可在自己的新实例上看到同名的登录账号，并且密码跟权限是完全跟本地一致的。
SQL Server 2008
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | -- 基本语句 create login 登录名 with password = '密码',CHECK_EXPIRATION = OFF, CHECK_POLICY = ON, DEFAULT_DATABASE=[master]; create user 用户名 for login 登录名 with default_schema=dbo; exec sp_addsrvrolemember @loginame = N'登录名', @rolename = N'sysadmin'（角色）; -- 获取密码hash值 SELECT loginname,CONVERT(VARBINARY(32), password),LOGINPROPERTY(loginname,'PASSWORDHASH') FROM syslogins WHERE name = 'LHR' ; alter login lhr6 with password = 0x0100301E99CB10A532DB23EC18967DDE9527E0BBD33808DD1A29 HASHED,CHECK_POLICY = OFF; -- 迁移login和用户 SELECT 'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+ CASE WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ''' + SP.name + ''' ,SID=' +CONVERT(NVARCHAR(MAX),SP.SID,1)+',CHECK_EXPIRATION = ' + CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END +', CHECK_POLICY = ' +CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END ELSE ' FROM WINDOWS WITH' END +' DEFAULT_DATABASE=[' +SP.default_database_name+ '], DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] END;' as CreateLogin FROM sys.server_principals AS SP LEFT JOIN sys.sql_logins AS SL ON SP.principal_id = SL.principal_id WHERE SP.type ='S' AND SP.name NOT LIKE '##%##' AND SP.name NOT LIKE 'NT AUTHORITY%' AND SP.name NOT LIKE 'NT SERVICE%' AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public') UNION ALL SELECT 'create user '+SL.name+' for login '+SL.name+' with default_schema=dbo;' as Createuser FROM sys.sql_logins AS SL WHERE SL.name NOT LIKE '##%##' AND SL.name NOT LIKE 'NT AUTHORITY%' AND SL.name NOT LIKE 'NT SERVICE%' AND SL.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public') UNION ALL SELECT 'exec sp_addsrvrolemember @loginame = N'''+SL.name+''', @rolename = N''sysadmin'';' as rolemember FROM sys.sql_logins AS SL WHERE SL.name NOT LIKE '##%##' AND SL.name NOT LIKE 'NT AUTHORITY%' AND SL.name NOT LIKE 'NT SERVICE%' AND SL.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public') ; -- 更新密码 SELECT 'alter login '+sl.name+' with password = ',CONVERT(VARBINARY(32), password),' HASHED,CHECK_POLICY = OFF;' as rolemember FROM syslogins AS SL WHERE SL.name NOT LIKE '##%##' AND SL.name NOT LIKE 'NT AUTHORITY%' AND SL.name NOT LIKE 'NT SERVICE%' AND SL.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public'); -- 数据库映射 USE [lhrdb] sp_change_users_login 'update_one', 'lhr', 'lhr'; |
SQL Server 2000以下
若是SQL Server 2000版本，则可以使用如下脚本，如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 | -- 基本SQL -- sp_addlogin '登录名','密码'; sp_addlogin 'USER','PWD','DATABASE'; go sp_adduser '登录名','用户名','db_owner'（组名）; go sp_addsrvrolemember '用户名','sysadmin' （角色名）; go -- LOGIN，记得加上G SELECT 1 AS ID,loginname,'sp_addlogin '''+ sp.loginname + ''',', CONVERT(VARBINARY(32), password),','''+sp.dbname+''';' FROM syslogins AS SP WHERE SP.name NOT LIKE '##%##' AND SP.name NOT LIKE 'NT AUTHORITY%' AND SP.name NOT LIKE 'NT SERVICE%' AND SP.name NOT LIKE '%Administrators%' AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public','sa') ; -- 用户 SELECT 2 AS ID,NAME,'sp_adduser '''+ sp.NAME + ''','''+ sp.NAME+''',''db_owner'';' FROM syslogins AS SP WHERE SP.name NOT LIKE '##%##' AND SP.name NOT LIKE 'NT AUTHORITY%' AND SP.name NOT LIKE 'NT SERVICE%' AND SP.name NOT LIKE '%Administrators%' AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public','sa') union all SELECT 3 AS ID,NAME,'GO' FROM syslogins AS SP WHERE SP.name NOT LIKE '##%##' AND SP.name NOT LIKE 'NT AUTHORITY%' AND SP.name NOT LIKE 'NT SERVICE%' AND SP.name NOT LIKE '%Administrators%' AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public','sa') union all SELECT 4 AS ID,NAME,'sp_addsrvrolemember '''+ sp.NAME + ''',''sysadmin'';' FROM syslogins AS SP WHERE SP.name NOT LIKE '##%##' AND SP.name NOT LIKE 'NT AUTHORITY%' AND SP.name NOT LIKE 'NT SERVICE%' AND SP.name NOT LIKE '%Administrators%' AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public','sa') union all SELECT 5 AS ID,NAME,'GO' FROM syslogins AS SP WHERE SP.name NOT LIKE '##%##' AND SP.name NOT LIKE 'NT AUTHORITY%' AND SP.name NOT LIKE 'NT SERVICE%' AND SP.name NOT LIKE '%Administrators%' AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public','sa') ORDER BY NAME,ID ; -- 数据库映射 USE [lhrdb] sp_change_users_login 'update_one', 'lhr', 'lhr'; select 0 id,name,'use '+name+';' from master..sysdatabases where name not in ('master','tempdb','model','msdb') union all select 1 id,name,'GO ' from master..sysdatabases where name not in ('master','tempdb','model','msdb') union all select 2 id,name,'sp_change_users_login ''update_one'', ''user_data'', ''user_data''; ' from master..sysdatabases where name not in ('master','tempdb','model','msdb') union all select 3 id,name,'GO ' from master..sysdatabases where name not in ('master','tempdb','model','msdb') ORDER BY NAME,ID ; |
删除登陆
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | -- 删除 sp_dropuser 'ac'; GO sp_droplogin 'ac'; GO SELECT 1 AS ID,loginname,'sp_dropuser ' + sp.loginname +';' FROM syslogins AS SP WHERE SP.name NOT LIKE '##%##' AND SP.name NOT LIKE 'NT AUTHORITY%' AND SP.name NOT LIKE 'NT SERVICE%' AND SP.name NOT LIKE '%Administrators%' AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public','sa') union all SELECT 2 AS ID,loginname,'GO' FROM syslogins AS SP WHERE SP.name NOT LIKE '##%##' AND SP.name NOT LIKE 'NT AUTHORITY%' AND SP.name NOT LIKE 'NT SERVICE%' AND SP.name NOT LIKE '%Administrators%' AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public','sa') union all SELECT 3 AS ID,loginname,'sp_droplogin ' + sp.loginname +';' FROM syslogins AS SP WHERE SP.name NOT LIKE '##%##' AND SP.name NOT LIKE 'NT AUTHORITY%' AND SP.name NOT LIKE 'NT SERVICE%' AND SP.name NOT LIKE '%Administrators%' AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public','sa') union all SELECT 4 AS ID,loginname,'GO' FROM syslogins AS SP WHERE SP.name NOT LIKE '##%##' AND SP.name NOT LIKE 'NT AUTHORITY%' AND SP.name NOT LIKE 'NT SERVICE%' AND SP.name NOT LIKE '%Administrators%' AND SP.name NOT IN ('rdsadmin','rdsbackup','rdsuser','rdsmirror','public','sa') ORDER BY loginname,ID ; |
DBLink连接
DBLink连接指SQL Server支持用户通过创建DBLink连接的方式，跟外部实例上的数据库进行交互，这种方式可以极大的方便用户不同实例间，不同数据库类型之间的数据库查询，同步，比较，所以大部分用户都会在本地实例上用到该服务，但是迁移上云后，本地DBLink是不会自动同步到云上实例的，还需要简单的手动进行同步。
通过微软提供的官方Microsoft SQL Server Management Studio客户端工具连接本地实例与云上实例，同时在“服务器对象 > 链接服务器”下找到当前实例的DBLink链接。
图2 查看DBLink链接
选中链接服务器，然后按F7，会自动弹出对象资源管理信息页，在该页面中可以方便你快速的自动创建脚本。
图3 自动创建脚本
在新窗口中，可以看到当前实例上所有DBLink的创建脚本，仅需复制该脚本到目标实例上，并修改@rmtpassword上的密码即可执行创建操作。
12345678USE [master]GO/****** Object: LinkedServer [DRS_TEST_REMOTE] Script Date: 2019/5/25 17:51:50 ******/EXEC master.dbo.sp_addlinkedserver @server = N'DRS_TEST_REMOTE', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'DESKTOP-B18JH5T\SQLSERVER2016EE'/* For security reasons the linked server remote logins password is changed with ######## */EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DRS_TEST_REMOTE',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='########'GO说明：
以上脚本为范例，创建的脚本可能包含大量系统默认配置项，但是每个DBLink仅需保留以下两个关键脚本即可执行成功，同时需要注意重新输入账号连接密码。
SQL Server 2000可能会报错“有出现过:"消息 15429，级别 16，状态 1，过程 sp_addlinkedserver，第 42 行'(null)' 是无效的产品名称。”
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
