完整SQL Server实例迁移案例

假设dba遇到以下窘境： 原先服务器出现硬件故障无法启动，需要将整套SQL Server实例迁移到一台备用服务器上 备用服务器和原服务器不同名，连SQL Server安装路径、文件路径都不一样 手头有比较完整的备份，除用户数据库外还有master、msdb和model的完整备份 那么这就涉及到sqlserver完整实例的迁移，包含实例的login及password、各个数据库的用户、所有job、linked servers、端点等所有数据库对象。下面是完整的迁移过程：

1、确认SQL Server版本一致

select @@version返回的号码必须一模一样--因为要恢复系统数据库，要保证恢复的master和msdb和原库一致，否则SQL Server不能正常工作。

2、停止服务以单用户模式启动

3、连接SQL Server实例

4、还原madter数据库

RESTORE DATABASE master
FROM DISK = 'C:\backup\master.bak'
with replace,
MOVE 'master' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf',
MOVE 'mastlog' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf' r> 

5、用跟踪标志3608来启动

由于恢复的master数据库里记载的其他数据库路径和现在的不一致，这时候重新启动SQL Server会失败，必须要用跟踪标志3608来启动

6、Sqlcmd连接SQL Server

 

7、原实例上找数据库文件路径

select db_name(database_id),name,physical_name from sys.master_files

8、修改系统数据库的正确路径

--resource数据库
ALTER DATABASE mssqlsystemresource MODIFY FILE(NAME=DATA,FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\mssqlsystemresource.mdf')
GO
ALTER DATABASE mssqlsystemresource MODIFY FILE(NAME=LOG,FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\mssqlsystemresource.ldf')
GO
--msdb数据库
ALTER DATABASE msdb MODIFY FILE(NAME=MSDBData,FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\msdbdata.mdf')
GO
ALTER DATABASE msdb MODIFY FILE(NAME=MSDBLog,FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\msdblog.ldf')
GO
--model数据库
ALTER DATABASE model MODIFY FILE(NAME=modeldev,FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\model.mdf')
GO
ALTER DATABASE model MODIFY FILE(NAME=modellog,FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\modellog.ldf')
GO
--tempdb数据库
ALTER DATABASE tempdb MODIFY FILE(NAME=tempdev,FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\tempdb.mdf')
GO
ALTER DATABASE tempdbMODIFY FILE(NAME=templog,FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\templog.ldf')
GO

9、停止服务以正常模式启动

10、恢复数据库

--恢复model数据库
RESTORE DATABASE model FROM DISK='c:\lab\model.bak'3 WITH
move 'modeldev' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\model.mdf',
move 'modellog' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\modellog.ldf' ,REPLACE
--恢复msdb数据库
RESTORE DATABASE msdb FROM DISK='c:\lab\msdb.bak'3 WITH
move 'MSDBData' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\MSDBData.mdf',
move 'MSDBLog' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\MSDBLog.ldf' ,REPLACE
Ps:为什麽resource数据库跟tempdb数据库不用还原呢？

resource：包含SQLSERVER附带的所有系统对象副本的只读数据库，resource数据库是不能备份的，而且在SSMS里是看不见的
tempdb：用于保存临时或中间结果集的工作空间。每次启动SQLSERVER实例时SQLSERVER都会根据model数据库为蓝本重新创建此数据库。
服务器实例关闭时，将永久删除tempdb数据库中的所有数据

11、修改服务器名称

迁移到新服务上后需修改master里记录的原服务器名
use master
go
select @@servername
select serverproperty('servername')
IF serverproperty('servername')<>@@servername
BEGIN
DECLARE @server SYSNAME
SET @server=@@servername
EXEC sp_dropserver @server=@server
SET @server=cast(serverproperty('servername') AS SYSNAME)
EXEC sp_addserver @server=@server,@local='LOCAL'
END
ELSE
PRINT '实例名与主机名一致，无需修改！'
运行完上面语句，重启SQL Server服务，再运行SSELECT @@servername

12、恢复所有用户数据库

第11步完成后，所有用户数据库都处于置疑状态，因为本地没有数据文件，后续就是恢复所有用户数据库即可
USE [master]
RESTORE DATABASE [test_shrink] FROM DISK = N'C:\backup\test.bak' WITH FILE = 1,
MOVE N'test_shrink' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\test_shrink.mdf',
MOVE N'test_shrink_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\test_shrink_log.ldf', NOUNLOAD, STATS = 5
GO
--直接对应数据库上右击还原，重定位路径即可。

13、迁移完毕检查

检查原来所有login是否能成功登陆，linked server、数据库触发器、端点及job等对象是否迁移成功。

原文：https://mp.weixin.qq.com/s?__biz=MzA4NzA1MDExMQ%3D%3D&idx=1&mid=2650301493&sn=eb007bde985e9213d2fc077ea61486a1