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 '+'"'[email protected]+'"'+
' from disk='+CHAR(39)[email protected][email protected]+CHAR(39)+
' with move '+CHAR(39)[email protected]+CHAR(39)+' to '+CHAR(39)[email protected]+'\'[email protected]+'.mdf'+CHAR(39)+','+'move '+CHAR(39)+
@dbname+'_log'+CHAR(39)+' to '+CHAR(39)[email protected]+'\'[email protected]+'_log.ldf'+CHAR(39)+';'

print @execsql
exec (@execsql)

fetch next from file_cur
into @fileName
end
close file_cur
deallocate file_cur
end