阿里云sqlserver 迁移到百度云sqlserver 2

阿里云的日志迁移到百度云后，要进行日志的追加操作

1.百度云sql中创建存储过程

2.exec RestoreDBLog 'D:\sqlback\log\log日志所在路径\','库名称'，如果报错，可能因为在执行的时候正在复制，最好不要在整点

use master
go
create procedure RestoreDBLog(   @LogPath varchar(200),
  @DBName varchar(200)
  )

  --exec RestoreDBLog 'D:\DBBackup\log\testex\','testexlog'
  as 
begin
declare @SqlStr varchar(2000),@file_timestamp varchar(50)
declare @Physical_Device_Name nvarchar(200)
declare @TimeBigint bigint
declare @FileName varchar(256)
declare @sql nvarchar(2000)
declare @LogPathGet nvarchar(200)


set @SqlStr = 'dir ' + @LogPath  + '\ /B'

create table #FileName([filename] varchar(200))

insert into #FileName exec master..xp_cmdshell @SqlStr
delete from #FileName where filename is null
delete from #FileName where filename like '%.txt'

alter table #FileName add TimeBigint bigint,RestoreStatus int
update #FileName set TimeBigint=cast(left(right([filename],21),17) as bigint),RestoreStatus=0
   set @LogPathGet=@LogPath+'%'
   select @LogPathGet
   set @sql=N'select top 1 @Physical_Device_Name=physical_device_name from msdb.dbo.backupmediafamily with(nolock) where physical_device_name like @LogPathGet order by media_set_id desc'   
   exec sp_executesql @sql,N'@LogPathGet NVARCHAR(200),@Physical_Device_Name nvarchar(200) OUTPUT',@LogPathGet,@Physical_Device_Name OUTPUT;
   select @Physical_Device_Name
 delete from #FileName where TimeBigint <= cast(left(right(@Physical_Device_Name,21),17) as bigint)
   set @SqlStr	=''
   	while exists (select  * from #FileName where RestoreStatus=0 )
	
			begin				 
				select top 1 @FileName=FileName,@TimeBigint=TimeBigint from 
				       #FileName where  RestoreStatus = 0 order by TimeBigint
                set @SqlStr	 ='restore database ' + @DBName + ' from disk = '+'''' + @logpath  + @FileName +''''+' with norecovery'	
				
				select 	@FileName,	    @TimeBigint
				print (@SqlStr)
				exec(@SqlStr)
				
				update #FileName set RestoreStatus = 1 where TimeBigint = @TimeBigint
		
				set @SqlStr=''
				if not exists (select  * from #FileName where RestoreStatus=0)				             
				begin
					set @SqlStr = 'restore database ' + @DBName + ' with standby='+''''+ @LogPath+ @DBName + '.txt'+''''
					exec(@SqlStr)
                    break
			     end
	         end
			 
		 drop table #FileName

--set @sql='forfiles /p D:\DBbackup\log\ /m *.trn /d -2 /s /c "cmd /c if @ISDIR==FALSE DEL /A A @FILE && Echo @path"'
--exec master..xp_cmdshell @sql
end

2.查询 是否加载日志

select physical_device_name from msdb.dbo.backupmediafamily with(nolock)
where physical_device_name like '%Aso_comment_statistics1%'
order by media_set_id desc 

3. 切库步骤

1、停服（如果可以停的话）
2、禁用写账号
3、kill进程
4、禁用日志备份job，自动传送日志job，日志还原job
5、手动执行备份日志存储过程，备份最后一次尾日志
6、手动执行自动传送日志备份
7、手动执行最后日志还原存储过程
8、检查最后日志还原节点
9、如果正确，改成recovery模式，通知开发人员改连接ip或者域名配置
10、如果一切验证都ok，切换完毕，如果有job启用job，后期做全备，日志备份，搭建备库等等后续工作
11、如果有问题，改回原来ip或者域名配置，启用原来写账号，重新备份数据库，还原数据库，启用日志备份、传输、还原job，等待下一次切换