自动还原远程数据库

自动还原远程数据库

自动还原远程数据库（此方法为了弥补netuse远程还原由于网络波动导致的还原失败）
declare @backup_set_id int
declare @restorefilepath varchar(max)
declare @restore nvarchar(max)
declare @backupmaxid int
declare @servername varchar(50)
declare @databasename varchar(100)
set @databasename='TestDB' -- 还原的数据库需修改
--LinkServer链接服务器需替换
--还原的日志文件位置是否需要修改
select @backupmaxid=max(backup_set_id) from [LinkServer].[msdb].[dbo].[backupset] where database_name=@databasename and type='L'
select @backup_set_id=min(backup_set_id) from [LinkServer].[msdb].[dbo].[backupset] where backup_set_id>(
select max(backup_set_id) from [LinkServer].[msdb].[dbo].[backupset] where database_name=@databasename and type='D') and database_name=@databasename and type='L'
while(@backup_set_id<=@backupmaxid)
begin
select @restorefilepath=name FROM [LinkServer].[msdb].[dbo].[backupset]
where backup_set_id=@backup_set_id and type='L' and database_name=@databasename
set @backup_set_id=@backup_set_id+2
select @restore='RESTORE LOG '+@databasename+' FROM DISK =''f:\log\'+ @restorefilepath+''''+'WITH NORECOVERY, STATS = 10'

select @restore
--exec sp_executesql @restore
end

 

注意点

1确保远程链接服务器已搭建成功

2确保需还原的数据库备份已拷贝到本机

转载请注明出处

posted on 2018-12-19 09:46 Andone勇闯天涯 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/andone/p/10141389.html