是否建议SQL Server就地升级不如从前？

#1 楼

答案很简短-就位。之后，您可以查看配置并实施SQL Server 2012的最佳实践。

有关SQL Server升级/迁移的更长的答案

所以这是一种观点，没有不一定是错误或正确的答案，但出于多种原因，我更喜欢迁移样式升级而不是就地升级。话虽这么说-我的一些客户出于各种原因别无选择，只能进行就地升级。实际上，自SQL Server 2005以来，就地升级并没有以前那么糟糕。

为什么我更喜欢迁移到就地升级



更容易回滚-如果出现问题，您可以通过简单地说“中止升级。。请在解决此问题的同时将连接字符串更改为旧服务器。”使用就位时，您正在修理它或将其放下。

刷新硬件-硬件变化迅速。通过就地升级，您很容易陷入四年前适合您公司的硬件，而不适用于今天和未来四年的硬件。无论如何，您可能都必须在某个时候进行迁移。

感觉更好-当然...这是主观的，但是知道您正在安装新的OS时，感觉很好。 ，一个新的SQL安装程序，在您之前（或您之前不知道今天所知道的情况）工作人员没有蛛网的情况下，将来可能会导致您头疼。

新OS-迁移如果您还没有最新的最新版本，则有机会开始使用新的操作系统版本。

您可以进行测试-曾经想在新计算机上获得一组基准安装SQL并将其与数据库和用法一起云化？您现在就可以做到。

有时更容易掌握最佳实践-也许SQL Server服务帐户是本地管理员。也许Builtin Administrators是SA服务器角色。也许事情以前已经被黑客入侵才能使其正常工作。您可以修复所有问题并重新开始。

免费的测试环境和额外的睡眠-在新环境启用之前，拥有一个可以在实际转换日之前工作的环境是一个很大的好处。 。迁移到新环境意味着您可以在工作时间（比实际转换日提前）构建它，并提前进行多种测试。您可以在所有应用程序和系统上运行几天的完整回归测试，并在真正进行最后一组还原/附加和转换所有应用程序并访问新环境之前放心。

您不必一次完成所有操作-我碰到的一种非常常见的情况是，一个环境试图将其整合为几个实例。也许每个版本一个，也许每个“层”和版本一个。根据测试，项目计划和供应商认证的及时性，许多此类项目对于各种应用程序和数据库都有不同的时间表。进行迁移意味着您可以移动就绪的那些数据库，当它们就绪时仍可以处理由于某种原因而无法移动的那些数据库的请求。

请记住，我不是说您必须将此作为迁移。就地可以正常工作，并且如果您不打算在预算内购买新硬件，并且无法进行此升级，那么它就可以很好地工作。升级过程中的支持比6.5天要好得多，因此您这样做不会让自己陷入困境。

如果您确实计划就地进行开发/测试，但想要进行生产迁移，则可以考虑在生产前至少进行一次迁移。这样，您可以提前制定清单，并处理所有您没有想到的潜在问题。

附加/分离与迁移备份/还原

如果您决定采用迁移方法，那么还有另一个决定可能仍在争论中，那就是如何将数据库移至新环境。您可以将数据库与旧服务器分离，然后将其附加到新服务器上，也可以备份并还原到那里。

我更喜欢备份/还原。我听到的关于分离/附加的最大优点是节省了一些时间。对我来说，备份/还原获胜有以下几个原因：




保持旧的可访问性-这使您仍可以在源服务器上拥有可访问的数据库。分离/附加应该执行相同的操作，但是这将需要一些步骤，并且分离/附加可能存在人为错误，这可能会使此问题变得复杂。

您保证拥有备份-而不是只是从分离中取出数据库并可能忘记备份步骤，就可以确保已进行了备份。

人为错误-如果您删除了错误的文件，请忘记将其发送到哪里某些事情或其他事情弄乱了您的步骤，您将为数据库移动数据和日志文件会冒很大的风险。现在，您可以通过复制而不是剪切来减轻这种情况（如果分离，则应该摆脱剪切和粘贴的习惯），但是您可能会变得一团糟。 SQL Server不再锁定这些文件，而意外删除文件太容易让我冒险。

其实并没有那么慢-进行备份和复制会花费更多的时间，但是我愿意为此付出额外的风险并没有那么多。实际上-使用完整的恢复模型和日志备份，您可以按照下面的“如何使迁移方法更快”中的描述减少停机时间，以便进行转换

如果您决定进行备份/ restore-这意味着您的旧源数据库将仍然在线。我喜欢在备份后使该数据库脱机。在编写安全性，作业，链接服务器，证书，数据库邮件设置和其他实例范围的信息的脚本后，有时我会更进一步，使整个SQL实例脱机。这样可以避免测试过程中有人说“一切看起来都不错！”的问题。一两天后才意识到他们一直在与旧服务器上的旧数据库通信。使这些数据库脱机或使整个实例脱机，可以防止那些误报及其造成的混乱。

如何更快地进行迁移方法

您可以最大程度地减少停机时间通过使用完整的恢复模型，从旧环境切换到新环境所需的资源，从而在繁忙的生产环境中停机时间很少。基本上-通过还原最新的完整备份，任何差异备份以及已指定NORECOVERY的任何已采取日志备份来准备迁移的环境-然后，要进行最终转换，您要做的就是恢复尚未还原的日志备份以及要恢复的最终日志备份，请指定WITH RECOVERY。通过这种方式，对于大型数据库，可以通过在停机时间窗口之前支付全部，差异和大多数日志还原的费用，从而大大减少实际的转换停机时间窗口。感谢Tao在评论中指出这一点！

如何使就地升级更安全

选择就地方法时，您可以做一些事情来改善您的体验和结果。



备份-对环境中的所有用户和系统数据库进行适当的备份提前确保自己的身体好（我很偏执。我实际上会先恢复他们的位置，以真正知道他们的身体好。可能会浪费您的时间。但是如果发生灾难，您可能要感谢自己）。在该环境中编写有关SQL和OS安装的所有配置信息。

在开始之前先进行良好的测试-验证您是否拥有良好的环境和良好的数据库。您应该执行一些操作，例如查看错误日志并定期运行DBCC CHECKDB，但是在进行就地升级之前，是个不错的开始。提前修复所有问题。

确保操作系统健康-不仅要确保SQL运行状况良好，还要确保服务器运行状况良好。您的系统或应用程序错误事件日志中是否存在任何严重错误？您的可用空间如何？

为最坏的情况做准备-不久前我有一篇博客文章，其前提是，如果您没有为失败做准备-您实际上是在准备失败。我仍然相信。因此，请仔细考虑您可能遇到的问题，并提前进行相应处理。让自己陷入“失败”的心态，您会想到其他本来没有的事情。

升级或迁移清单的重要性

如果您决定进行升级（无论是进行升级还是迁移），您应该认真考虑创建清单并在每个环境中使用该清单。您应该在此清单中添加很多内容，其中至少要包括以下内容：



在开始时-做一些事情，例如执行测试升级，在最新的数据库兼容性级别上测试应用程序，并考虑提前运行SQL Server升级顾问之类的工具，以查看执行SQL之前需要完成哪些任务服务器升级或迁移。

前置步骤-清理，操作系统任务，提前打补丁，为升级准备应用程序（清理关机，连接字符串工作），备份等。

升级/迁移步骤-为使升级或迁移成功并按正确的顺序进行，您必须进行的所有操作。安装，更改（或不更改，取决于您的测试和方法）对数据库的兼容模式更改等。

迁移/升级步骤-各种测试，发布新版本或新服务器配置选项，最佳实践实施，安全性更改等。

回滚步骤-在整个过程中，您应该具有回滚步骤和里程碑。如果您能做到这一点并且发生了这种情况，您将怎么办？什么是“完全回滚”标准？以及如何进行回滚（更改反向连接字符串，更改回设置，返回到旧版本，在适当位置重新安装，在迁移时指向旧服务器等）。

然后让将要进行生产升级的人员在生产以外的某些环境中遵循清单，尤其是在可能的情况下关闭类似于生产的环境（如我所说的“生产南部” ...）并注意任何问题或指出由于缺少清单而不得不从清单中转移或即兴创作的地方。然后将更改合并到一起，并享受生产更改带来的乐趣。

我不能过分强调在迁移或升级后以及在迁移之前进行充分测试的重要性。在升级过程中做出回滚决定应该很容易-尤其是在迁移过程中。如果有什么不舒服的地方，请回滚并找出原因，以免在迁移过程中无法有效而可靠地进行故障排除。一旦您处于这个新环境中并与用户建立联系-回滚就成为一项艰巨的任务。您不能将SQL Server数据库还原到早期版本。这意味着手动工作和数据迁移。我总是要等几个星期才能消除旧环境，但是您应该尽一切努力，在您的实时用户接触新环境之前，通过查找所有问题来避免使用旧环境。最好在开始升级/迁移之前。

有关SQL Server Reporting Services迁移/升级的快速说明
迁移SSRS安装并不是很多人认为的艰巨任务。这篇technet / books在线文章实际上很方便。该文章中最重要的建议之一是“备份加密密钥”，尤其是当您保存了许多敏感信息（如计划的报告电子邮件收件人的电子邮件地址，多个连接的连接信息等）时。可以不时问我的一位客户，这有多重要。他们知道，因为我搞砸了这一步，并花了很多时间修改报告计划和连接字符串权限。

#2 楼

以我的经验，应该像以前一样做出相同的决策过程。 AFAIK本身在MS SQL Server产品中还没有安装SQL Server的“世界改变者”，以及在推出具有数百万行代码的软件时可能遇到的潜在问题。可能会发生一些不良情况，现在您没有使用“ ROLLBACK”选项。

但是，您确实有其他选择。您可以考虑制作系统快照，在其他地方还原，执行升级并查看会发生什么。此测试应该给您带来很大的舒适感，但不能绝对保证在产品包装盒上不会出现任何问题。但是，此选项在SQL 6.5天内不可用。

我只是假设最坏的情况。您进行了就地升级，但失败很惨。然后，您必须在RTO和RCO中从中恢复。企业是否了解风险，您是否制定了减轻风险的计划？

如果生意做不好，那就不要做，这是我的建议。

#3 楼

如果您的服务器在虚拟环境中运行，则可以在克隆上执行快照，然后应用就地升级并测试实例以验证升级是否成功。如果可行，您可以应用快照并使克隆成为生产服务器。如果处理不佳，则可以删除快照，然后返回升级前的映像以重试，或者删除克隆并进行完整迁移。

#4 楼

由于大量的硬件投资，我们被要求仅升级操作系统，同时保留当前的SQL Server版本（2012、3个服务器，22个实例，约300个数据库）。没有复杂的设置，例如镜像等。

由于未升级SQL Server，因此本示例与问题不完全匹配。我认为这仍然是一个不错的答案，因为显示的步骤实际上比真正的就地迁移要简单。

概述：附加了外部驱动器以进行完整备份，主要是为了预防起见。实际上，只有模型和msdb会从外部驱动器恢复。 ldf / mdf留在原处以进行分离/连接。数据库中引用了一些本地帐户。在OS中重新创建它们之后，将重新创建DB中的引用（因为SID可能会更改）。

然后这是对我们有用的步骤：

1）注意服务器级别的设置，这些设置将在步骤12（服务器角色）和步骤18到23中恢复。

2）将SQL Server 2012修补到SP3（如果要恢复，则需要一致性）

3）验证每个实例上的版本是否匹配。 “选择@@ version”

4）通过运行此脚本来生成这6个脚本。如果有很多实例，Redgate SQL Multiscript可以节省大量时间（将“工具”->“选项” =>“行长”调整为最大值（8192），然后使用“文本输出”。）


备份
恢复
分离
附加
重新创建登录名

将用户重新链接到登录名

-- (1) BACKUP / (2) RESTORE
--    
--*** SET THESE to external drive location
--*** and create the Destination Directories
declare 
    @backupInstanceDir  varchar(300) = 'F:\ExternalDriveBackups\' + replace(@@servername, '\', '_'),
    @dateSuffix         varchar(100) = '2015-12-14'; 

if (object_id('tempdb..DatabaseStatus') is not null)
drop table #DAtabseSTatus;

select 
    d.name DbName, 
    d.state_desc DbState,
    d.user_access_desc UserMode,
    convert(bit, (d.is_read_only * -1 + 1)) as IsWritable,
    d.is_trustworthy_on as IsTrustWorthy,
    d.is_in_standby IsInStandby,
    d.recovery_model_desc RecoveryModel,
    suser_sname(d.owner_sid) as Owner,
    convert(bit, 
        case when d.database_id <= 4 or d.is_distributor = 1
            then 1
            else 0
        end) as IsSystemDb,
    mf.type_desc as FileType,
    mf.name FileName,
    mf.state FileState,
    mf.state_desc FileStatDesc,
    mf.physical_name PhysicalName,
    mf.type as FileTypeId    
into #DatabaseStatus
from
    sys.master_files AS mf
join sys.databases AS d
ON  mf.database_id = d.database_id
where
    1=1
order by
    d.name,
    mf.physical_name;

if object_id('tempdb..#sqlOut') is not null
    drop table #sqlOutBU

if object_id('tempdb..#sqlOut') is not null
    drop table #sqlOutRE

create table #sqlOutBU
(
    Command nvarchar(max) not null,
    Row int identity(1,1) not null primary key
);

create table #sqlOutRE
(
    Command nvarchar(max) not null,
    Row int identity(1,1) not null primary key
);

insert into #sqlOutBU select char(10) + '-- BACKUP SCRIPT' + char(10);
insert into #sqlOutRE select char(10) + '-- RESTORE SCRIPT' + char(10);


insert into #sqlOutBU select char(10) + char(10) + '/* ---------------------------------------------------------------------------------------------' + char(10) + 
'ServerName: ' + @@servername + char(10) + 'ServiceName: ' + @@servicename + char(10) + 'Version: ' + @@version + 
'--------------------------------------------------------------------------------------------- */';

insert into #sqlOutRE select char(10) + char(10) + '/* ---------------------------------------------------------------------------------------------' + char(10) + 
'ServerName: ' + @@servername + char(10) + 'ServiceName: ' + @@servicename + char(10) + 'Version: ' + @@version + 
'--------------------------------------------------------------------------------------------- */';        

PRINT '--Script for Backing up all DBs in a SQL Server Instance to a specific location' 

SET nocount ON 

insert into #sqlOutBU select char(10) + 
'--' + char(10) + '-- BACKUP ' + @@servername + '--' + char(10) + 
'use [Master]; set deadlock_priority high;' + char(10);

insert into #sqlOutRE select '
-- RESTORE
--
-- BE SURE TO BACKUP SYSTEM DBS TO AN ALTERNATE LOCATION JUST BEFORE RESTORING!
--
use [Master]; set deadlock_priority high;' + char(10);

DECLARE @dbname nvarchar(128) 
declare dblist_cursor cursor fast_forward for 
select [name] from master.sys.databases where [name] != 'tempdb'
order by iif(database_id <= 4, '0', '1') + [name]

open dblist_cursor 
fetch next from dblist_cursor into @dbname 

while @@fetch_status = 0 
begin 

    declare @bak nvarchar(300) = @backupInstanceDir + '\' + @dbname + '_' + @dateSuffix + '.bak';

    insert into #sqlOutBU select char(10) + 'backup database [' + @dbname + '] to disk = ''' + @bak + ''' WITH COPY_ONLY, NOFORMAT, NOINIT, ' + char(10) + 
        'NAME = N''' + @dbName + '-Full'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 25;';

    insert into #sqlOutRE select 'restore database [' + @dbName + '] from disk = ''' + @bak + ''' WITH FILE = 1,' + char(10) +
    (
        select '    move ''' + FileName + ''' to ''' + PhysicalName + '''' From #DatabaseStatus
        where FileType = 'Rows' and DbName = @dbName
    ) + ',' + char(10) +
    (
        select '    move ''' + FileName + ''' to ''' + PhysicalName + '''' From #DatabaseStatus
        where FileType = 'Log' and DbName = @dbName
    ) + ',' + char(10) +
    '    NOUNLOAD, REPLACE, STATS = 25;' + char(10);               

    fetch next from dblist_cursor into @dbname 
end 

close dblist_cursor 
deallocate dblist_cursor 

insert into #sqlOutBU select char(10) + 'go' + char(10);
insert into #sqlOutRE select char(10) + 'go' + char(10);

select Command from #sqlOutBU order by Row; -- BACKUP SCRIPT
select Command from #sqlOutRE order by Row; -- RESTORE SCRIPT

go



--
-- (3) DETACH  -  Org Author: Artemakis Artemiou
--      

if object_id('tempdb..#sqlOutDT') is not null
    drop table #sqlOutDT

create table #sqlOutDT
(
    Command nvarchar(max) not null,
    Row int identity(1,1) not null primary key
);

insert into #sqlOutDT select char(10) + '-- DETACH all DBs from a SQL Server Instance' + char(10);      

insert into #sqlOutDT select char(10) + char(10) + '/* ---------------------------------------------------------------------------------------------' + char(10) + 
'ServerName: ' + @@servername + char(10) + 'ServiceName: ' + @@servicename + char(10) + 'Version: ' + @@version + 
'--------------------------------------------------------------------------------------------- */';

SET nocount ON 

insert into #sqlOutDT select char(10) + '--' + char(10) + '-- DETACH ' + @@servername + char(10) + '--' + char(10) + '
use MAster; set deadlock_priority high;' + char(10) + char(10);

DECLARE @dbname nvarchar(128) 
DECLARE dblist_cursor CURSOR fast_forward FOR 
SELECT [name] 
FROM   master.sys.databases 
WHERE  database_id > 4 

OPEN dblist_cursor 
FETCH next FROM dblist_cursor INTO @dbname 

WHILE @@FETCH_STATUS = 0 
BEGIN 
    insert into #sqlOutDT select
    'alter database ' + @dbname + ' set single_user with rollback immediate;' + char(10) +
    'EXEC sp_detach_db ''' + @dbname + ''', ''true'';' + char(10);
    FETCH next FROM dblist_cursor INTO @dbname 
END 

CLOSE dblist_cursor 
DEALLOCATE dblist_cursor 

insert into #sqlOutDT select char(10) + 'go' + char(10);
select Command from #sqlOutDT order by Row;

go



--
-- (4) ATTACH  -  Org Author: Artemakis Artemiou
--    

if object_id('tempdb..#sqlOut') is not null
    drop table #sqlOutAT

create table #sqlOutAT
(
    Command nvarchar(max) not null,
    Row int identity(1,1) not null primary key
);

insert into #sqlOutAT select char(10) + '-- ATTACH ALL DBs to a SQL Server Instance' + char(10);

insert into #sqlOutAT select char(10) + char(10) + '/* ---------------------------------------------------------------------------------------------' + char(10) + 
'ServerName: ' + @@servername + char(10) + 'ServiceName: ' + @@servicename + char(10) + 'Version: ' + @@version + 
'--------------------------------------------------------------------------------------------- */';

SET NOCOUNT ON

insert into #sqlOutAT select char(10) + '--' + char(10) + '-- ATTACH ' + @@servername + char(10) + '--' + char(10) + 
'use MAster;' + char(10) + char(10);

DECLARE @dbname nvarchar(128);

DECLARE DBList_cursor CURSOR fast_forward FOR 
select [name] from master.sys.databases where database_id > 4
order by name;

OPEN DBList_cursor

FETCH NEXT FROM DBList_cursor 
INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN

declare @attach_TSQL_script varchar(max)
set @attach_TSQL_script=''
set @attach_TSQL_script=@attach_TSQL_script+'CREATE DATABASE ' + @dbname +' ON ' 

declare @tsql varchar(max),@filename varchar(max)
set @tsql='DECLARE DBFiles_cursor CURSOR FOR select [filename] from '+ @dbname + '.sys.sysfiles'

execute (@tsql) 

PRINT '--'+@dbname 

OPEN DBFiles_cursor
FETCH NEXT FROM DBFiles_cursor INTO @filename

WHILE @@FETCH_STATUS = 0
BEGIN   
set @attach_TSQL_script=@attach_TSQL_script+ char(10)+'    (FILENAME = '''+ @filename +'''),' 
FETCH NEXT FROM DBFiles_cursor INTO @filename
END

set @attach_TSQL_script=SUBSTRING(@attach_TSQL_script,0,len(@attach_TSQL_script))
set @attach_TSQL_script=@attach_TSQL_script+ char(10) +'    FOR ATTACH;';

insert into #sqlOutAT select @attach_TSQL_script + char(10);

PRINT @attach_TSQL_script 
PRINT ''

CLOSE DBFiles_cursor
DEALLOCATE DBFiles_cursor

FETCH NEXT FROM DBList_cursor 
INTO @dbname

END 

CLOSE DBList_cursor
DEALLOCATE DBList_cursor

insert into #sqlOutAT select char(10) + 'go' + char(10);
select Command from #sqlOutAT order by Row;
go



--
-- (5) GENERATE A 'RE-CREATE LOGINS' SCRIPT
--
-- This script was modified from a version that was designed to copy from one server to another:
--      http://stackoverflow.com/a/5983773/538763
--


USE [master]

if object_id('tempdb..#sqlOut') is not null
drop table #sqlOut;

create table #sqlOut
(
Command nvarchar(max) not null,
Row int identity(1,1) not null primary key
);

insert into #sqlOut select char(10) + '-- RECREATE LOGINS' + char(10);


insert into #sqlOut select char(10) + char(10) + '/* ---------------------------------------------------------------------------------------------' + char(10) + 
'ServerName: ' + @@servername + char(10) + 'ServiceName: ' + @@servicename + char(10) + 'Version: ' + @@version + 
'--------------------------------------------------------------------------------------------- */';

insert into #sqlOut select 'use Master;' + char(10);
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
declare @Debug bit = 0;
declare @PartnerServer varchar(100) = @@SERVICENAME;  -- use current server before it is shutdown (disabled below)

declare
    @MaxID int,
    @CurrID int,
    @SQL nvarchar(max),
    @LoginName sysname,
    @IsDisabled int,
    @Type char(1),
    @SID varbinary(85),
    @SIDString nvarchar(100),
    @PasswordHash varbinary(256),
    @PasswordHashString nvarchar(300),
    @RoleName sysname,
    @Machine sysname,
    @PermState nvarchar(60),
    @PermName sysname,
    @Class tinyint,
    @MajorID int,
    @ErrNumber int,
    @ErrSeverity int,
    @ErrState int,
    @ErrProcedure sysname,
    @ErrLine int,
    @ErrMsg nvarchar(2048);

declare @Logins Table (LoginID int identity(1, 1) not null primary key,
                    [Name] sysname not null,
                    [SID] varbinary(85) not null,
                    IsDisabled int not null,
                    [Type] char(1) not null,
                    PasswordHash varbinary(256) null)
declare @Roles Table (RoleID int identity(1, 1) not null primary key,
                RoleName sysname not null,
                LoginName sysname not null)
declare @Perms Table (PermID int identity(1, 1) not null primary key,
                LoginName sysname not null,
                PermState nvarchar(60) not null,
                PermName sysname not null,
                Class tinyint not null,
                ClassDesc nvarchar(60) not null,
                MajorID int not null,
                SubLoginName sysname null,
                SubEndPointName sysname null)

Set NoCount On;

If CharIndex('\', @PartnerServer) > 0
Begin
Set @Machine = LEFT(@PartnerServer, CharIndex('\', @PartnerServer) - 1);
End
Else
Begin
Set @Machine = @PartnerServer;
End

-- Get all Windows logins from principal server
Set @SQL = 'Select P.name, P.sid, P.is_disabled, P.type, L.password_hash' + CHAR(10) +
    'From ' /*+ QUOTENAME(@PartnerServer) + '.*/ + 'master.sys.server_principals P' + CHAR(10) +
    'Left Join '/* + QUOTENAME(@PartnerServer) + '.*/ + 'master.sys.sql_logins L On L.principal_id = P.principal_id' + CHAR(10) +
    'Where P.type In (''U'', ''G'', ''S'')' + CHAR(10) +
    'And P.name <> ''sa''' + CHAR(10) +
    'And P.name Not Like ''##%''' + CHAR(10) +
    'and P.Name Not like ''NT SERVICE%''' + CHAR(10) +
    'And CharIndex(''' + @Machine + '\'', P.name) = 0;';

Insert Into @Logins (Name, SID, IsDisabled, Type, PasswordHash)
Exec sp_executesql @SQL;

-- Get all roles from principal server
Set @SQL = 'Select RoleP.name, LoginP.name' + CHAR(10) +
    'From '/* + QUOTENAME(@PartnerServer) + '.*/ + 'master.sys.server_role_members RM' + CHAR(10) +
    'Inner Join '/* + QUOTENAME(@PartnerServer) + .*/ +'master.sys.server_principals RoleP' +
    CHAR(10) + char(9) + 'On RoleP.principal_id = RM.role_principal_id' + CHAR(10) +
    'Inner Join '/* + QUOTENAME(@PartnerServer) + '.*/ + 'master.sys.server_principals LoginP' +
    CHAR(10) + char(9) + 'On LoginP.principal_id = RM.member_principal_id' + CHAR(10) +
    'Where LoginP.type In (''U'', ''G'', ''S'')' + CHAR(10) +
    'And LoginP.name <> ''sa''' + CHAR(10) +
    'And LoginP.name Not Like ''##%''' + CHAR(10) +
    'And LoginP.name Not Like ''NT SERVICE%''' + CHAR(10) +
    'And RoleP.type = ''R''' + CHAR(10) +
    'And CharIndex(''' + @Machine + '\'', LoginP.name) = 0;';

Insert Into @Roles (RoleName, LoginName)
Exec sp_executesql @SQL;

-- Get all explicitly granted permissions
Set @SQL = 'Select P.name Collate database_default,' + CHAR(10) +
    '   SP.state_desc, SP.permission_name, SP.class, SP.class_desc, SP.major_id,' + CHAR(10) +
    '   SubP.name Collate database_default,' + CHAR(10) +
    '   SubEP.name Collate database_default' + CHAR(10) +
    'From '/* + QUOTENAME(@PartnerServer) + '.*/ + ' master.sys.server_principals P' + CHAR(10) +
    'Inner Join '/* + QUOTENAME(@PartnerServer) + '.*/ + ' master.sys.server_permissions SP' + CHAR(10) +
    CHAR(9) + 'On SP.grantee_principal_id = P.principal_id' + CHAR(10) +
    'Left Join '/* + QUOTENAME(@PartnerServer) + '.*/ + ' master.sys.server_principals SubP' + CHAR(10) +
    CHAR(9) + 'On SubP.principal_id = SP.major_id And SP.class = 101' + CHAR(10) +
    'Left Join '/* + QUOTENAME(@PartnerServer) + '.*/ + ' master.sys.endpoints SubEP' + CHAR(10) +
    CHAR(9) + 'On SubEP.endpoint_id = SP.major_id And SP.class = 105' + CHAR(10) +
    'Where P.type In (''U'', ''G'', ''S'')' + CHAR(10) +
    'And P.name <> ''sa''' + CHAR(10) +
    'And P.name Not Like ''##%''' + CHAR(10) +
    'And P.name Not Like ''NT SERVICE%''' + CHAR(10) +
    'And CharIndex(''' + @Machine + '\'', P.name) = 0;'

Insert Into @Perms (LoginName, PermState, PermName, Class, ClassDesc, MajorID, SubLoginName, SubEndPointName)
Exec sp_executesql @SQL;

--select * from @Logins;
--select * from @Roles;
--select * from @perms;


Select @MaxID = Max(LoginID), @CurrID = 1
From @Logins;

While @CurrID <= @MaxID
Begin
Select @LoginName = Name,
    @IsDisabled = IsDisabled,
    @Type = [Type],
    @SID = [SID],
    @PasswordHash = PasswordHash
From @Logins
Where LoginID = @CurrID;

--    If Not Exists (Select 1 From sys.server_principals
--              Where name = @LoginName)
Begin

    set @sql = char(10);
    set @sql += 'If Not Exists (Select 1 From sys.server_principals Where name = ''' + @LoginName + ''')' + char(10);
    set @sql += 'begin' + char(10) + '    ';

    Set @SQL += 'Create Login ' + quotename(@LoginName)
    If @Type In ('U', 'G')
    Begin
        Set @SQL = @SQL + ' From Windows;'
    End
    Else
    Begin
        Set @PasswordHashString = '0x' +
            Cast('' As XML).value('xs:hexBinary(sql:variable("@PasswordHash"))', 'nvarchar(300)');

        Set @SQL = @SQL + ' With Password = ' + @PasswordHashString + ' HASHED;  --, ';

        Set @SIDString = '0x' +
            Cast('' As XML).value('xs:hexBinary(sql:variable("@SID"))', 'nvarchar(100)');
        Set @SQL = @SQL + 'SID = ' + @SIDString + ';' + char(10);
    End

    set @sql += char(10) +
        '    print ''Created Login ' + @loginName  + ''';' + char(10) +
        'end' + char(10) +
        'else' + char(10) +
        convert(nvarchar(max), '    print ''Login ' + @loginName + ' already existed. '';') + char(10);

    If @Debug = 0
    insert into #sqlOut select @SQL;                      
    Else
    Print @SQL;

    If @IsDisabled = 1
    Begin
        Set @SQL = 'Alter Login ' + quotename(@LoginName) + ' Disable;'
        If @Debug = 0
            insert into #sqlOut select @SQL;                              
        Else              
            Print @SQL;              
    End
    End
Set @CurrID = @CurrID + 1;
End


insert into #sqlOut select char(10) + 'use Master;' + char(10);

Select @MaxID = Max(RoleID), @CurrID = 1
From @Roles;

While @CurrID <= @MaxID
Begin
Select @LoginName = LoginName,
    @RoleName = RoleName
From @Roles
Where RoleID = @CurrID;

/*  If Not Exists (Select 1 From sys.server_role_members RM
            Inner Join sys.server_principals RoleP
                On RoleP.principal_id = RM.role_principal_id
            Inner Join sys.server_principals LoginP
                On LoginP.principal_id = RM.member_principal_id
            Where LoginP.type In ('U', 'G', 'S')
            And RoleP.type = 'R'
            And RoleP.name = @RoleName
            And LoginP.name = @LoginName)*/
Begin
    If @Debug = 0
    Begin          
        insert into #sqlOut select 'Exec sp_addsrvrolemember @rolename = ''' + @RoleName + ''', @loginame = ''' + @LoginName + ''';';
    End
    Else
    Begin
        Print 'Exec sp_addsrvrolemember @rolename = ''' + @RoleName + ''',';
        Print '     @loginame = ''' + @LoginName + ''';';
    End
End

Set @CurrID = @CurrID + 1;
End


insert into #sqlOut select char(10) + 'use Master;' + char(10);


Select @MaxID = Max(PermID), @CurrID = 1
From @Perms;

While @CurrID <= @MaxID
Begin
Select @PermState = PermState,
    @PermName = PermName,
    @Class = Class,
    @LoginName = LoginName,
    @MajorID = MajorID,
    @SQL = PermState + space(1) + PermName + SPACE(1) +
        Case Class When 101 Then 'On Login::' + QUOTENAME(SubLoginName)
                When 105 Then 'On ' + ClassDesc + '::' + QUOTENAME(SubEndPointName)
                Else '' End +
        ' To ' + QUOTENAME(LoginName) + ';'
From @Perms
Where PermID = @CurrID;

/*If Not Exists (Select 1 From sys.server_principals P
            Inner Join sys.server_permissions SP On SP.grantee_principal_id = P.principal_id
            Where SP.state_desc = @PermState
            And SP.permission_name = @PermName
            And SP.class = @Class
            And P.name = @LoginName
            And SP.major_id = @MajorID)*/
Begin
    If @Debug = 0
            insert into #sqlOut select @sql;                      
    Else          
        Print @SQL;          
End

Set @CurrID = @CurrID + 1;
End


select Command from #sqlOut as SqlOut order by Row;
go


--
-- (6) Generate a script to Re-link all users to logins based on current state (before shutdown)
--

use Master;

if object_id('tempdb..#sqlOut') is not null
drop table #sqlOut;

create table #sqlOut
(
    Command nvarchar(max) not null,
    Row int identity(1,1) not null primary key
);

insert into #sqlOut select char(10) + '-- RELINK USERS TO LOGINS' + char(10);

insert into #sqlOut select char(10) + char(10) + '/* ---------------------------------------------------------------------------------------------' + char(10) + 
'ServerName: ' + @@servername + char(10) + 'ServiceName: ' + @@servicename + char(10) + 'Version: ' + @@version + 
'--------------------------------------------------------------------------------------------- */';

declare @dbCmd varchar(8000) = '
use ?;

insert into #sqlOut select char(10) + ''use ?;'' + char(10);  

with links as
(
select u.name as UserName,
    l.loginname as LoginName
    from sysusers u 
    join master..syslogins l
    on u.sid = l.sid        
where u.name != ''dbo''
    and u.isSqlUser = 1 or l.isNtName = 1 or l.isNtGroup = 1
)
insert into #sqlOut 
select ''alter user ['' + UserName + ''] with name = ['' + UserName + ''], login = ['' + LoginName + '']''
from links
';    

exec sp_MSforeachdb @dbCmd;

select Command from #sqlOut order by Row;

go

5）运行脚本以将包括系统（主数据库，msdb，模型）在内的所有DB备份到外部驱动器。

6）运行脚本以分离所有DBs

7 ）C盘将被重新格式化。如果LDF / MDF不在C上，则保留它们。

8）Windows Server 2012安装在C上。

9）将原始系统文件的LDF / MDF移出如果它们不在C驱动器上，请按照这种方式操作。10）将重新安装SQL Server 2012并将其修补到SP3
。重新创建系统用户/组帐户

11）将系统DB备份到新位置或文件名（小心不要覆盖原始文件！）。

12）运行重新创建角色片段。像这样的东西：

USE [master]
CREATE SERVER ROLE [SomeServerRole]
--ALTER SERVER ROLE [dbcreator] ADD MEMBER [SomeServerRole]
--ALTER SERVER ROLE [bulkadmin] ADD MEMBER [SomeServerRole]
-- ALTER SERVER ROLE [SomeServerRole] ADD MEMBER [SomeMemberOrRole]

13）运行重新创建登录脚本（如果恢复了登录名则不执行任何操作）

14）停止SQL AGENT 。

（可以在这里恢复Master，我们先退出了。）

15）使用上面的脚本附加mdf / ldf。如果失败，则使用上面的脚本从bak手动还原。

16）尝试还原模型

17）确保SQL Agent已停止。恢复MSDB（链接）
如果失败，则需要重新创建作业+维护计划+邮件配置+操作员

18）打开用户登录脚本...

    a. If there are master users (rare?) then First Re-Create users for master since it was not restored:
        use master;       
        CREATE USER [ABC] FOR LOGIN [machine\ABC]

    b. Run the rest of the script

19）使服务代理能够匹配原始值
从sys.databases中选择名称is_broker_enabled;

    alter database MSDB set single_user with rollback immediate;
    ALTER DATABASE [MSDB] SET ENABLE_BROKER;
    alter database MSDB set multi_user;

20）启动SQL Agent

21）将并行度阈值设置为原始值

22）将任何数据库设置调整为其原始值：

 declare @dbCmd varchar(8000) = '
      use ?;
      if db_name() not in (''master'', ''model'', ''tempdb'', ''msdb'')
      begin
             print ''Adjusting [?]...'';    
            alter database [?] set single_user with rollback immediate;
             aLTER AUTHORIZATION ON DATABASE::[?] to [sa];
            -- alter database [?] set trustworthy on;
            ALTER DATABASE [?] SET AUTO_CLOSE OFF WITH NO_WAIT;     
            alter database [?] set multi_user;
      end     
      else
             print ''Skipping [?]...'';
    ';    

    exec sp_MSforeachdb @dbCmd;

23）检查作业所有权：

select s.name as JobName, l.name as login, SUSER_SNAME(s.owner_sid) AS login2
from  msdb..sysjobs s 
left join master.sys.syslogins l on s.owner_sid = l.sid

如果还升级了SQL Server版本，我不认为可以还原模型和msdb数据库，因此作业将丢失由于https://support.microsoft.com/zh-cn/kb/264474

缺少的内容：主数据库中的原始用户（罕见？ ）
服务器角色
？

#5 楼

两种方法本身都没有错-我都做过，而且两种结果通常都很好。

如果迁移方法存在问题，那不是技术问题：这是懒惰。我经常发现，一家公司尚未完全升级到xxxx版本的原因是因为他们选择了摇摆式迁移并且从未四处走动以完成全部迁移。现在，他们有两组或更多组服务器，而不是一组。