SQLServer大批量数据库迁移方案

　　在项目实施过程中，有时候会遇到大批量数据库（上百个）同时迁移的问题，如果采用常规的备份还原的方式会消耗非常多的时间，对业务会造成非常大的影响，生产环境下业务很难接受这种方式，所以我们采取镜像的方式来做迁移，即提前搭建镜像，在迁移的时候进行故障转移，然后断开镜像连接，由于数据库太多，我们会通过脚本进行批量操作。

　　首先需要建立所有数据库的源服务器到目标数据库的镜像关系，由于数据库太多，这里我们也使用脚本进行批量备份和还原：

　　1. 批量备份指定的数据库和日志（备份数据库时间较长，故备份日志）：

Declare @total int
select @total=count(name) from sys.databases where name in
　　(
　　'DB1','DB2','DB3','DB4','DB100'
　　)
while @total<>0
　　begin
　　Declare @DBname varchar(1000)
　　Declare @sql01 varchar(2000)
　　Declare @sql02 varchar(2000)
　　Declare @path varchar(1000)
　　Declare @date varchar(50)
　　select @DBname=a.name from
      　　(select name,row_number()over(order by name) sequence from sys.databases
      　　　　where name in
　　　　　　　　　　　　(
　　　　　　　　　　　　'DB1','DB2','DB3','DB4','DB100'
　　　　　　　　　　　　))a
　　where @total=a.sequence;
set @date=replace(convert(varchar,getdate(),23),'-','')
set @path='Y:\Backup\'+@DBname+'_'+@date+'.bak';
print @path
set @sql01='BACKUP DATABASE '+quotename(@DBname,'[]')+' TO DISK='+quotename(@path,'''')+' WITH NOFORMAT,NOINIT,NAME='+quotename('Full 　　　　　　　　　　　　Database Backup','''')+',SKIP,NOREWIND,NOUNLOAD,STATS = 10'
print @sql01
exec (@sql01);
set @total=@total-1
end

　　备份日志：

Declare @total int
select @total=count(name) from sys.databases where name in
(
'DB1','DB2','DB3','DB4','DB100'
)
while @total<>0
begin
 Declare @DBname varchar(1000)
 Declare @sql01 varchar(2000)
 Declare @sql02 varchar(2000)
 Declare @path varchar(1000)
 Declare @date varchar(50)
 select @DBname=a.name from
      (select name,row_number()over(order by name) sequence from sys.databases
      where name in
(
'DB1','DB2','DB3','DB4','DB100'
))a
      where @total=a.sequence;
 set @date=replace(convert(varchar,getdate(),23),'-','')
 set @path='Y:\Backup\'+@DBname+'_'+@date+'_Log.bak';
 print @path
set @sql01='BACKUP LOG '+quotename(@DBname,'[]')+' TO DISK='+quotename(@path,'''')+' WITH NOFORMAT,NOINIT,NAME='+quotename('Log Backup','''')+',SKIP,NOREWIND,NOUNLOAD,STATS = 10'
print @sql01
exec (@sql01);
set @total=@total-1
end

　　Copy数据库备份文件和日志备份文件到目标服务器

　　2. 自动检查逻辑名（用RESTORE FILELISTONLY命令从备份文件中读取数据库的信息，本例中有三个逻辑文件）后进行还原：

Create table #database
(name varchar(100))
insert into #database
values
('DB1')
,('DB2')
,('DB3')
,('DB4')
,('DB100')
--drop table #database
--select * from #database
Declare @total int
select @total=count(name) from #database where name in
(
'DB1','DB2','DB3','DB4','DB100'
)
while @total<>0
begin
 Declare @DBname varchar(1000)
 Declare @sql01 varchar(2000)
 Declare @sql02 varchar(2000)
 Declare @path1 varchar(1000)
 Declare @path2 varchar(1000)
 Declare @path3 varchar(1000)
 Declare @path4 varchar(1000)
 Declare @Mdfname varchar(50)
 Declare @Ndfname varchar(50)
 Declare @Logname varchar(50)
 Declare @date varchar(50)
 select @DBname=a.name from
      (select name,row_number()over(order by name) sequence from #database
      where name in
(
'DB1','DB2','DB3','DB4','DB100'
))a
      where @total=a.sequence;
 set @date=replace(convert(varchar,getdate(),23),'-','')
 set @path1='Y:\Backup\'+@DBname+'_'+@date+'.bak'
 set @path2='W:\SQLServer\Data\'+@DBname+'.mdf'
 set @path3='W:\SQLServer\Data\'+@DBname+'.ndf'
 set @path4='Y:\SQLServer\Log\'+@DBname+'.ldf'
create table #Logicalname
(LogicalName nvarchar(128)
,PhysicalName nvarchar(260)
,Type char(1)
,FileGroupName nvarchar(128)
,Size numeric(20,0)
,MaxSize numeric(20,0)
,FileID bigint
,CreateLSN numeric(25,0)
,DropLSN numeric(25,0) NULL
,UniqueID uniqueidentifier
,ReadOnlyLSN numeric(25,0)
,ReadWriteLSN numeric(25,0)
,BackupSizeInBytes bigint
,SourceBlockSize int
,FileGroupID int
,LogGroupGUID uniqueidentifier
,DifferentialBaseLSN numeric(25,0)
,DifferentialBaseGUID uniqueidentifier
,IsReadOnly bit
,IsPresent bit
,TDEThumbprint varbinary(32)
)
Declare @restore varchar(1000)
set @restore='RESTORE FILELISTONLY from disk='+QUOTENAME(@path1,'''')
print @restore
exec (@restore)
insert into #Logicalname exec (@restore)
--select * from #Logicalname
 select @Mdfname=LogicalName from #Logicalname where Fileid=1
 select @Ndfname=LogicalName from #Logicalname where FileID=3
 select @Logname=LogicalName from #Logicalname where FileID=2
set @sql01='RESTORE DATABASE '+quotename(@DBname,'[]')+' from disk='+quotename(@path1,'''')+' WITH FILE = 1,MOVE '+quotename(@Mdfname,'''')+' to '+
   quotename(@path2,'''')+',move '+quotename(@Ndfname,'''')+' to '+quotename(@path3,'''')+
   ',move '+quotename(@Logname,'''')+' to '+quotename(@path4,'''')+',NORECOVERY,NOUNLOAD,STATS = 10'
print @sql01
exec (@sql01);
set @total=@total-1
drop table #Logicalname
end

　　还原日志

Declare @total int
select @total=count(name) from sys.databases where name in
(
'DB1','DB2','DB3','DB4','DB100'
)
while @total<>0
begin
 Declare @DBname varchar(1000)
 Declare @sql01 varchar(2000)
 Declare @sql02 varchar(2000)
 Declare @path varchar(1000)
 Declare @date varchar(50)
 select @DBname=a.name from
      (select name,row_number()over(order by name) sequence from sys.databases
      where name in
(
'DB1','DB2','DB3','DB4','DB100'
))a
      where @total=a.sequence;
 set @date=replace(convert(varchar,getdate(),23),'-','')
 set @path='Y:\Backup\'+@DBname+'_'+@date+'_Log.bak';
 print @path
set @sql01='RESTORE LOG '+quotename(@DBname,'')+' from disk='+quotename(@path,'''')+' with FILE=1,NORECOVERY,NOUNLOAD,STATS = 10'
print @sql01
exec (@sql01);
set @total=@total-1
end　

　　3. 创建镜像

　　在目标服务器上进行partner配置：

Declare @total int
select @total=count(name) from sys.databases where name in
(
'DB1','DB2','DB3','DB4','DB100'
)
while @total<>0
begin
 Declare @DBname varchar(1000)
 Declare @sql01 varchar(2000)
 Declare @sql02 varchar(2000)
 Declare @path varchar(1000)
 Declare @date varchar(50)
 select @DBname=a.name from
      (select name,row_number()over(order by name) sequence from sys.databases
      where name in
(
'DB1','DB2','DB3','DB4','DB100'
))a
      where @total=a.sequence;
set @sql01='ALTER DATABASE '+quotename(@DBname,'')+' set PARTNER='+quotename('TCP://SourceServer.domain:5022','''')
print @sql01
exec (@sql01);
set @total=@total-1
end

　　在源服务器上配置镜像，并将镜像设置为高性能模式（以防影响生产环境业务性能）：

Declare @total int
select @total=count(name) from sys.databases where name in
(
'DB1','DB2','DB3','DB4','DB100'
)
while @total<>0
begin
 Declare @DBname varchar(1000)
 Declare @sql01 varchar(2000)
 Declare @sql02 varchar(2000)
 Declare @path varchar(1000)
 Declare @date varchar(50)
 select @DBname=a.name from
      (select name,row_number()over(order by name) sequence from sys.databases
      where name in
(
'DB1','DB2','DB3','DB4','DB100'
))a
      where @total=a.sequence;
set @sql01='ALTER DATABASE '+quotename(@DBname,'')+' set PARTNER='+quotename('TCP://DestinationServer.Domain:5022','''');
set @sql02='ALTER DATABASE '+quotename(@DBname,'')+' SET PARTNER SAFETY OFF';
print @sql01
print @sql02
exec (@sql01);
exec (@sql02);
set @total=@total-1
end

　　4. 以上都是迁移前的准备工作，其它准备工作如迁移账号和权限，迁移Job这里也给出相应的方法：

　　迁移所有账号：请参考微软官方文档https://support.microsoft.com/zh-cn/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

　　账号权限迁移，在源服务器用以下脚本生成语句，copy所有语句到目的服务器执行：

select N'EXEC sp_addsrvrolemember  N''' +sp.name+ ''' ,N''' + rsp.name+''' '

FROM sys.server_principals sp    

LEFT JOIN sys.server_role_members srm ON sp.principal_id=srm.member_principal_id    

LEFT JOIN sys.server_principals rsp ON srm.role_principal_id=rsp.principal_id  

where rsp.name  is not null

　　迁移Job：右键Job, 按’F7’, 多选了之后右键，点击create to new window，然后copy整个窗口的内容到目的服务器执行

　　5. 迁移时，先更改镜像状态为安全模式（只有在安全模式下，镜像才能执行故障转移）：

Declare @UserDBname nvarchar(1000)
Declare @sql nvarchar(2000)
Declare @Total int

select @Total=count(*) from sys.databases where name not in               
(
N'master', N'model', N'msdb', N'tempdb', N'distribution', N'DWDiagnostics',
N'DWConfiguration', N'DWQueue', N'resource',N'ReportServer',N'ReportServerTempDB',
N'ReportServer$KABA_MAINTempDB',N'ReportServer$KABA_MAIN'
)

while @Total<>0
 begin
 select @UserDBname=a.name from (
         Select name, row_number() over(order by name)  Sequence from sys.databases
           where name not in
(
N'master', N'model', N'msdb', N'tempdb', N'distribution', N'DWDiagnostics',
N'DWConfiguration', N'DWQueue', N'resource',N'ReportServer',N'ReportServerTempDB',
N'ReportServer$KABA_MAINTempDB',N'ReportServer$KABA_MAIN'
)
) a
 where @Total=a.Sequence;
set @sql=
 'use master;'+
 'Alter database '+quotename(@UserDBname,'')+' set partner failover
 ';
print @sql;
exec (@sql);
set @Total=@Total-1
end

 

　　查看数据库镜像同步状态：

select DB_name(database_id),mirroring_state_desc,mirroring_role_desc,mirroring_safety_level_desc,mirroring_partner_name,mirroring_partner_instance from sys.database_mirroring
where mirroring_guid is not NULL

 

　　6. 在迁移后的镜像主节点（目的服务器）上执行命令删除镜像：

Declare @total int
select @total=count(name) from sys.databases where name in
(
'DB1','DB2','DB3','DB4','DB100'
)
while @total<>0
begin
 Declare @DBname varchar(1000)
 Declare @sql01 varchar(2000)
 Declare @sql02 varchar(2000)
 Declare @path varchar(1000)
 Declare @date varchar(50)
 select @DBname=a.name from
      (select name,row_number()over(order by name) sequence from sys.databases
      where name in
(
'DB1','DB2','DB3','DB4','DB100'
))a
      where @total=a.sequence;
set @sql01=
 'use master;'+
 'Alter database '+quotename(@DBname,'')+' set partner off
 ';
print @sql01
exec (@sql01);
set @total=@total-1
end

 

　　至此，便完成了迁移的过程，在迁移完后，需要检查数据库的孤立账号以及更新统计信息：

　　7. 批量检查孤立账号：

Declare @UserDBname nvarchar(1000)
Declare @sql nvarchar(2000)
Declare @Total int

select @Total=count(*) from sys.databases where name not in               
(
N'master', N'model', N'msdb', N'tempdb', N'distribution', N'DWDiagnostics',
N'DWConfiguration', N'DWQueue', N'resource',N'ReportServer',N'ReportServerTempDB',
N'ReportServer$KABA_MAINTempDB',N'ReportServer$KABA_MAIN'
)

while @Total<>0
 begin
 select @UserDBname=a.name from (
         Select name, row_number() over(order by name)  Sequence from sys.databases
           where name not in
(
N'master', N'model', N'msdb', N'tempdb', N'distribution', N'DWDiagnostics',
N'DWConfiguration', N'DWQueue', N'resource',N'ReportServer',N'ReportServerTempDB',
N'ReportServer$KABA_MAINTempDB',N'ReportServer$KABA_MAIN'
)
) a
 where @Total=a.Sequence;
set @sql=
 'use '+quotename(@UserDBname,'')+'; select DB_Name() as DBName; '+
 'exec sp_change_users_login @Action='+quotename('Report','''')
 ;
print @sql;
exec (@sql);
set @Total=@Total-1
end

 

　　批量删除孤立账号：

Declare @UserDBname nvarchar(1000)
Declare @sql nvarchar(2000)
Declare @Total int

--drop table #Orphan_User
Create table #Orphan_User
(
UserName varchar(100),
UserID varchar(500)
)
select @Total=count(*) from sys.databases where
--           name not in
--(
--N'master', N'model', N'msdb', N'tempdb', N'distribution', N'DWDiagnostics',
--N'DWConfiguration', N'DWQueue', N'resource',N'ReportServer',N'ReportServerTempDB',
--N'ReportServer$KABA_MAINTempDB',N'ReportServer$KABA_MAIN'
--)
--and
name in (N'ZRBT_01077')

while @Total<>0
 begin
 select @UserDBname=a.name from (
         Select name, row_number() over(order by name)  Sequence from sys.databases
           where
--           name not in
--(
--N'master', N'model', N'msdb', N'tempdb', N'distribution', N'DWDiagnostics',
--N'DWConfiguration', N'DWQueue', N'resource',N'ReportServer',N'ReportServerTempDB',
--N'ReportServer$KABA_MAINTempDB',N'ReportServer$KABA_MAIN'
--)
--and
name in (N'ZRBT_01077')
) a
 where @Total=a.Sequence;

set @sql=
 'use '+quotename(@UserDBname,'')+
 ';insert into #Orphan_User exec sp_change_users_login @Action='+quotename('Report','''')
 ;

print @sql;
exec (@sql);
select * from #Orphan_User

Declare @UserName nvarchar(1000);
Declare DropUser cursor
   for
select UserName from #Orphan_User;
open DropUser;
fetch next from DropUser into @UserName;
while @@FETCH_STATUS=0
begin
Declare @DropUsersql varchar(1000)
set @DropUsersql=
'use '+quotename(@UserDBname,'')+';
DROP SCHEMA'+quotename(@UserName,'[]')+';
DROP USER'+ quotename(@UserName,'[]');
print (@DropUsersql)
exec (@DropUsersql)
fetch next from DropUser into @UserName;
end
close DropUser;
deallocate DropUser;

Truncate table #Orphan_User
set @Total=@Total-1
end

 

　　在这个过程中，需要和应用确认具体的需求，比如使用的账号信息和Job的信息等，迁移完成后，需要应用更新连接信息，应用检查完没问题，迁移就算完成了。 　　

　　这便是整个迁移的过程，在遇到的实际情况中，环境可能更加复杂，比如是群集之间的迁移，或者迁移后应用不能更改IP（可以通过修改服务器IP实现），迁移后镜像是否保留等，都是可以以本文为蓝本，做出相应的方案的。

　　

转载于:https://www.cnblogs.com/LC0507/p/11190389.html