sqlserver批量恢复

最新推荐文章于 2023-06-28 17:39:29 发布

原创

最新推荐文章于 2023-06-28 17:39:29 发布 · 774 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sqlserver #批量恢复

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 ='restore database '+'"'+@dbName+'"'+
' from disk='+CHAR(39)+@backpath+@fileName+CHAR(39)+
' with move '+CHAR(39)+@dbname+CHAR(39)+' to '+CHAR(39)+@datapath+'\'+@dbn