create procedure P_databaseInit
@bakFileName varchar(128)=null,--备份文件位置以及名称如:d:/bak.bak
@DataBaseName varchar(128)=null,--新数据库名称如:myTestDb
@dbPath varchar(128)=null, --新数据路径如:d:/或d:
@replace bit=0 --是否强制制恢复,如果遇到同名的数据库是否牵制恢复
as
create table #tmp( --建立临时表用来存储备份文件信息
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
Type char(1),
FileGroupName nvarchar(128),
Size numeric(20,0),
MaxSize numeric(20,0))
--insert into #tmp Exec #p_getBakFileList
insert into #tmp exec('RESTORE FILELISTONLY FROM DISK = ''e:/motorbak.bak''') --获取备份文件信息存入临时表
declare @LogicalName varchar(128),@restoreStr varchar(5000),@PhysicalName varchar(260)
declare myCur cursor
for
select LogicalName,physicalName from #tmp
if substring(reverse(@dbPath),1,1)<>'/' --新数据库路径处理,当然路径先要建立好来。可以在其他语言中处理
set @dbPath=@dbPath+'/';
if @replace=1 --处理是否强制还原
set @restoreStr ='restore database '+@DataBaseName+' from disk='''+@bakFileName+'''with recovery,replace';
else
set @restoreStr ='restore database '+@DataBaseName+' from disk='''+@bakFileName+'''with recovery';
open myCur
fetch next from myCur into @logicalName,@PhysicalName
while (@@fetch_status=0)
begin
set @physicalName=reverse(subString(reverse(@physicalName),1,charIndex('/',reverse(@physicalName),1)-1)) --获取原来的物理文件名
set @restoreStr=@restoreStr+',move '''+@logicalName+''' to '''+@dbPath+@physicalName+''''
fetch next from myCur into @logicalName,@PhysicalName
end
exec @restoreStr