迁移场景	文档链接
使用界面方式迁移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账号。

-- 基本语句
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)+
CASE
WHEN 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,' 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')
;

-- 获取密码hash值
SELECT loginname,CONVERT(VARBINARY(MAX), password),LOGINPROPERTY(loginname,'PASSWORDHASH')
  FROM syslogins 
  WHERE name = 'LHR'
;

-- 基本语句

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)+

CASE

WHEN 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,' 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')

;

-- 获取密码hash值

SELECT loginname,CONVERT(VARBINARY(MAX), password),LOGINPROPERTY(loginname,'PASSWORDHASH')

FROM syslogins

WHERE name = 'LHR'

;

将新建的Login账号跟用户当前RDS SQL Server实例上的迁移过来的数据库用户权限进行映射（mapping），以保证该账号在当前实例上的权限一致性，执行脚本如下。

declare @DBName nvarchar(200)
declare @Login_name nvarchar(200)
declare @SQL nvarchar(MAX)
set @Login_name = 'TestLogin7' //输入Login名称逐个执行
declare DBName_Cursor cursor for
select quotename(name)from sys.databases where  database_id > 4 and state = 0
and name not like '%$%'
and name <> 'rdsadmin'
open DBName_Cursor
fetch next from DBName_Cursor into @DBName
WHILE @@FETCH_STATUS= 0
begin
SET @SQL='    USE '+ (@DBName)+ '
if exists(select top 1 1 from sys.sysusers where name = '''+ @Login_Name +''')
begin
ALTER USER '+@Login_name+' with login = '+@Login_name+';
end
'
print @SQL
EXEC (@SQL)
fetch next from DBName_Cursor into @DBName
end
close DBName_Cursor
deallocate DBName_Cursor

declare @DBName nvarchar(200)

declare @Login_name nvarchar(200)

declare @SQL nvarchar(MAX)

set @Login_name = 'TestLogin7' //输入Login名称逐个执行

declare DBName_Cursor cursor for

select quotename(name)from sys.databases where database_id > 4 and state = 0

and name not like '%$%'

and name <> 'rdsadmin'

open DBName_Cursor

fetch next from DBName_Cursor into @DBName

WHILE @@FETCH_STATUS= 0

begin

SET @SQL=' USE '+ (@DBName)+ '

if exists(select top 1 1 from sys.sysusers where name = '''+ @Login_Name +''')

begin

ALTER USER '+@Login_name+' with login = '+@Login_name+';

end

print @SQL

EXEC (@SQL)

fetch next from DBName_Cursor into @DBName

end

close DBName_Cursor

deallocate DBName_Cursor

说明：

以上脚本执行完成后，用户即可在自己的新实例上看到同名的登录账号，并且密码跟权限是完全跟本地一致的。

SQL Server 2008

-- 基本语句
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';

-- 基本语句

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版本，则可以使用如下脚本，如下：

   -- 基本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
   ;

-- 基本SQL

-- sp_addlogin '登录名','密码';

sp_addlogin 'USER','PWD','DATABASE';

sp_adduser '登录名','用户名','db_owner'（组名）;

sp_addsrvrolemember '用户名','sysadmin' （角色名）;

-- 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

;

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！

删除登陆


-- 删除
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

;

-- 删除

sp_dropuser 'ac';

sp_droplogin 'ac';

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上的密码即可执行创建操作。

USE [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

USE [master]

/****** 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='########'

说明：

以上脚本为范例，创建的脚本可能包含大量系统默认配置项，但是每个DBLink仅需保留以下两个关键脚本即可执行成功，同时需要注意重新输入账号连接密码。

SQL Server 2000可能会报错“有出现过:"消息 15429，级别 16，状态 1，过程 sp_addlinkedserver，第 42 行'(null)' 是无效的产品名称。”

解决：添加参数名为srvproduct，值的话随便填一个好记的就可以了。

,@srvproduct=N''

1	,@srvproduct=N''

Agent JOB

Agent JOB又名SQL Server代理服务，可以方便用户快速的在实例上创建定时任务，帮助用户进行日常运维和数据处理工作，用户在本地的JOB需要手动进行脚本迁移。

通过微软提供的官方Microsoft SQL Server Management Studio客户端工具连接本地实例与云上实例，同时在“SQL Server代理 > 作业”下找到当前实例上的所有JOB任务。
图4 查看作业
选择SQL Server代理下的作业，然后按F7，可以在对象资源管理器中看到所有的作业（JOB），全部选中后创建脚本到新窗口。
图5 创建脚本
复制新窗口中的T-SQL创建脚本到新实例上，然后注意修改如下几个关键项，以保障你的创建成功。
- 注意修改每个JOB上的Ower账号：
  例如：
  @owner_login_name=N'rdsuser'
  1
  @owner_login_name=N'rdsuser'
- 注意修改每个JOB上的实例名称：
  例如：
  @server=N'实例IP' @server_name = N'实例IP'
  1
  2
  @server=N'实例IP'
  @server_name = N'实例IP'
说明：
新建JOB的Owner账号十分重要，在RDS SQL Server上，仅有该JOB的Owner可以看到实例上自己的JOB，别的Login账号是看不到无法操作的，所以建议所有的JOB Owner尽量是同一个账号方便管理。
如果报错：“不能将值 NULL 插入列 'owner_sid'，表 'msdb.dbo.sysjobs'；列不允许有 Null 值。INSERT 失败。”
解决：将“@owner_login_name=N'SUNDAN1\administrator'”修改为“@owner_login_name=N'sa'”

关键配置

用户将数据库还原到RDS for SQL Server实例上之后，本地的一些重要配置项也需要进行同步确认，避免影响业务的正常使用。

tempdb：临时数据库的文件配置需要进行同步。

推荐配置为8个临时文件，注意路径一定要确保在D:\RDSDBDATA\Temp\

通过在目标数据库端执行如下脚本添加临时数据库的文件配置：

USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb1', FILENAME = N'D:\RDSDBDATA\Temp\tempdb1.ndf' , SIZE = 65536KB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb2', FILENAME = N'D:\RDSDBDATA\Temp\tempdb2.ndf' , SIZE = 65536KB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb3', FILENAME = N'D:\RDSDBDATA\Temp\tempdb3.ndf' , SIZE = 65536KB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb4', FILENAME = N'D:\RDSDBDATA\Temp\tempdb4.ndf' , SIZE = 65536KB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb5', FILENAME = N'D:\RDSDBDATA\Temp\tempdb5.ndf' , SIZE = 65536KB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb6', FILENAME = N'D:\RDSDBDATA\Temp\tempdb6.ndf' , SIZE = 65536KB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb7', FILENAME = N'D:\RDSDBDATA\Temp\tempdb7.ndf' , SIZE = 65536KB , FILEGROWTH = 65536KB )
GO

USE [master]

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb1', FILENAME = N'D:\RDSDBDATA\Temp\tempdb1.ndf' , SIZE = 65536KB , FILEGROWTH = 65536KB )

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb2', FILENAME = N'D:\RDSDBDATA\Temp\tempdb2.ndf' , SIZE = 65536KB , FILEGROWTH = 65536KB )

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb3', FILENAME = N'D:\RDSDBDATA\Temp\tempdb3.ndf' , SIZE = 65536KB , FILEGROWTH = 65536KB )

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb4', FILENAME = N'D:\RDSDBDATA\Temp\tempdb4.ndf' , SIZE = 65536KB , FILEGROWTH = 65536KB )

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb5', FILENAME = N'D:\RDSDBDATA\Temp\tempdb5.ndf' , SIZE = 65536KB , FILEGROWTH = 65536KB )

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb6', FILENAME = N'D:\RDSDBDATA\Temp\tempdb6.ndf' , SIZE = 65536KB , FILEGROWTH = 65536KB )

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb7', FILENAME = N'D:\RDSDBDATA\Temp\tempdb7.ndf' , SIZE = 65536KB , FILEGROWTH = 65536KB )

图6 检查临时文件

数据库隔离级别：请确认原实例上数据库的隔离级别是否开启，并同步到RDS SQL Server实例，快照隔离参数有2个，分别是：
- 读提交快照（Is Read Committed Snapshot On）
- 允许快照隔离（Allow Snapshot Isolation）
若原实例上数据库的隔离级别是开启的，您可以通过在目标数据库端执行如下脚本开启数据库的隔离级别：
USE [DBName] GO ALTER DATABASE [DBName] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT GO ALTER DATABASE [DBName] SET ALLOW_SNAPSHOT_ISOLATION ON GO
1
2
3
4
5
6
USE [DBName]
GO
ALTER DATABASE [DBName] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
GO
ALTER DATABASE [DBName] SET ALLOW_SNAPSHOT_ISOLATION ON
GO
实例最大并行度：实例最大并行度在RDS for SQL Server实例上默认设置为0，用户也可以根据自己本地原来的设置项进行同步设置，避免不同环境下业务场景出现异常。
右击本地实例选择属性，在服务器属性弹出框中选择高级，然后在右侧找到最大并行度（max degree of parallelism）设置项，确认本地实例设置值，并同步在目标RDS for SQL Server实例管理的参数组中进行修改。
图7 查看本地实例最大并行度值
登录本云实例控制台，在实例管理页，单击目标实例名称，进入基本信息页签，切换至“参数修改”，搜索最大并行度（max degree of parallelism）并进行修改。
图8 修改目标RDS for SQL Server实例的最大并行度
迁移上云的数据库恢复模式是否为完整（FULL）模式，如果不是需要进行修改。
右击数据库选择属性，在弹出数据库属性框中选择选项，并在右侧确认该数据库恢复模式为完整（FULL），保证该数据库高可用和备份策略可执行。
图9 检查数据库恢复模式

打赏赞(4)

标签： AgentJOB迁移 DBLink Login账号 MSSQL MSSQL2000 SQL Server 代理迁移元数据迁移用户迁移

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

2023年 5月
一	二	三	四	五	六	日
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

AI (2)
ClickHouse (5)
DM达梦 (9)
Docker (34)
GBase (34)
Greenplum (85)
HANA (2)
K8S (14)
Kafka (3)
Memcached (2)
MongoDB (3)
MySQL (177)
NoSQL (3)
OceanBase (14)
OGG (43)
openGauss (9)
Oracle (590)
OS (329)
PostgreSQL (228)
Python (26)
Redis (18)
SQL Server (106)
SQLite (1)
StarRocks (9)
TDSQL (5)
TiDB (18)
YMatrix (5)
中间件 (8)
云 (10)
人大金仓KingBase (7)
其它 (78)
培训考证 (43)
大数据 (4)
存储 (4)
巡检监控 (43)
工具 (65)
数据迁移 (16)
架构篇 (9)
生活 (8)
电脑 (14)
网络 (77)
育儿 (5)
视频 (17)
面试笔试 (38)

嘿，我是小麦，需要帮助随时找我哦

18509239930
个人微信
麦老师QQ聊天
个人邮箱
点击加入QQ群
个人微店
回到顶部

小麦苗

MSSQL手动迁移Login账号、DBLink、AgentJOB等信息

MSSQL迁移方案概览

操作场景

Login账号及其密码迁移

SQL Server 2012

SQL Server 2008

SQL Server 2000以下

删除登陆

DBLink连接

Agent JOB

关键配置

相关文章

您可能还喜欢...

发表回复取消回复

博客寄语

博客概况

本站其它工具

搜标题

搜索本网站

标签云☁

登陆本博客

博客日历

博客归档

博客分类

MSSQL手动迁移Login账号、DBLink、AgentJOB等信息

MSSQL迁移方案概览

操作场景

Login账号及其密码迁移

SQL Server 2012

SQL Server 2008

SQL Server 2000以下

删除登陆

DBLink连接

Agent JOB

关键配置

相关文章

您可能还喜欢...

无法完成域加入，原因是试图加入的域的SID与本计算机的SID相同

MSSQL删除数据库脚本

在Linux中安装MSSQL 2017 Always On Availability Group

发表回复 取消回复

博客寄语

博客概况

本站其它工具

搜标题

搜索本网站

标签云☁

登陆本博客

博客日历

博客归档

博客分类

发表回复取消回复