sqlserver实例迁移

SQLServer实例迁移.docx

应用场景

数据库实例中有大量数据库，用户名，及密码或用户名密码丢失，不能强制修改（如公司.4服务器数据库迁移）

准备工作

查看数据库版本号（此方案暂只适用于同版本的sqlserver)

select @@version

备份系统库master、model、msdb到新服务器

一：停止数据库服务，以单用户模式启动

net stop mssqlserver
net start mssqlserver /m

二：连接实例

查看当前实例名：

连接当前实例：

sqlcmd -E -S WIN-C5KO3QMA1VS

三：还原madter数据库（指定目录替换为当前数据库的目录）

RESTORE DATABASE master
FROM DISK = 'D:\master.bak'
with replace,
MOVE 'master' TO 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf',
MOVE 'mastlog' TO 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mastlog.ldf';

四：用跟踪标志3608来启动

net start mssqlserver /f /m /T3608

五：Sqlcmd连接SQL Server

sqlcmd -E -S WIN-C5KO3QMA1VS

六：查看原实例中的文件路径

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

七：修改当前实例中的系统数据库路径为正确路径

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

八：停止服务以正常模式启动

net stop mssqlserver
net start mssqlserver

九：恢复数据库

–恢复model数据库

RESTORE DATABASE model 
FROM DISK='D:\model.bak' 
WITH REPLACE,
move 'modeldev' TO 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\model.mdf',
move 'modellog' TO 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\modellog.ldf';
--恢复msdb数据库
RESTORE DATABASE msdb 
FROM DISK='d:\msdb.bak' 
WITH REPLACE,
move 'MSDBData' TO 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MSDBData.mdf',
move 'MSDBLog' TO 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MSDBLog.ldf';

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

十：修改服务器名称

use master
go
select @@servername --查看主机名
EXEC sp_dropserver @@servername --删除主机名
EXEC sp_addserver [WIN-C5KO3QMA1VS], local  --修改主机名

重启服务后查看主机名是否一致

十一、恢复所有用户数据库

完成第十部后，所有用户数据库都处于置疑状态，因为本地没有数据文件，后续就是恢复所有用户数据库即可

USE [master]
RESTORE DATABASE [test_shrink] FROM DISK = N'D:\backup\test.bak' WITH FILE = 1,
MOVE N'test_shrink' TO N'D:\Program Files\Microsoft SQL Server\MSSQL.1\test_shrink.mdf',
MOVE N'test_shrink_log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL.1\test_shrink_log.ldf', NOUNLOAD, STATS = 5
GO
--直接对应数据库上右击还原，重定位路径即可