sqlserver批量恢复

最新推荐文章于 2024-04-15 14:11:45 发布

zw_hard

最新推荐文章于 2024-04-15 14:11:45 发布

阅读量737

点赞数

分类专栏： sqlserver 文章标签： sqlserver 批量恢复

本文链接：https://blog.youkuaiyun.com/zhengwei125/article/details/108005879

版权

1.需要修改备份文件目录

2.备份文件需bak结尾

3.备份文件名需默认的那种格式 dbname_backup_日期

declare
@backpath varchar(2000),
@execsql varchar(2000),
@datapath varchar(2000);
begin
set @backpath='C:\backup\';
set @execsql='dir /w '+ @backpath +'\*.BAK';
set @datapath='c:\Data';
begin
if not exists (select * from sysobjects where name='backfiles')
create table backfiles(name varchar(2000));
end
truncate table backfiles
insert into backfiles exec xp_cmdshell @execsql

DELETE backfiles WHERE upper(right(name,3))<>'BAK' OR NAME IS NULL

declare @fileName varchar(100),
@dbName varchar(100)

declare file_cur cursor for select name from backfiles;

open file_cur

fetch next from file_cur
into @fileName

while @@fetch_status=0
begin

select @dbName=substring(@fileName,1,charindex('_backup_',@filename)-1)

set @execsql ='rest