阿里云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,等待下一次切换