create proc 恢复到默认路径 @库名 varchar(100), @文件路径 varchar(1000) asdeclare @path varchar(200),@a nvarchar(200),@b nvarchar(200),@c nvarchar(200),@d nvarchar(200)select @path = filename from master.dbo.sysfiles set @path=ltrim(REVERSE(@path)) set @path=REVERSE(substring(@path,CHARINDEX('\',@path),len(@path))) create table #t(logicalname nvarchar(200), physicalname nvarchar(500), type varchar(10), filegroupname varchar(20), size bigint, maxsize bigint) insert #t exec('RESTORE FILELISTONLY FROM DISK = N''' + @文件路径 + '''') select @a=case when type='D' then logicalname else @a end,@b=case when type='L' then logicalname else @a end from #tselect @c=@path+@a+'.mdf',@d=@path+@b+'.ldf'restore database @库名 from disk=@文件路径 with NORECOVERY, move @a to @c, move @b to @d go
@库名 varchar(100),
@文件路径 varchar(1000)
asdeclare @path varchar(200),@a nvarchar(200),@b nvarchar(200),@c nvarchar(200),@d nvarchar(200)select @path = filename from master.dbo.sysfiles
set @path=ltrim(REVERSE(@path))
set @path=REVERSE(substring(@path,CHARINDEX('\',@path),len(@path))) create table #t(logicalname nvarchar(200),
physicalname nvarchar(500),
type varchar(10),
filegroupname varchar(20),
size bigint,
maxsize bigint)
insert #t exec('RESTORE FILELISTONLY FROM DISK = N''' + @文件路径 + '''')
select @a=case when type='D' then logicalname else @a end,@b=case when type='L' then logicalname else @a end from #tselect @c=@path+@a+'.mdf',@d=@path+@b+'.ldf'restore database @库名
from disk=@文件路径
with NORECOVERY,
move @a to @c,
move @b to @d
go